来自: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:
- Dim conn As ADODB.Connection
- Dim cat As ADOX.Catalog
- Dim tbl As ADOX.Table
2. Open the connection to your database:
- set conn = New ADODB.Connection
- conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=C:/NewAccessDb.mdb"
3. Supply the open connection to the ActiveConnection property of the ADOX Catalog object:
- Set cat = New ADOX.Catalog
- set cat.ActiveConnection = conn
4. Create a new Table object:
- Set tbl = New ADOX.Table
5. Provide the name for your table:
- 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:
- cat.Tables.Append tbl
At this point your table is empty.
7. Add new fields (columns) to your new table:
- With tbl.Columns
- .Append "SiteId", adVarWChar, 10
- .Append "Category", adSmallInt
- .Append "InstallDate", adDate
- 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:
- Dim col As ADOX.Column
- set col = New ADOX.Column
- With col
- .Name = "SiteId"
- .DefinedSize = 10
- End With
- 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)
- On Error GoTo ErrorHandler
- ……
- cat.Tables.Append myTbl
- ……
- Exit Sub
- ErrorHandler:
- If Err.Number = -2147217857 Then
- cat.Tables.Delete "tblFilters"
- Resume
- End If
- 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.
- Sub Copy_Table()
- Dim conn As ADODB.Connection
- Dim strTable As String
- Dim strSQL As String
- On Error GoTo ErrorHandler
- strTable = "Customers"
- strSQL = "SELECT " & strTable & ".* INTO "
- strSQL = strSQL & strTable & "Copy "
- strSQL = strSQL & "FROM " & strTable
- Debug.Print strSQL
- Set conn = New ADODB.Connection
- conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & CurrentProject.Path & _
- "/Northwind.mdb"
- conn.Execute strSQL
- conn.Close
- Set conn = Nothing
- MsgBox "The " & strTable & " table was copied."
- Exit Sub
- ErrorHandler:
- If Err.Number = -2147217900 Then
- conn.Execute "DROP Table " & strTable
- Resume
- Else
- MsgBox Err.Number & ": " & Err.Description
- End If
- 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.
- Sub Delete_Table(strTblName As String)
- Dim cat As ADOX.Catalog
- On Error GoTo ErrorHandler
- Set cat = New ADOX.Catalog
- cat.ActiveConnection = CurrentProject.Connection
- cat.Tables.Delete strTblName
- Set cat = Nothing
- Exit Sub
- ErrorHandler:
- MsgBox "Table '" & strTblName & _
- "' cannot be deleted " & vbCrLf & _
- v"because it does not exist."
- Resume Next
- 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.
- cat.ActiveConnection = CurrentProject.Connection
- 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
- Dim pr As ADOX.Property
- ……
- Set tbl = cat.Tables("tblFilters")
- ' retrieve table properties
- For Each pr In tbl.Properties
- Debug.Print tbl.Name & ": " & _
- pr.Name & "= "; pr.Value
- Next
- Set cat = Nothing
- ……
(10)Retrieving Field Properties:
- Dim col As ADOX.Column
- Dim pr As ADOX.Property
- ……
- Set col = New ADOX.Column
- Set col = cat.Tables("tblFilters").Columns("Id")
- ……
- For Each pr In col.Properties
- Debug.Print pr.Name & "="; pr.Value
- Next
- ……
(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.
- ……
- strDb = CurrentProject.Path & "/Northwind.mdb"
- strTable = "Employees"
- Set cat = New ADOX.Catalog
- cat.ActiveConnection = CurrentProject.Connection
- Set lnkTbl = New ADOX.Table
- With lnkTbl
- ' Name the new Table and set its ParentCatalog property to the
- ' open Catalog to allow access to the Properties collection.
- .Name = strTable
- Set .ParentCatalog = cat
- ' Set the properties to create the link
- .Properties("Jet OLEDB:Create Link") = True
- .Properties("Jet OLEDB:Link Datasource") = strDb
- .Properties("Jet OLEDB:Remote Table Name") = strTable
- End With
- ' Append the table to the Tables collection
- cat.Tables.Append lnkTbl
- 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.
- Sub Link_ExcelSheet()
- Dim rst As ADODB.Recordset
- DoCmd.TransferSpreadsheet acLink, _
- acSpreadsheetTypeExcel8, _
- "mySheet", _
- CurrentProject.Path & "/Regions.xls", _
- -1, _
- "Regions!A1:B15"
- Set rst = New ADODB.Recordset
- With rst
- .ActiveConnection = CurrentProject.Connection
- .CursorType = adOpenKeyset
- .LockType = adLockOptimistic
- .Open "mySheet", , , , adCmdTable
- End With
- Do Until rst.EOF
- Debug.Print rst.Fields(0).Value, rst.Fields(1).Value
- rst.MoveNext
- Loop
- rst.Close
- Set rst = Nothing
- 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:
- rst.Open "mySheet", _
- CurrentProject.Connection, adOpenKeyset, _
- 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
- Set cat = New ADOX.Catalog
- cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
- "Data Source=" & CurrentProject.Path & _
- "/Northwind.mdb"
- For Each tbl In cat.Tables
- If tbl.Type <> "VIEW" And _
- tbl.Type <> "SYSTEM TABLE" And _
- tbl.Type <> "ACCESS TABLE" Then Debug.Print tbl.Name
- Next tbl
- 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
- Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)
- Do Until rst.EOF
- Debug.Print rst.Fields("TABLE_TYPE") & " ->" _
- & rst.Fields("TABLE_NAME")
- rst.MoveNext
- Loop
Obtaining the names of fields requires that you use adSchemaColumns as the parameter for the OpenSchema method.
- Set rst = conn.OpenSchema(adSchemaColumns)
- Do Until rst.EOF
- curTable = rst!table_Name
- If (curTable <> newTable) Then
- newTable = rst!table_Name
- Debug.Print "Table: " & rst!table_Name
- counter = 1
- End If
- Debug.Print "Field" & counter & ": " & rst!Column_Name
- counter = counter + 1
- rst.MoveNext
- Loop
- rst.Close
- conn.Close
- Set rst = Nothing
- 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.
- Set conn=New ADODB.Connection
- conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
- & "Data Source=" & CurrentProject.Path & _
- "/Northwind.mdb"
- Set rst=conn.OpenSchema(adSchemaProviderTypes)
- Do Until rst.EOF
- Debug.Print rst!Type_Name & vbTab _
- & "Size: " & rst!Column_Size
- rst.MoveNext
- 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.