How to get the list of tables in a MS Access database using ADO?

[Raptors Fan]

Hi all,

New to ADO. I need to get the names of all the tables in an Access database using ADO and VB6.
I need to check if the table exists in the database before running a query done on the fly.

Thank you.

[John G Duffy]

Try this simple Print routine. Caution: It uses CommonDialog to get bith the INput dataBAse AND THE Output fileName, so don't accidentially overlay your DataBASe with the output Text file

private Sub cmdPrint_Click()
Dim adoConnection as ADODB.Connection
Dim adoRsFields as ADODB.Recordset
Dim sConnection as string
Dim sCurrentTable as string
Dim sNewTable as string
on error resume next

' get the name and location of the Data Base
CommonDialog1.DialogTitle = "Select the DataBase to map"
CommonDialog1.ShowOpen
If CommonDialog1.FileName = "" then Exit Sub

set adoConnection = new ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=" & CommonDialog1.FileName
adoConnection.Open sConnection
set adoRsFields = adoConnection.OpenSchema(adSchemaColumns)
sCurrentTable = ""
sNewTable = ""

' get the location of the output print file
CommonDialog1.DialogTitle = "Define the output file"
CommonDialog1.CancelError = true
CommonDialog1.ShowSave ' Where to output the file
If CommonDialog1.FileName = "" then Exit Sub
Open CommonDialog1.FileName for Output as #1
print #1, " "
print #1, Now & " " & sConnection
print #1, ""

Do Until adoRsFields.EOF
sCurrentTable = adoRsFields!TABLE_NAME
If (sCurrentTable <> sNewTable) then
sNewTable = adoRsFields!TABLE_NAME
print #1, ""
print #1, "Table: " & adoRsFields!TABLE_NAME
End If
If Left(UCase(adoRsFields!TABLE_NAME), 7) <> "MSYSMOD" then
print #1, Tab(8); " Field: " & adoRsFields!COLUMN_NAME

' the following statements will print Field characteristics

' Printer.print Tab(20); "TABLE_CATALOG: " & adoRsFields!TABLE_CATALOG
' Printer.print Tab(20); "TABLE_SCHEMA : " & adoRsFields!TABLE_SCHEMA
' Printer.print Tab(20); "TABLE_NAME : " & adoRsFields!TABLE_NAME
' Printer.print Tab(20); "COLUMN_NAME : " & adoRsFields!COLUMN_NAME
' Printer.print Tab(20); "COLUMN_GUID : " & adoRsFields!COLUMN_GUID
' Printer.print Tab(20); "COLUMN_PROPID: " & adoRsFields!COLUMN_PROPID
' Printer.print Tab(20); "ORDINAL_POSITION: " & adoRsFields!ORDINAL_POSITION
' Printer.print Tab(20); "COLUMN_HASDEFAULT: " & adoRsFields!COLUMN_HASDEFAULT
' Printer.print Tab(20); "COLUMN_DEFAULT: " & adoRsFields!COLUMN_DEFAULT
' Printer.print Tab(20); "COLUMN_FLAGS: " & adoRsFields!COLUMN_FLAGS
' Printer.print Tab(20); "IS_NULLABLE: " & adoRsFields!IS_NULLABLE
' Printer.print Tab(20); "DATA_TYPE: " & adoRsFields!DATA_TYPE
' Printer.print Tab(20); "TYPE_GUID: " & adoRsFields!TYPE_GUID
' Printer.print Tab(20); "CHARACTER_MAXIMUM_LENGTH: " & adoRsFields!CHARACTER_MAXIMUM_LENGTH
' Printer.print Tab(20); "CHARACTER_OCTET_LENGTH: " & adoRsFields!CHARACTER_OCTET_LENGTH
' Printer.print Tab(20); "NUMERIC_PRECISION: " & adoRsFields!NUMERIC_PRECISION
' Printer.print Tab(20); "NUMERIC_SCALE: " & adoRsFields!NUMERIC_SCALE
' Printer.print Tab(20); "DATETIME_PRECISION: " & adoRsFields!DATETIME_PRECISION
' Printer.print Tab(20); "CHARACTER_SET_CATALOG: " & adoRsFields!CHARACTER_SET_CATALOG
' Printer.print Tab(20); "CHARACTER_SET_SCHEMA: " & adoRsFields!CHARACTER_SET_SCHEMA
' Printer.print Tab(20); "CHARACTER_SET_NAME: " & adoRsFields!CHARACTER_SET_NAME
' Printer.print Tab(20); "COLLATION_Catalog: " & adoRsFields!COLLATION_CATALOG
' Printer.print Tab(20); "COLLATION_SCHEMA: " & adoRsFields!COLLATION_SCHEMA
' Printer.print Tab(20); "COLLATION_NAME: " & adoRsFields!COLLATION_NAME
' Printer.print Tab(20); "DOMAIN_CATALOG: " & adoRsFields!DOMAIN_CATALOG
' Printer.print Tab(20); "DOMAIN_SCHEMA: " & adoRsFields!DOMAIN_SCHEMA
' Printer.print Tab(20); "DOMAIN_NAME: " & adoRsFields!DOMAIN_NAME
' Printer.print Tab(20); "DESCRIPTION: " & adoRsFields!Description
End If
adoRsFields.MoveNext
Loop
Close #1
adoRsFields.Close
set adoRsFields = nothing
adoConnection.Close
set adoConnection = nothing
End Sub





John G

[Raptors Fan]

Thanks a lot for your help John G. Duffy.