'***** ' DAO: '*****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: '***** Public Sub OpenSchemaX() Dim cnn1 As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnn As String
Set cnn1 = New ADODB.Connection strCnn = "driver={SQL Server};server=srv;" & _ "uid=sa;pwd=;database=pubs" cnn1.Open strCnn
End Sub'****** ' ADOX: '****** Private Sub PrintTableName() Dim cat As New ADOX.Catalog cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _ "Data Source= 'c:\Program Files\Microsoft Office\" & _ "Office\Samples\Northwind.mdb';" For i = 0 To cat.Tables.Count - 1 If cat.Tables(i).Type = "TABLE" Then Debug.Print cat.Tables(i).Name End If Next i End Sub
'***** ' DAO: '*****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 CollectionFor Each td In db.TableDefsIf td.Attributes >= 0 And td.Attributes <> dbHiddenObject _ And td.Attributes <> 2 ThencolTables.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: '***** Public Sub OpenSchemaX()Dim cnn1 As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnn As StringSet cnn1 = New ADODB.Connection strCnn = "driver={SQL Server};server=srv;" & _ "uid=sa;pwd=;database=pubs" cnn1.Open strCnnSet 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.Closecnn1.CloseEnd Sub'****** ' ADOX: '****** Private Sub PrintTableName() Dim cat As New ADOX.Catalogcat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _ "Data Source= 'c:\Program Files\Microsoft Office\" & _ "Office\Samples\Northwind.mdb';" For i = 0 To cat.Tables.Count - 1 If cat.Tables(i).Type = "TABLE" Then Debug.Print cat.Tables(i).Name End If Next i End Sub
dao当然没问题,但如果用ado,要用adoX Acess数据库是这样,但sql我没试过
select count(1) from sysobjects where type = 'U'
select count(1) from sysobjects where ObjectProperty(id,N'IsUserTable')=1
' DAO:
'*****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:
'*****
Public Sub OpenSchemaX() Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
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'******
' ADOX:
'******
Private Sub PrintTableName()
Dim cat As New ADOX.Catalog cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= 'c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb';"
For i = 0 To cat.Tables.Count - 1
If cat.Tables(i).Type = "TABLE" Then
Debug.Print cat.Tables(i).Name
End If
Next i
End Sub
' DAO:
'*****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 CollectionFor Each td In db.TableDefsIf td.Attributes >= 0 And td.Attributes <> dbHiddenObject _
And td.Attributes <> 2 ThencolTables.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:
'*****
Public Sub OpenSchemaX()Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As StringSet cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnnSet 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.Closecnn1.CloseEnd Sub'******
' ADOX:
'******
Private Sub PrintTableName()
Dim cat As New ADOX.Catalogcat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source= 'c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb';"
For i = 0 To cat.Tables.Count - 1
If cat.Tables(i).Type = "TABLE" Then
Debug.Print cat.Tables(i).Name
End If
Next i
End Sub
Acess数据库是这样,但sql我没试过