Public Function NonSystemTables(dbPath As String) As Collection'Input: Full Path to an Access Database'Returns: Collection of the names 'of non-system tables in that database 'or Nothing if there is an error'Requires: a reference to data access 'objects (DAO) in your projectOn Error GoTo ErrHandlerDim td As DAO.TableDef Dim db As DAO.Database Dim colTables As CollectionSet db = workspaces(0).opendatabase(dbPath)Set colTables = New Collection For Each td In db.TableDefs If td.Attributes >= 0 And td.Attributes <> dbHiddenObject _ And td.Attributes <> 2 Then
colTables.Add td.Name End If Next db.close Set NonSystemTables = colTablesExit Function ErrHandler: On Error Resume Next If Not db Is Nothing Then db.CloseSet NonSystemTables = NothingEnd Function
使用ADO必须使用ADOX才能得到数据库中的表集合,查询集合 在VB中引用一下ADOX.
ADO: Public Sub OpenSchemaX() Dim cnn1 As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnn As String
Set cnn1 = New ADODB.Connection strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourDB.mdb;Persist Security Info=False" cnn1.Open strCnn
'of non-system tables in that database
'or Nothing if there is an error'Requires: a reference to data access
'objects (DAO) in your projectOn Error GoTo ErrHandlerDim td As DAO.TableDef
Dim db As DAO.Database
Dim colTables As CollectionSet db = workspaces(0).opendatabase(dbPath)Set colTables = New Collection For Each td In db.TableDefs If td.Attributes >= 0 And td.Attributes <> dbHiddenObject _
And td.Attributes <> 2 Then
colTables.Add td.Name
End If
Next
db.close
Set NonSystemTables = colTablesExit Function
ErrHandler:
On Error Resume Next
If Not db Is Nothing Then db.CloseSet NonSystemTables = NothingEnd Function
在VB中引用一下ADOX.
Public Sub OpenSchemaX() Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourDB.mdb;Persist Security Info=False"
cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
End Sub
select name from MSysObjects;
当然of123()的方法也可以。