Creating a New Access Database

You can create a new Microsoft Jet database programmatically by using the ADOX Catalog's Create method. The ADOX library is discussed in a later chapter. The Create method creates and opens a new ADO connection to the data source. An error will occur if the provider does not support creating new catalogs. The procedure in Hands-On 10-10 creates a new blank database named NewAccessDb.mdb in your computer's root directory. The error trap ensures that the procedure works correctly even if the specified file already exists. The VBA Kill statement is used to delete the file from your hard disk when the error is encountered.

©Hands-On 10-10: Creating a New Microsoft Access Database Using ADO

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the CreateI_NewDatabase procedure shown below.

Creating and Manipulating Databases with ADO

3. Choose Run | Run Sub/UserForm to execute the procedure.

' you must make sure that a reference to ' Microsoft ADO Ext. 2.5 for DDL and Security ' Object Library is set in the References dialog box

Sub CreateI_NewDatabase() Dim cat As ADOX.Catalog Dim strDb As String

Set cat = New ADOX.Catalog strDb = "C:\NewAccessDb.mdb"

On Error GoTo ErrorHandler cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" &_

"Data Source=" & strDb MsgBox "The database was created (" & strDb & ")." Set cat = Nothing Exit Sub

ErrorHandler:

If Err.Number = -2147217897 Then Kill strDb Resume 0 Else

MsgBox Err.Number & ": " & Err.Description End If End Sub

While creating a database, you may specify that the database should be encrypted by setting the Jet OLEDB:Encrypt Database property to True. You can also include the database version information with the JetOLEDB:Engine Type property. Simply include these properties in the connection string, as shown in the following example:

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" &_ "Data Source=" & strDb & _ "Jet OLEDB:Encrypt Database=True;" & _ "Jet OLEDB:Engine Type=1;"

0 0

Post a comment