feature: Gets the number of tables in an Access library and the name of the table
How to implement with ADO
Project---> Reference--->microsoft ActiveX Data Object 2.x (version number)
'--- -------------------------------------------------------------------------
Private Sub form_load ()
Dim ADOCN As New ADODB. Connection ' defines the connection to the database
Dim strcnn as New ADODB. The Recordset
Dim Rstschema as New ADODB. The 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
Rstsch Ema. Close
Adocn.close
Debug.Print out
End Sub
Method Two:
The Access system table msysobjects contains a list of database objects. Although not documented in the document, you can still query it to get what you want. However, by default, the operating system table is not available and you must manually set permissions to query the system tables.
Please refer to the following animation: How to obtain permission to operate on Msysobject
http://access911.net/eg/swf/msobject.swf
Warning: Do not modify any access system tables, otherwise unexpected situations can occur.
Use the following SQL statement to get what you want
Inquire:
SELECT msysobjects.name from Msysobjects WHERE ([name],1) <> ' ~ ') and (Msysobjects.type) =5 ORDER by msysobjects . Name;
Form:
SELECT msysobjects.name from Msysobjects WHERE ([name],1) <> ' ~ ') and (Msysobjects.type) =-32768 ORDER BY Msysobjects.name;
Table:
SELECT msysobjects.name from Msysobjects WHERE ([name],1) <> "~") and (left$ ([name],4) <> "Msys") and (MSy Sobjects.type) =1 order by Msysobjects.name;
Report:
SELECT msysobjects.name from Msysobjects WHERE ([name],1) <> ' ~ ') and (msysobjects.type) = -32764 ORDER by Msysob Jects. Name;
Module:
SELECT msysobjects.name from Msysobjects WHERE ([name],1) <> ' ~ ') and (msysobjects.type) = -32761 ORDER by Msysob Jects. Name;
Macro:
SELECT msysobjects.name from Msysobjects WHERE ([name],1) <> ' ~ ') and (msysobjects.type) = -32766 ORDER by Msysob Jects. Name;