哈哈,我知道耶,终于有我可以回答的问题了,呜呜,好感动哈,方法可能不是很好,你看着。 private sub GetAllTab() Dim Conn As New ADODB.Connection Dim Rs As ADODB.Recordset Dim ConnStr As String Dim StrTab As String ConnStr = "Provider=Microsoft.Jet.OLEDB.3.51;" & "Data Source=文件路径加文件名" Conn.Open ConnStr Set Rs = Conn.OpenSchema(adSchemaTables) Do Until Rs.EOF StrTab = Rs!table_name List1.AddItem StrTab Rs.MoveNext Loop Conn.Close end sub 这样就可以得到所以的表啦,包括系统表,如果你只需要用户表的话,那就把用户表筛选出来。用户表的Rs!table_type值为SYSTEM TABLE,用户表的Rs!table_type值为TABLE,注意一定是大写的。用if Rs!table_type=TABLE就可以筛选出用户表啦。哈哈,觉得我的方法还可以的话就给我加分吧,我还没尝过得到专家分的滋味哈。
说明一下,以上程序出自于OICQ的bbs中,不是我自己想的,哈哈
但是表达式:if Rs!table_type=TABLE并不能起到筛选作用.?????
应该是Rs!table_type = "TABLE",我忘了打引号了。
建议你好好看看msdn上有关OpenSchema方法的帮助,上面讲的很清楚
如果是SQL SERVER数据库的话可以试一试 private sub GetAllTab() Dim Rs As ADODB.Recordset Dim ConnStr As String Dim StrTab As String Dim strsql As String Dim i As Long
On Error GoTo HError strsql = "select s1 = o.name, s2 = user_name(o.uid)" strsql = strsql & " from dbo.sysobjects o, dbo.sysindexes i" strsql = strsql & " where OBJECTPROPERTY(o.id, N'IsTable') = 1" strsql = strsql & " and i.id = o.id" strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsSystemTable') = 0" strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0" strsql = strsql & " and i.indid < 2" strsql = strsql & " and o.name not like N'#%'" strsql = strsql & " order by s1, s2" ConnStr = "你的连接" ConnStr = "driver=sql server;server=dengchao2;uid=sa;pwd=;database=tt" Set Rs = New ADODB.Recordset With Rs .ActiveConnection = ConnStr .CursorLocation = adUseClient .Open strsql End With
For i = 1 To Rs.RecordCount List1.AddItem Rs!s1 & "" Rs.MoveNext Next
Rs.Close Set Rs = Nothing
HError:end sub
如何用Ado读出Access数据库中的表名?(SQL Server 一样) http://www.csdn.net/expert/Topic/33923.shtm
只讨论 ADO 方 法 1: 引用 ADO Dim adoConnectionX As New ADODB.Connection Dim adoSchemaRecordsetX As New ADODB.Recordset 'Ms SQL 7: 'adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS" 'Access 2000: adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Set adoSchemaRecordsetX = adoConnectionX.OpenSchema(adSchemaTables) Do until adoSchemaRecordsetX.EOF Debug.Print "Table name: " & _ adoSchemaRecordsetX !TABLE_NAME & vbCr & _ "Table type: " & adoSchemaRecordsetX!TABLE_TYPE & vbCr adoSchemaRecordsetX.movenext Loop 方 法 2(引 用 Microsoft ADO Extensions 2.1 for DDL and Security (ADOX)): ( 需 要 升 级 至 VB 6.0 SP3+) Dim adoConnectionX As New ADODB.Connection 'Ms SQL 7: adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS" 'Access 2000: 'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Dim adoxCatalogX As New ADOX.Catalog Set adoxCatalogX.ActiveConnection = adoConnectionX
Dim adoxTableX As ADOX.Table For Each adoxTableX In adoxCatalogX.Tables debug.print adoxTableX.name Next
private sub GetAllTab()
Dim Conn As New ADODB.Connection
Dim Rs As ADODB.Recordset
Dim ConnStr As String
Dim StrTab As String
ConnStr = "Provider=Microsoft.Jet.OLEDB.3.51;" & "Data Source=文件路径加文件名"
Conn.Open ConnStr
Set Rs = Conn.OpenSchema(adSchemaTables)
Do Until Rs.EOF
StrTab = Rs!table_name
List1.AddItem StrTab
Rs.MoveNext
Loop
Conn.Close
end sub 这样就可以得到所以的表啦,包括系统表,如果你只需要用户表的话,那就把用户表筛选出来。用户表的Rs!table_type值为SYSTEM TABLE,用户表的Rs!table_type值为TABLE,注意一定是大写的。用if Rs!table_type=TABLE就可以筛选出用户表啦。哈哈,觉得我的方法还可以的话就给我加分吧,我还没尝过得到专家分的滋味哈。
private sub GetAllTab()
Dim Rs As ADODB.Recordset
Dim ConnStr As String
Dim StrTab As String
Dim strsql As String
Dim i As Long
On Error GoTo HError
strsql = "select s1 = o.name, s2 = user_name(o.uid)"
strsql = strsql & " from dbo.sysobjects o, dbo.sysindexes i"
strsql = strsql & " where OBJECTPROPERTY(o.id, N'IsTable') = 1"
strsql = strsql & " and i.id = o.id"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsSystemTable') = 0"
strsql = strsql & " and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0"
strsql = strsql & " and i.indid < 2"
strsql = strsql & " and o.name not like N'#%'"
strsql = strsql & " order by s1, s2" ConnStr = "你的连接"
ConnStr = "driver=sql server;server=dengchao2;uid=sa;pwd=;database=tt"
Set Rs = New ADODB.Recordset
With Rs
.ActiveConnection = ConnStr
.CursorLocation = adUseClient
.Open strsql
End With
For i = 1 To Rs.RecordCount
List1.AddItem Rs!s1 & ""
Rs.MoveNext
Next
Rs.Close
Set Rs = Nothing
HError:end sub
http://www.csdn.net/expert/Topic/33923.shtm
方 法 1: 引用 ADO
Dim adoConnectionX As New ADODB.Connection
Dim adoSchemaRecordsetX As New ADODB.Recordset
'Ms SQL 7:
'adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"
'Access 2000:
adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Set adoSchemaRecordsetX = adoConnectionX.OpenSchema(adSchemaTables)
Do until adoSchemaRecordsetX.EOF
Debug.Print "Table name: " & _
adoSchemaRecordsetX !TABLE_NAME & vbCr & _
"Table type: " & adoSchemaRecordsetX!TABLE_TYPE & vbCr
adoSchemaRecordsetX.movenext
Loop
方 法 2(引 用 Microsoft ADO Extensions 2.1 for DDL and Security (ADOX)):
( 需 要 升 级 至 VB 6.0 SP3+)
Dim adoConnectionX As New ADODB.Connection
'Ms SQL 7:
adoConnectionX.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=yuer;DataBase=NorthwindCS"
'Access 2000:
'adoConnectionX.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=E:\DRptPlus\DRptPlus\Data\NWind2K.mdb;Persist Security Info=False"
Dim adoxCatalogX As New ADOX.Catalog
Set adoxCatalogX.ActiveConnection = adoConnectionX
Dim adoxTableX As ADOX.Table
For Each adoxTableX In adoxCatalogX.Tables
debug.print adoxTableX.name
Next