Microsoft SQL Server and MySQL have an INFORMATION_SCHEMA table that I can query. However it does not exist in an MS Access database.
Is there an equivalent I can use?
This question is related to
ms-access
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:
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
SELECT
Name
FROM
MSysObjects
WHERE
(Left([Name],1)<>"~")
AND (Left([Name],4) <> "MSys")
AND ([Type] In (1, 4, 6))
ORDER BY
Name
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:
Here is an updated answer which works in Access 2010 VBA using Data Access Objects (DAO). The table's name is held in TableDef.Name. The collection of all table definitions is held in TableDefs. Here is a quick example of looping through the table names:
Dim db as Database
Dim td as TableDef
Set db = CurrentDb()
For Each td In db.TableDefs
YourSubTakingTableName(td.Name)
Next td
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
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
Here is an updated answer which works in Access 2010 VBA using Data Access Objects (DAO). The table's name is held in TableDef.Name. The collection of all table definitions is held in TableDefs. Here is a quick example of looping through the table names:
Dim db as Database
Dim td as TableDef
Set db = CurrentDb()
For Each td In db.TableDefs
YourSubTakingTableName(td.Name)
Next td
Best not to mess with msysObjects (IMHO).
CurrentDB.TableDefs
CurrentDB.QueryDefs
CurrentProject.AllForms
CurrentProject.AllReports
CurrentProject.AllMacros
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
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:
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
Best not to mess with msysObjects (IMHO).
CurrentDB.TableDefs
CurrentDB.QueryDefs
CurrentProject.AllForms
CurrentProject.AllReports
CurrentProject.AllMacros
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:
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
Source: Stackoverflow.com