Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

DAO: Creating A Database

The Testing Department is calling again. How about creating a DAO database—in code? Hmm, you think, is that possible?

It is, with the objects in the Microsoft DAO Object Library. To add a reference to that library, select the Project|References menu item, select the Microsoft DAO Object Library, and click on OK to close the References dialog box. Now we can make use of the data objects in that library to create a new database using CreateDatabase. CreateDatabase is a method of the DAO Workspace object (there are a collection of Workspace objects in the DAO DBEngine object’s Workspaces collection). Here’s how you use CreateDatabase:

Set database = workspace.CreateDatabase (name, locale [, options])

Here are the arguments to CreateDatabase:

•  name—A string up to 255 characters long that is the name of the database file that you’re creating. It can be the full path and file name, such as C:vbbb\db.mdb. If you don’t supply a file name extension, .mdb is added.

•  locale—A string that specifies a collating order for creating the database, like dbLangGeneral (which includes English), dbLangGreek, and so on.

---

TIP:  You can create a password for a new Database object by concatenating the password (starting with “;pwd=”) with a constant in the locale argument, like this: dbLangGreek & “;pwd=NewPassword”. If you want to use the default locale, but specify a password, simply enter a password string for the locale argument: “;pwd=NewPassword”.

---

Here are the possible settings for the options argument:

•  dbEncrypt—Creates an encrypted database.

•  dbVersion10—Creates a database that uses the Jet engine version 1 file format.

•  dbVersion11—Creates a database that uses the Jet database engine version 1.1 file format.

•  dbVersion20—Creates a database that uses the Jet database engine version 2 file format.

•  dbVersion30—The default. Creates a database that uses the Jet database engine version 3 file format (compatible with version 3.5).

Let’s see an example to make this clearer. When the user selects the New database item in our example DAO program, daocode (see the first topic in this chapter), we will create a new database. First, we declare that database, db, as a form-wide variable:

Dim db As Database

Next, we add a Common Dialog control, CommonDialog1, to the program and show it to get the name of the database file the user wants to create:

Private Sub NewDatabase_Click()
    CommonDialog1.ShowSave
    If CommonDialog1.FileName <> "" Then
...

Finally, we create the new database, passing the CreateDatabase method the name of the database file and indicating that we want to use the default collating order by passing the constant dbLangGeneral:

Private Sub NewDatabase_Click()
    CommonDialog1.ShowSave
    If CommonDialog1.FileName <> "" Then
        Set db = DBEngine.Workspaces(0).CreateDatabase_
            (CommonDialog1.FileName, dbLangGeneral)
    End If
End Sub

And that’s it—we’ve created a new, empty database. The next step is to add a table to that database, and we’ll take a look at that in the next topic.

DAO: Creating A Table With A TableDef Object

How do you create a table in a DAO database? You define it with a TableDef object. After you do so, you can append fields to the table, and then you can append the new table definition to a database’s TableDefs collection.

Let’s see an example. After the users create a new database with our DAO code example, the daocode project (see the first topic in this chapter), they can create a new table using the New Table item in the File menu. That item opens the New Table dialog box you see in Figure 25.2.

Figure 25.2  The New Table dialog box.

Users can enter the name of the new table to create in the text boxes in the New Table dialog box, and we can use that information to create a new TableDef object, td, which we declare as a form-wide variable:

Dim td As TableDef

We create a new TableDef for the Database object we created in the previous topic, db, using the name for the table the user has placed in Text1 in the New Table dialog box:

Sub CreateTable()
    Set td = db.CreateTableDef(TableForm.Text1.Text)
...

This code creates a new, empty TableDef object named td. An empty table isn’t much use, though—we’ll see about adding fields to this object in the next topic.