Obtain the number of tables in the ACCESS database and the table name [add to favorites]

'Function: gets the number of tables in the ACCESS database and the table name.
'How to implement with ADO
'Project ---> reference ---> Microsoft ActiveX Data Object 2.x( Version)
'----------------------------------------------------------------------------
Private sub form_load ()
Dim adocn as new ADODB. Connection 'defines the database 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

Method 2:

 

The Access System Table msysobjects contains a list of database objects. Although it is not recorded in the document, you can query it to obtain what you want. However, the system table cannot be operated by default. You must manually set the permission to receive the table.
See the following Animation: how to obtain operation permissions for msysobject
Http://access911.net/eg/swf/msobject.swf

Warning do not modify any access system table. Otherwise, unexpected conditions may occur.

Use the following SQL statement to obtain

Query:
Select msysobjects. name from msysobjects where (left ([name], 1) <> "~ ") And (msysobjects. Type) = 5 order by msysobjects. Name;

Form:
Select msysobjects. name from msysobjects where (left ([name], 1) <> "~ ") And (msysobjects. Type) =-32768 order by msysobjects. Name;

Table:
Select msysobjects. name from msysobjects where (left ([name], 1) <> "~ ") And (left $ ([name], 4) <>" msys ") and (msysobjects. Type) = 1 order by msysobjects. Name;

Report:
Select msysobjects. name from msysobjects where (left ([name], 1) <> "~ ") And (msysobjects. Type) =-32764 order by msysobjects. Name;

Module:
Select msysobjects. name from msysobjects where (left ([name], 1) <> "~ ") And (msysobjects. Type) =-32761 order by msysobjects. Name;

MACRO:
Select msysobjects. name from msysobjects where (left ([name], 1) <> "~ ") And (msysobjects. Type) =-32766 order by msysobjects. Name;