INORMATION_SCHEMA table does not exist in an access database :(
+1
A:
From http://databases.aspfaq.com/database/how-do-i-get-a-list-of-access-tables-and-their-row-counts.html
Getting a list of tables:
SELECT
Table_Name = Name,
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
ORDER BY
Name
Ilya Kochetov
2008-10-14 14:08:53
+1
A:
Schema information which is designed to be very close to that of the SQL-92 INFORMATION_SCHEMA may be obtained for the Jet/ACE engine (which is what I assume you mean by 'access') via the OLE DB providers.
See:
onedaywhen
2008-10-14 14:44:10
+1
A:
You can use schemas in Access.
Sub ListAccessTables2(strDBPath)
Dim cnnDB As ADODB.Connection
Dim rstList As ADODB.Recordset
Set cnnDB = New ADODB.Connection
' Open the connection.
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With
' Open the tables schema rowset.
Set rstList = cnnDB.OpenSchema(adSchemaTables)
' Loop through the results and print the
' names and types in the Immediate pane.
With rstList
Do While Not .EOF
If .Fields("TABLE_TYPE") <> "VIEW" Then
Debug.Print .Fields("TABLE_NAME") & vbTab & _
.Fields("TABLE_TYPE")
End If
.MoveNext
Loop
End With
cnnDB.Close
Set cnnDB = Nothing
End Sub
From: http://msdn.microsoft.com/en-us/library/aa165325(office.10).aspx
Remou
2008-10-14 18:38:44
+1
A:
To build on Ilya's answer try the following query:
SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~")
AND ((Left([Name],4))<>"MSys")
AND ((MSysObjects.Type) In (1,4,6)));
order by MSysObjects.Name
(this one works without modification)
CodeSlave
2008-10-14 19:40:37
Record(s) cannot be read; no read permission on 'MSysObjects'.
Ian Boyd
2009-07-05 15:59:20
A:
SELECT
Name
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
p.s. based on answer from Ilya Kochetov
Miroslav Stampar
2010-03-30 14:02:54
Does this return the same information as SQL-92 INFORMATION_SCHEMA? I'm assuming that more than a list of tables is needed, which is all querying MSysObjects will give you.
David-W-Fenton
2010-04-02 16:59:48