Return to Home page.

Okay, let's get the sarcasm out of the way first. You're right - I'm /not/ a Visual Basic programmer. I do C, and a bit of C++. My Visual Basic skills are not really something I shout about, and with good reason. Nevertheless, I do use it occasionally - not as much nowadays as I used to. Anyway, this morning (26/10/1999) I was asked how to access an Access (if you'll pardon the pun) database from Visual Basic. Having written several programs along these lines a year or two ago, I wittered on briefly about recordsets and workspaces but rapidly had to admit that I'd completely forgotten how to do this! So it was time for a voyage of re-discovery. Thus this article which, I suspect, is about to trace the effects of a strange combination of experience and amnesia.

Without further ado, let's fire up Visual Basic. You might care to pour yourself a coffee while you wait...

Well, that didn't take too long, did it? Okay, the first thing I need to do is to create a database. Normally, one would use Access to do this. It is possible in VB, but I seem to recall that it's awkward, so Access would seem to be the Right Thing (ahem - I'll keep my opinions of Access to myself). Since I don't have Access, however (at least, I have an Office CD lurking in the depths, but I'm not in that much of an exploratory mood), I'm going to have to come up with some other way.

Fortunately, Visual Basic comes complete with a little application called VisData. You will find it skulking in the Samples directory.

In a moment we're going to use it to create a database. But first, what kind of database do we want to create? Just for a moment, there was a chance I'd choose an interesting example, but I'm afraid I'm going to wimp out and choose an old favourite; college grades. Here's my first cut at a design:

Table 1: Course

Each record contains

Field: Course Name

Field: Number of Students

Field: Tutor name

Table 2: Student

Each record contains

Field: Student Name

Field: Cour...

Oh dear, this isn't going to work, is it? Students enrol on more than one course, and more than one student enrols on any one course (except for the really scary or dull courses, I suppose), so we have the dreaded many-to-many relationship - something to be avoided in database design. Now, I suspect that Access can sort this all out by making an invisible intermediate table - but I'm not sure, and anyway we can write a much more useful table of this kind ourselves. After all, if there's one thing that links a student to a course, it's a grade!

So, here's our second cut at the design:

Table 1: Course

Each record contains

Field: Course Number (key field)

Field: Course Name

Field: Tutor name

Table 2: Student

Each record contains

Field: Student ID (key field)

Field: Student Name

Table 3: Grade

Each record contains

Field: Student ID

Field: Course ID

Field: Grade

This isn't very sophisticated, I know. We'll keep it simple for now. If you are an experienced DBA, you've probably already stopped reading this article anyway, and if you aren't an experienced DBA, you probably don't want too much sophistication. If you don't even know what a DBA is, don't worry about it.

Now, let's fire up VisData (it's a Visual Basic app in the Samples\VisData directory of your VB installation) and create that table.

The first question VisData asked me when I started it was something to do with an ID file. I didn't understand the question, so I clicked No and that seemed to satisfy it. (When in doubt, say No to anything a computer asks you...)

Next, I moused through File/New... / Database/Access 7.0 mdb, which gave me a dialog box asking me where I wanted my new database to be put. Good question. I created a new directory for it. I chose the marvellously original name "VBDBDemo" for both the directory and the database.

I was now presented with a Database Window and an SQL Statement window, neither of which looked particularly friendly. Still, I clicked on the SQL Statement window and it looked like it wanted me to type in it, so I did. I don't know about you, but I'm not that hot on SQL, so I cheated and looked up CREATE TABLE in online help. Hey, at least I knew what to look up, and that's half the battle, right?

Here's what we want to do:

CREATE TABLE Course (ID TEXT CONSTRAINT COURSEID PRIMARY KEY, NAME TEXT, TUTOR TEXT);

CREATE TABLE Student (ID TEXT CONSTRAINT STUDENTID PRIMARY KEY, FIRSTNAME TEXT, LASTNAME TEXT);

CREATE TABLE Grade (COURSEID TEXT, STUDENTID TEXT, GRADE TEXT, MARK INTEGER);

Well, that's my guess anyway. I've included a new field in the Grade table, on a whim - the MARK field, which I reckon will be handy for recording the actual percentage scored on a course, and hence will be useful in later calculations, should we choose to make them.

(Note that the Course ID and Student ID would, in a real program, almost certainly be selected by the program, not typed in by the user, using an incremental counter. You can actually tell Access to do this when you create a table - but I couldn't work out how to tell VisData. As long as you know how it should be done, we can carry on and do it the lame way for now.)

Okay, let's try that. Copynpaste rocks... click Execute...

Well, I was asked if this was a passthrough query. In accordance with IIDSN (If In Doubt, Say No), I said No. I got an error message. So I tried again, and this time said Yes. This time, the error I got was "table already exists" which is cool by me.

I repeated the whole thing, complete with error messages, for the other two tables.

Now, in theory, I have a database - with no records in it, but at least it should have some tables - unless those ominous error messages portended some dire problem. To test my theory, I closed the database and re-opened it to see if I could work out a way of examining the tables. I wasn't hopeful but, hey!, it worked!

So, I'm happy now. I have a database. (The only minor cloud on the horizon is that, when I clicked on the field names, I observed that they had a DataUpdatable property which, in every case, was set to False. But we'll cross that bridge when we come to it.)

I've just leapt forward in time, and discovered that this wasn't in fact a problem - or at least didn't appear to be. Then I went back in time so that I could report this wonderful news here and now. Sorry I can't stop - I have to go back to the future.

(Did you just hear a noise? No? Oh well, maybe it's just me [shudder] )

So, now let's add some records to our database. For this, I need to write a simple VB application (at last, I hear you cry!). If I can just get some courses into the database programmatically I'll be happy enough (for now), so I just want three text boxes and two buttons. The text boxes are for ID, Course Name, and Tutor, and the buttons are for "Add" and "Exit". Traditionally, these boxes should be called Text1, Text2 and Text3, but I'm going to break with this long and honourable history for the sake of sheer clarity - I'm instead going to call them edCourseID, edCourseName, edCourseTutor. The ed prefix reminds me that I'm dealing with an edit box, and Course (which may seem redundant) simply reminds me which table I'm dealing with. I don't intend to explore data-bound controls here; apparently they're really easy, so that would feel like ducking out - so I'm just going to use these edit controls to get user data into memory. It'll be our job to get it from memory into the database, using real code.

Please note: unless you have built the sample database using the same field and table names as I did, the following code won't work for you. That may not be a problem, if you are only reading this to get an idea of how to do similar things to your own database - which is fine by me; after all, that's the whole point, isn't it?

So, I close VisData and start a new project. I'm going to put it in the same directory as the database, to make life as simple as possible. I won't insult your intelligence by describing how I put three edit boxes and two buttons onto a form. Give me a moment, and then we'll take a look at the code I put on the button. (Please note, I'm not going to mess about with validation. I'll leave error checking and stuff as an exercise for the motivated reader. Of course, any production quality application must check pretty well everything for errors at all times.)

One minor point which will have you scratching your hair out if I don't tell you about it: you need to add a new reference to your project before we go much further. In the Project menu, click References... and find Microsoft DAO 3.51 Object Library. Check it (click the little square so you get a tick mark or check mark or whatever you call them in whatever country you find yourself in right now), and click OK. (I was scratching around in Project/Components for quite a while and couldn't find it anywhere - so I phoned a friend (hi MasterW!) who put me straight and told me to look in References, whilst roundly cursing my name for daring to remind him of the existence of Microsoft Access).

Go have some coffee. I won't be long.

Okay, I'm back, and it worked beautifully. I just checked with VisData, and it showed me that I have successfully added a record to the Course table. So, here's the code that achieved it:

Private Sub cmdAddCourse_Click()

    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    
    Set ws = CreateWorkspace("VBDemoWorkspace", "admin", "", dbUseJet)
    Set db = ws.OpenDatabase("vbdbdemo.mdb", , False)
    Set rs = db.OpenRecordset("COURSE")
    
    With rs
        .AddNew
        !ID = edCourseID.Text
        !Name = edCourseName.Text
        !Tutor = edCourseTutor.Text
        .Update
    End With
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    ws.Close
    Set ws = Nothing
End Sub

There, that wasn't so bad, was it?

Given this promising start, it shouldn't be too hard to repeat our success with the Student table. Here's the code:

Private Sub cmdAddStudent_Click()
    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    
    Set ws = CreateWorkspace("VBDemoWorkspace", "admin", "", dbUseJet)
    Set db = ws.OpenDatabase("vbdbdemo.mdb", , False)
    Set rs = db.OpenRecordset("STUDENT")
    
    With rs
        .AddNew
        !ID = edStudentID.Text
        !FirstName = edStudentFirstName.Text
        !LastName = edStudentLastName.Text
        .Update
    End With
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    ws.Close
    Set ws = Nothing
End Sub

Does that look familiar? ;-)

I had a quick peek using VisData to ensure that I successfully added a record. In case you're wondering how I did this, I simply ran the demo to add a record, then slipped into VisData and opened the database, then typed SELECT * FROM STUDENT; in the SQL Statement box, and clicked on Execute.

It would be more satisfactory if we could examine the data from within our own program. We're going to do that next - we have to, because our next table, Grade, is based on the other two tables; it refers to both of them. The plan is to read the two tables, and put their contents into dropdown lists, so that we can pick and click. To do this, we will have to iterate through the other two tables. Note: the best time to do this is when the user selects the New Grade option. I chose to put all this functionality on a new form (in fact, I used a separate form for each 'New Foo') - so the right place for the code is the Form_Activate method of that form.

Private Sub Form_Activate()
    Dim ws As Workspace
    Dim db As Database

    ' rs is fine when you only have one table. With more, better names are a good idea.
    Dim rsStudent As Recordset
    Dim rsCourse As Recordset
    
    Set ws = CreateWorkspace("VBDemoWorkspace", "admin", "", dbUseJet)
    Set db = ws.OpenDatabase("vbdbdemo.mdb", , False)
    Set rsStudent = db.OpenRecordset("STUDENT")
    Set rsCourse = db.OpenRecordset("COURSE")
    
    With rsCourse
        .MoveFirst
        While Not .EOF

            ' I chose to display the whole record, with @ signs as field
            ' delimiters, in case I need to yank out the data later (and I do!).
            ' The presentation is a bit naff, but this is a demo, not a finished product,
            ' and it's not as if I'm getting paid for it. :-)

            Temp$ = !ID & "@ " & !Name & " @ " & !Tutor
            comboCourse.AddItem (Temp$)
            .MoveNext
        Wend
    End With

    ' Not being a VB expert, I'm not sure if I have fallen foul of an off-by-one error
    ' on the record iterations. I've coded this the way I would in C - try to read a
    ' record, then check EOF in case it failed. I presume VB works in the same way
    ' but that presumption could be wrong. It seems to work as written, but I've
    ' not stress-tested it. Either do that yourself, or consult a VB expert.

    With rsStudent
        .MoveFirst
        While Not .EOF
            Temp$ = !ID & "@ " & !FirstName & " " & !LastName
            comboStudent.AddItem (Temp$)
            .MoveNext
        Wend
    End With
    rsCourse.Close
    Set rsCourse = Nothing
    rsStudent.Close
    Set rsStudent = Nothing
    db.Close
    Set db = Nothing
    ws.Close
    Set ws = Nothing
End Sub

Now that code populates our combo boxes; this enables us to select existing records from the database, so that we can link up to them. If we'd just left the user to remember, our data might well have got into a right mess.

Now that we have the combo boxes working (and they do, I checked) we can cut code to add the record to the Grade table.

Private Sub cmdAdd_Click()
    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    Dim OkToAdd As Boolean
    Dim Course As Long
    Dim student As Long
    
    Set ws = CreateWorkspace("VBDemoWorkspace", "admin", "", dbUseJet)
    Set db = ws.OpenDatabase("vbdbdemo.mdb", , False)
    Set rs = db.OpenRecordset("GRADE")
    
    OkToAdd = False
    
    If comboCourse.ListIndex >= 0 Then
        If comboStudent.ListIndex >= 0 Then
            Course = InStr(comboCourse.List(comboCourse.ListIndex), "@")
            student = InStr(comboStudent.List(comboStudent.ListIndex), "@")
            If Course > 0 And student > 0 Then
                OkToAdd = True
            End If
        End If
    End If
    
    If OkToAdd Then
        With rs
            .AddNew
            !StudentID = Left(comboStudent.List(comboStudent.ListIndex), student - 1)
            !CourseID = Left(comboCourse.List(comboCourse.ListIndex), student - 1)
            !Grade = edGradeGrade.Text
            !Mark = Int(Val(edGradeMark.Text))
            .Update
        End With
    End If
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    ws.Close
    Set ws = Nothing
End Sub

So we can now add records to the course table, the student table, and (subject to those tables having some data in them) the grade table. What's left?

A database traditionally requires four primitive functions to be of any real use:

Create

Retrieve

Amend

Purge

We've dealt with data creation in this tutorial. (If you have been reading closely, you will have spotted some sneaky retrieval going on too.) And that's where we'll leave it for now. I'll pick this up again in a few years, when I will discuss Data Retrieval, Amendment, and Purging in more detail.

Click here for the sample database and the full source code (as a zip file). Don't worry too much if you get a few "can't load OCX file nag nag nag" messages - I got a bit carried away putting doodads and oojimaflips into the project, but deleted them all eventually.

C_Dreamer

October 1999