VBA Brush Up 11：Creating and Accessing Tables and Fields with ADO

Technorati 标签: VBA, ADO, table, error handler

来自：Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p194-p211

In this chapter you will use the ADOX library to programmatically create tables and add new fields.

（1）

（2）The Catalog object is extremely important as it represents an entire database and contains database tables, columns, indexes, groups, users, procedures, and views.

（3）The following steps outline the process of creating a new Microsoft Access table:

1. Declare the variables representing the Connection, Catalog, and Table objects:

1. Dim conn As ADODB.Connection 
2. Dim cat As ADOX.Catalog 
3. Dim tbl As ADOX.Table 

2. Open the connection to your database:

1. set conn = New ADODB.Connection 
2. conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
3.     "Data Source=C:/NewAccessDb.mdb" 

3. Supply the open connection to the ActiveConnection property of the ADOX Catalog object:

1. Set cat = New ADOX.Catalog 
2. set cat.ActiveConnection = conn 

4. Create a new Table object:

1. Set tbl = New ADOX.Table 

5. Provide the name for your table:

1. tbl.Name = "tblAssets" 

The Table object is a member of the Tables collection, which in turn is a member of the Catalog object. Each Table object has a Name property and a Type property. The Type property specifies whether a Table object is a standard Microsoft Access table, a linked table, a system table, or a view.

6. Append the Table object to the Catalog object’s Tables collection:

1. cat.Tables.Append tbl 

At this point your table is empty.

7. Add new fields (columns) to your new table:

1. With tbl.Columns 
2.     .Append "SiteId", adVarWChar, 10 
3.     .Append "Category", adSmallInt 
4.     .Append "InstallDate", adDate 
5. End With

You can create new fields in a table by passing the Column object Name, Type, and DefinedSize properties as arguments of the Columns collection’s Append method.

The Table object contains the Columns collection that contains Column objects. To add a new field to a table you could create a Column object and write the following code:

1. Dim col As ADOX.Column 
2. set col = New ADOX.Column 
3. With col 
4.     .Name = "SiteId" 
5.     .DefinedSize = 10 
6. End With 
7. tbl.Columns.Append col

Creating fields in this manner takes longer and is less efficient than using the method demonstrated earlier.

（4）Notice that ADOX uses different data types than those used in the Access user interface

（5）

1.     On Error GoTo ErrorHandler
2.     ……
3.     cat.Tables.Append myTbl
4.     ……
5.     Exit Sub
7. ErrorHandler: 
8.     If Err.Number = -2147217857 Then 
9.         cat.Tables.Delete "tblFilters" 
10.         Resume 
11.     End If 
12.     MsgBox Err.Number & ": " & Err.Description

（6）SQL SELECT…INTO statement: select all records from one table into a new table. This statement creates a new table and inserts data from other tables. To copy a table, the SQL statement is passed as the first argument of the Execute method of the Connection object. Note that the copied table will not have the indexes that may exist in the original table.

1. Sub Copy_Table()
2.     Dim conn As ADODB.Connection 
3.     Dim strTable As String 
4.     Dim strSQL As String
6.     On Error GoTo ErrorHandler
8.     strTable = "Customers"
10.     strSQL = "SELECT " & strTable & ".* INTO " 
11.     strSQL = strSQL & strTable & "Copy " 
12.     strSQL = strSQL & "FROM " & strTable 
13.     Debug.Print strSQL
15.     Set conn = New ADODB.Connection 
16.     conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
17.         "Data Source=" & CurrentProject.Path & _ 
18.         "/Northwind.mdb" 
19.     conn.Execute strSQL 
20.     conn.Close 
21.     Set conn = Nothing 
22.     MsgBox "The " & strTable & " table was copied." 
23.     Exit Sub
25. ErrorHandler: 
26.     If Err.Number = -2147217900 Then 
27.         conn.Execute "DROP Table " & strTable 
28.         Resume 
29.     Else 
30.         MsgBox Err.Number & ": " & Err.Description 
31.     End If
32. End Sub

（7）Deleting a Database Table: You can delete a table programmatically by opening the ADOX Catalog object, accessing its Tables collection, and calling the Delete method to remove the specified table from the Tables collection.

1. Sub Delete_Table(strTblName As String)
3.     Dim cat As ADOX.Catalog 
4.     On Error GoTo ErrorHandler
6.     Set cat = New ADOX.Catalog 
7.     cat.ActiveConnection = CurrentProject.Connection 
8.     cat.Tables.Delete strTblName 
9.     Set cat = Nothing 
10.     Exit Sub
12. ErrorHandler: 
13.     MsgBox "Table '" & strTblName & _ 
14.         "' cannot be deleted " & vbCrLf & _ 
15.     v"because it does not exist." 
16.     Resume Next
18. End Sub

（8）Removing a Field from a Table：access the ADOX Columns collection of a Table object and use the Columns collection Delete method to remove a field from a table.

1. cat.ActiveConnection = CurrentProject.Connection 
2. cat.Tables("tblFilters").Columns.Delete "Type"

A run-time error will occur if you attempt to delete a field that is part of an index.

（9）Retrieving Table Properties

1. Dim pr As ADOX.Property
2. ……
3. Set tbl = cat.Tables("tblFilters")
5. ' retrieve table properties 
6. For Each pr In tbl.Properties 
7.     Debug.Print tbl.Name & ": " & _ 
8.         pr.Name & "= "; pr.Value 
9. Next 
11. Set cat = Nothing
12. ……

（10）Retrieving Field Properties：

1. Dim col As ADOX.Column 
2. Dim pr As ADOX.Property
3. ……
5. Set col = New ADOX.Column 
6. Set col = cat.Tables("tblFilters").Columns("Id")
7. ……
9. For Each pr In col.Properties 
10. Debug.Print pr.Name & "="; pr.Value 
11. Next
12. …… 

（11）Linking a Microsoft Access Table：In Microsoft Office Access 2003, you can create links to tables in Access databases as well as other data formats supported by Microsoft Jet’s installable drivers (for example: Excel, dBASE, Paradox, Exchange/Outlook, Lotus, Text, and HTML) and ODBC drivers.

1. ……
2. strDb = CurrentProject.Path & "/Northwind.mdb" 
3. strTable = "Employees" 
4. Set cat = New ADOX.Catalog 
5. cat.ActiveConnection = CurrentProject.Connection
7. Set lnkTbl = New ADOX.Table 
8. With lnkTbl
10.     ' Name the new Table and set its ParentCatalog property to the 
11.     ' open Catalog to allow access to the Properties collection. 
12.     .Name = strTable 
13.     Set .ParentCatalog = cat
15.     ' Set the properties to create the link 
16.     .Properties("Jet OLEDB:Create Link") = True 
17.     .Properties("Jet OLEDB:Link Datasource") = strDb 
18.     .Properties("Jet OLEDB:Remote Table Name") = strTable 
19. End With
21. ' Append the table to the Tables collection 
22. cat.Tables.Append lnkTbl
24. Set cat = Nothing

（12）Linking a Microsoft Excel Spreadsheet：You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method of the DoCmd object. Note, however, that neither the DoCmd object nor its Transfer-Spreadsheet method are members of the ADO Object Model. The DoCmd object is built into the Microsoft Access library.

1. Sub Link_ExcelSheet()
3.     Dim rst As ADODB.Recordset 
4.     DoCmd.TransferSpreadsheet acLink, _ 
5.         acSpreadsheetTypeExcel8, _ 
6.         "mySheet", _ 
7.         CurrentProject.Path & "/Regions.xls", _ 
8.         -1, _ 
9.         "Regions!A1:B15"
11.     Set rst = New ADODB.Recordset 
12.     With rst 
13.         .ActiveConnection = CurrentProject.Connection 
14.         .CursorType = adOpenKeyset 
15.         .LockType = adLockOptimistic 
16.         .Open "mySheet", , , , adCmdTable 
17.         End With 
18.     Do Until rst.EOF 
19.         Debug.Print rst.Fields(0).Value, rst.Fields(1).Value 
20.         rst.MoveNext 
21.     Loop 
22.     rst.Close 
23.     Set rst = Nothing
25. End Sub 

The Link_ExcelSheet procedure begins by creating a linked table named mySheet from the specified range of cells (A1:B15), located in the Regions worksheet in the Regions.xls file. The –1 argument in the DoCmd statement indicates that the first row of the spreadsheet contains column headings. Next, the procedure uses the ADO Recordset object to retrieve the data from the mySheet table into the Immediate window. Notice that prior to opening the Recordset object, several properties of the Recordset object must be set.

• • The ActiveConnection property sets the reference to the current database.
  • The CursorType property specifies how the Recordset object should interact with the data source.
  • The adOpenKeyset setting tells Visual Basic that instead of retrieving all the records from the data source, only the keys are to be retrieved. The data for these keys is retrieved only as you scroll through the recordset. This guarantees better performance than retrieving big chunks of data at once.
  • The LockType property determines how to lock the data while it is being manipulated.
  • The adLockOptimistic setting locks the record only when you attempt to save it.
  • Opening the Recordset object also requires that you specify the data source. The data source in this procedure is the linked table named mySheet. The parameter passed depends on the source type used.
  • The adCmdTable setting indicates that all rows from the source table should be included.

You could also open the Recordset object by passing all the required parameters at once, as follows:

1. rst.Open "mySheet", _ 
2.     CurrentProject.Connection, adOpenKeyset, _ 
3.     adLockOptimistic, adCmdTable

（13）Listing Database Tables: uses the ADOX Catalog object to gain access to the database, then iterates through the Tables collection to retrieve the names of Access tables, system tables, and views. The ADOX Tables collection stores various types of Table objects, as shown in Table 11-2

1. Set cat = New ADOX.Catalog 
2. cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
3.     "Data Source=" & CurrentProject.Path & _ 
4.     "/Northwind.mdb" 
5. For Each tbl In cat.Tables 
6.     If tbl.Type <> "VIEW" And _ 
7.         tbl.Type <> "SYSTEM TABLE" And _ 
8.         tbl.Type <> "ACCESS TABLE" Then Debug.Print tbl.Name 
9. Next tbl 
10. Set cat = Nothing

（14）Changing the AutoNumber：The AutoNumber is a unique sequential number (incremented by 1) or a random number assigned by Microsoft Access whenever a new record is added to a table. Microsoft Office Access 2003 allows you to set the start and step value of auto-increment fields programmatically by using Jet 4.0 SQL statements

（15）Listing Tables and Fields：To list tables in your database you can also use the OpenSchema method of the ADO Connection object

1. Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)
3. Do Until rst.EOF 
4.     Debug.Print rst.Fields("TABLE_TYPE") & " ->" _ 
5.         & rst.Fields("TABLE_NAME") 
6.     rst.MoveNext 
7. Loop

Obtaining the names of fields requires that you use adSchemaColumns as the parameter for the OpenSchema method.

1. Set rst = conn.OpenSchema(adSchemaColumns)
3. Do Until rst.EOF 
4.     curTable = rst!table_Name 
5.     If (curTable <> newTable) Then 
6.         newTable = rst!table_Name 
7.         Debug.Print "Table: " & rst!table_Name 
8.             counter = 1 
9.     End If 
10.     Debug.Print "Field" & counter & ": " & rst!Column_Name 
11.     counter = counter + 1 
12.     rst.MoveNext 
13. Loop 
14. rst.Close 
15. conn.Close 
16. Set rst = Nothing 
17. Set conn = Nothing

（16）Listing Data Types：uses the adSchemaProvider-Types parameter of the ADO Connection object’s OpenSchema method to list the data types supported by the Microsoft Jet OLE DB 4.0 provider.

1. Set conn=New ADODB.Connection 
2. conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
3.     & "Data Source=" & CurrentProject.Path & _ 
4.     "/Northwind.mdb" 
5. Set rst=conn.OpenSchema(adSchemaProviderTypes) 
6. Do Until rst.EOF 
7.     Debug.Print rst!Type_Name & vbTab _ 
8.         & "Size: " & rst!Column_Size 
9.     rst.MoveNext 
10. Loop

（17）This chapter has shown you how to programmatically create Microsoft Access tables by using ActiveX Data Objects. You learned how to add fields to your tables and define field data types. You found out how to list both tables and fields, and investigate their properties. In addition to creating new tables from scratch, you discovered how to work with linked tables. You also learned how to copy tables and transfer data from a Microsoft Excel spreadsheet to an Access table.