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

Enumerating Tables

The Tables collection in the ADOX object model offers an easy route for enumerating tables in an Access database. Table 3-1 shows that the Tables collection contains several types of tables. You might prefer to filter out one or more of these table types during a typical enumeration task. Besides the ADOX library, you can also use AccessObject objects to enumerate tables. AccessObject object types correspond to major elements within an Access database file. The next section in this chapter concentrates on AccessObject objects for tables.

Listing All Table Types in a Catalog

The first code sample for enumerating tables lists all the tables within a catalog's ActiveConnection property setting. This setting is just a connection string that points at a data source through an ADO data provider. The sample consists of two procedures. The first procedure designates a data source argument for the connection string. The listing that appears here sets the string variable str1 to the path and filename for the Northwind database. You can designate any Access database file that you prefer to use instead of the Northwind database. For example, this listing contains an alternate file named MyNewDB that is commented out. This file was generated in the preceding sample.

SubCallListTablesRaw()
Dimstr1AsString
   
'RunwitheitherbackupcopyofNorthwindorMyNewDB.mdb
str1="C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
'str1="C:\Access11Files\Chapter03\MyNewDB.mdb"
ListTablesRawstr1
   
EndSub
   
SubListTablesRaw(str1)
Dimcat1AsADOX.Catalog
Dimtbl1AsADOX.Table
Dimstr2AsString
Dimstr3AsString
DimmwdAsInteger
   
'Instantiateacatalog,andpointittothetargetdatabase.
Setcat1=NewADOX.Catalog
cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource="&str1
   
'Computethelengthofthelongesttablenameinthedatabase.
'Assumeminimumlengthof5characters.
mwd=5
ForEachtbl1Incat1.Tables
IfLen(tbl1.Name)>mwdThenmwd=Len(tbl1.Name)
Nexttbl1
mwd=mwd+1
   
'PrintarowtotheImmediatewindowcontainingthename
'andtypeofmemberinthecatalog'sTablescollection
ForEachtbl1Incat1.Tables
str2=tbl1.Name
str3=String(mwd-Len(str2),"")
Debug.Printstr2&str3&tbl1.Type
Nexttbl1
   
'Cleanupobjects
Setcat1=Nothing
   
EndSub

After its declarations, the second procedure instantiates a Catalog object and sets its ActiveConnection property to the path and filename passed from the first procedure. Next, the second procedure passes through the members of the Tables collection to compute the longest table name. This permits the next block of code to space content evenly across the Immediate window for any table name in the catalog. Figure 3-2 shows the output to the Immediate window from the Northwind database. Notice the first column lists many more tables than you normally see in the Database window when you open the Northwind database. The second column shows each table type. This enumeration lists all the table types in the Northwind database but not all possible table types. Review Table 3-1 for a complete list of the possible table types along with a brief description of each. Those tables with a TABLE type specification appear in the Database window by default. Recall from Table 3-1 that views are virtual tables. The term virtual table correctly conveys the notion that a view is not actually a table. Instead, it is a SQL statement that returns a rowset, which it can present in a datasheet ”just like the rowset contained in a real table.

click to expand
Figure 3.2: Output from the CallListTablesRaw and ListTablesRaw procedures for a clean backup copy of the Northwind database.

The output of MyNewDB might surprise you. Running CallListTablesRaw on MyNewDB generates output even before we populate the database file with any user -defined tables, such as those with a type of TABLE , LINK , or PASS-THROUGH . The listing of tables for the empty MyNewDB database consists of the SYSTEM TABLE type. This table category depends on system-generated activity, as opposed to end-user activity or developer activity.

Filtering Table Types Before Enumerating

The preceding sample includes some of the resources that table enumeration can deliver to an application. However, instead of having a broad array of every table type, you can specify the return of a small subset of the total tables by designating the return of just one table type. By implementing this capability, you can gather precise information about the tables in any Access database file on a local area network (LAN) or wide area network (WAN). The reduced number of items associated with a filtered subset makes for a better record source for a combo box or list box. User interfaces work best when they show users only the information that they need to view.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

The next pair of procedures implements filtering to return just one type of table. The first procedure has three variables for a user to set. The first is a string variable that designates the path and filename for an Access database file. You must also assign a value to a second string variable that specifies a table type to return from the search target. This next listing sets this second string variable to the TABLE type. Recall that this type of table includes just tables in the local database created by or for users. The third variable has a Boolean data type. Setting the Boolean variable to True in the first procedure causes the second procedure to return a list of tables in the current Access database file. A False Boolean value points the catalog at the database specified by the first string, str1 .

SubCallListTablesTypeFilter()
Dimstr1AsString
Dimstr2AsString
Dimbol1AsBoolean
   
str1="C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind.mdb"
str2="TABLE"
bol1=False
ListTablesTypeFilterstr1,str2,bol1
   
EndSub
   
SubListTablesTypeFilter(str1AsString,_
str2AsString,bol1AsBoolean)
Dimcat1AsADOX.Catalog
Dimtbl1AsADOX.Table
Dimstr3AsString
Dimstr4AsString
DimmwdAsInteger
   
'Instantiatecatalog,andconnecttoCurrentProject
'oranotherdatabase.
Setcat1=NewADOX.Catalog
Ifbol1=TrueThen
Setcat1.ActiveConnection=_
CurrentProject.Connection
Else
cat1.ActiveConnection=_
"Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource="&str1
EndIf
   
'CreatereferencetoTablescollectionforcatalog,
'anddeterminelongesttablenameinthecatalog.
mwd=5
ForEachtbl1Incat1.Tables
If(tbl1.Type=str2)Then_
IfLen(tbl1.Name)>mwdThenmwd=Len(tbl1.Name)
Nexttbl1
mwd=mwd+1
   
'Printtablenamesandtheirtype.
ForEachtbl1Incat1.Tables
Iftbl1.Type=str2Then
str3=tbl1.Name
str4=String(mwd-Len(str3),"")
Debug.Printstr3&str4&tbl1.Type
EndIf
Nexttbl1
   
'Cleanupobjects.
Setcat1=Nothing
   
EndSub

The second procedure in this sample employs the design of the previous sample, with one major exception. This exception relates to how the sample develops a setting for the Catalog object's ActiveConnection property. An If Else statement assigns one of two data sources for the catalog's ActiveConnection property. The second procedure chooses a path from the If Else statement based on the Boolean value passed to it from the first procedure. By using a Select Case statement, the second procedure could easily enable the selection of any of a wide set of specified sources for the catalog's ActiveConnection property.

Printing Data for the Table Details View

The Details view in the Access Database window returns more than just the name of a table and its type. It also returns two other particularly useful bits of information, the table's date of creation and its last modification date. Because the Table object exposes DateCreated and DateModified properties, generating a display like the one in the Database window's Details view is relatively straightforward ”just make the width of the Description column zero after clicking the Details control on the Database window.

To keep the design of the next code listing simple, the first procedure passes just one argument to the second procedure. This argument is a string parameter that designates the filename and path for the target database file. The argument designates the Northwind_backup.mdb file. In my office, I use the Northwind_backup.mdb file when I want to work with an unedited version of the Northwind database. Recall that I initially referred to the Northwind_backup.mdb file in the "Connecting to Jet Databases" section of Chapter 1. The second procedure has hard-coded filters that return only user-defined tables in the target database. Because this sample generates four columns of output, it prints a row of headers that clarify what each column contains for the members of the tables collection.

SubCallTablesDetail()
Dimstr1AsString
   
str1="C:\ProgramFiles\MicrosoftOffice\"&_
"Office11\Samples\Northwind_backup.mdb"
TablesDetailstr1
   
EndSub
   
SubTablesDetail(str1AsString)
Dimcat1AsADOX.Catalog
Dimtbl1AsTable
DimmwdAsByte
   
'Instantiateacatalog,andpointitatthetargetdatabase.
Setcat1=NewADOX.Catalog
cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_
"DataSource="&str1
   
'Computethelengthofthelongesttablenameinthedatabase.
'Minimumlengthof10providesforthelengthofthecolumnheader.
mwd=10
ForEachtbl1Incat1.Tables
Iftbl1.Type="TABLE"Then_
IfLen(tbl1.Name)>mwdThenmwd=Len(tbl1.Name)
Nexttbl1
mwd=mwd+1
   
'PrintarowofcolumnheaderstotheImmediatewindowbefore
'printingthename,datemodified,datecreated,andtypeofmembers
'inthecatalog'sTablescollection.
Debug.Print"TableName"&String(mwd-Len("TableName"),"")&_
"DateModified"&String(22-Len("DateModified"),"")&_
"DateCreated"&String(22-Len("DateCreated"),"")&"Type"
ForEachtbl1Incat1.Tables
Iftbl1.Type="TA