acess: SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name; sql: select name from sysobjects where type='U'
select * from sysobjects where type='U'Access下边没用过,不知道有没有效果。
sql: select name from sysobjects where type='U' and status>0
SQL 数据库:在 Sysobjects 表 Xtype='u'
sql: use db_name go select name from sysobjects
select name from sysobjects where type='U'
三种方法:注意引用相应的对象库 '***** ' 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
To: zyg0(影子(成功减肥10斤,可给我饿坏了,我要补回来) ACCESS里 MSysObjects没有权限怎么办?
获取access库中表的个数及表的名称[收藏] 数据库-MSAccess '功能:获取access库中表的个数及表的名称 '用ado怎样实现 '工程--->引用--->Microsoft ActiveX Data Object 2.x(版本号) '---------------------------------------------------------------------------- Private Sub Form_Load() Dim adoCN As New ADODB.Connection '定义数据库的连接 Dim strCnn As New ADODB.Recordset Dim rstSchema As New ADODB.Recordset Dim I As Integer str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.MDB;Persist Security Info=False" adoCN.Open str1
Set rstSchema = adoCN.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF If rstSchema!TABLE_TYPE = "TABLE" Then out = out & "Table name: " & _ rstSchema!TABLE_NAME & vbCr & _ "Table type: " & rstSchema!TABLE_TYPE & vbCr I = I + 1 End If rstSchema.MoveNext Loop MsgBox I rstSchema.Close
adoCN.Close Debug.Print out End Sub 方法二: Access 系统表 MsysObjects 包含了数据库对象列表。尽管未在文档中记载,你仍可通过查询它来获取你想要的。但是默认情况下无法操作系统表,必须手动设定权限收才能查询系统表。 请参考以下动画:如何获取对msysobject的操作权限 http://access911.net/eg/swf/msobject.swf警告:不要修改任何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;
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;
sql:
select name from sysobjects where type='U'
select name from sysobjects where type='U' and status>0
sql:
use db_name
go
select name from sysobjects
'*****
' 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
数据库-MSAccess '功能:获取access库中表的个数及表的名称
'用ado怎样实现
'工程--->引用--->Microsoft ActiveX Data Object 2.x(版本号)
'----------------------------------------------------------------------------
Private Sub Form_Load()
Dim adoCN As New ADODB.Connection '定义数据库的连接
Dim strCnn As New ADODB.Recordset
Dim rstSchema As New ADODB.Recordset
Dim I As Integer
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.MDB;Persist Security Info=False"
adoCN.Open str1
Set rstSchema = adoCN.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
If rstSchema!TABLE_TYPE = "TABLE" Then
out = out & "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
I = I + 1
End If
rstSchema.MoveNext
Loop
MsgBox I
rstSchema.Close
adoCN.Close
Debug.Print out
End Sub
方法二: Access 系统表 MsysObjects 包含了数据库对象列表。尽管未在文档中记载,你仍可通过查询它来获取你想要的。但是默认情况下无法操作系统表,必须手动设定权限收才能查询系统表。
请参考以下动画:如何获取对msysobject的操作权限
http://access911.net/eg/swf/msobject.swf警告:不要修改任何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;