Creating a New Database

The Catalog object is at the top of the ADOX object model. You can use this object to iterate through the members of its collections or to create a new database whose collections you subsequently populate. The two procedures in the following listing demonstrate the syntax for programmatically creating a new Access database. If an existing file has the same name as the target file for the new database, a run-time error occurs. The second procedure recovers from this failure by deleting the existing file and resuming. You can use the FileSystemObject object in the Microsoft Scripting Runtime library to perform a more sophisticated solution, such as renaming an existing file. The Persisting Quarterly Totals to a Text File section in Chapter 1 demonstrates use of the FileSystemObject, and the concluding sample in this chapter revisits the topic. After adding a reference to the Microsoft Scripting Runtime library, you can use the Object Browser to explore the methods of the FileSystemObject.

The initial procedure performs just two functions. First, it specifies the path and filename for the new Access database file. Second, it passes these as a single variable to the second procedure. The second procedure instantiates a new Catalog object and then uses the Create method to generate a new Access database file. The Create method takes a simple ADO connection string as an argument. The connection string consists of the designation of a data provider (for a Jet 4 database in this sample) and the string variable passed to the procedure with the path and filename for the new database. Because the ADOX model is a special ADO extension for Jet, you should use the ADOX model to create Access database files only.

Sub CallMakeAJetDB()
Dim str1 As String
   
'Specify path and filename for the new database
str1 = "C:\Access11Files\Chapter03\MyNewDB.mdb"
MakeAJetDB str1
   
End Sub
   
Sub MakeAJetDB(str1 As String)
On Error GoTo MakeAJetDB_Trap
Dim cat1 As ADOX.Catalog
   
'Instantiate catalog, and create a new
'database file based on it
Set cat1 = New ADOX.Catalog
cat1.create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & str1
   
MakeAJetDB_Exit:
'Clean up objects
Set cat1 = Nothing
Exit Sub
MakeAJetDB_Trap:
If Err.Number = -2147217897 Then
'When the file already exists,
'kill the prior version
    Debug.Print str1
    Kill (str1)
    Resume
Else
    Debug.Print Err.Number, Err.Description
    MsgBox "View Immediate window for error diagnostics.", _
        vbInformation, "Programming Microsoft Access 2003"
End If
   
End Sub