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.

 SubCallMakeAJetDB() Dimstr1AsString     'Specifypathandfilenameforthenewdatabase str1="C:\Access11Files\Chapter03\MyNewDB.mdb" MakeAJetDBstr1     EndSub     SubMakeAJetDB(str1AsString) OnErrorGoToMakeAJetDB_Trap Dimcat1AsADOX.Catalog     'Instantiatecatalog,andcreateanew 'databasefilebasedonit Setcat1=NewADOX.Catalog cat1.create"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&str1     MakeAJetDB_Exit: 'Cleanupobjects Setcat1=Nothing ExitSub MakeAJetDB_Trap: IfErr.Number=-2147217897Then 'Whenthefilealreadyexists, 'killthepriorversion Debug.Printstr1 Kill(str1) Resume Else Debug.PrintErr.Number,Err.Description MsgBox"ViewImmediatewindowforerrordiagnostics.",_ vbInformation,"ProgrammingMicrosoftAccess2003" EndIf     EndSub