如何获取access中的表名
我想实现用vb的connection对象获取access(我已经建立了几个表)中的表名,我用OpenSchema(adSchemaTables)方法返回的记录集的记录总数总是-1,为何?请各位高手指点!!! 问题点数：20、回复次数：4Top
1 楼wwh999（印钞机V2.0...开发中....）回复于 2006-05-03 17:09:23 得分 0
Access 系统表 MsysObjects 包含了数据库对象列表, 尽管未在文档中记载, 你仍可通过查询它来获取你想要的.
注: 请不要有意或无意地修改任何ACCESS系统表,否则会出现不可意料的情况.
使用下列 SQL 语句来获取你想要的
查询:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;
窗体:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;
表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
报表:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;
模块:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;
宏:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;Top
2 楼hihiha（）回复于 2006-05-03 20:55:58 得分 0
谢谢高手指点了!
我按照您的试了一下,但vb提示我没有读取MsysObjects的权限呀!怎么解决?谢谢!Top
3 楼of123（）回复于 2006-05-03 23:46:52 得分 0
'*****
' 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 project
On Error GoTo ErrHandler
Dim td As DAO.TableDef
Dim db As DAO.Database
Dim colTables As Collection
Set 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 = colTables
Exit Function
ErrHandler:
On Error Resume Next
If Not db Is Nothing Then db.Close
Set NonSystemTables = Nothing
End 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 SubTop
4 楼wwh999（印钞机V2.0...开发中....）回复于 2006-05-04 08:29:31 得分 0
按照您的试了一下,但vb提示我没有读取MsysObjects的权限呀!怎么解决?谢谢!
-------------------------------------------------------------------------------------------------------
指定用户是ADMIN即可Top
