Get the number of tables in an Access library and the name of the table [favorites]

Access
' function: Gets the number of tables in the Access library and the name of the table
' How to implement with ADO
' Engineering---> Reference--->microsoft ActiveX Data Object 2.x (version number)
'----------------------------------------------------------------------------
Private Sub Form_Load ()
Dim ADOCN as New ADODB. Connection ' defines a connection to a database
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


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;