Databases Reference
In-Depth Information
Next ii
GetTableNames = UBound(GetSchema)
' Close the connection to the NWind data source.
SQLClose (Chan)
Exit Function
No_Database:
MsgBox SQLOpenString, vbOKOnly + vbCritical, "Error: Cannot
Open Database"
GetTableNames = 0
' Close the connection to the NWind data source.
SQLClose (Chan)
End Function
This function can be tested quite easily on the sample MCS.mdb database by placing it in the
folder “C:\temp\ADA\MCS.mdb” and utilizing the following code snippet:
Sub TestGetTableNames()
Dim Tables()
Debug.Print GetTableNames("DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=C:\temp\ADA\MCS.mdb", Tables())
For i = 1 To UBound(Tables)
Debug.Print i; Tables(i)
Next i
End Sub
Doing so will result in the following Table Names printed to the screen:
1.
MSysAccessObjects
2.
MSysACEs
3.
MSysObjects
4.
MSysQueries
5.
MSysRelationships
6.
Employees
7.
Products
8.
Suppliers
The first five table names are system tables.
hold the names of forms, reports, queries,
and tables within a particular database. System tables are readily identifiable as they will be prefixed
with “MSys.”
The names of forms, reports, queries, and tables within a database can be accessed using the
MSysObjects. For example, the following query will select all the forms:
System tables
select Name, DateUpdate from MSysObjects
where Type=
−
32768;
Queries and tables are a little problematic because they can be generated by the system! Often it
is useful to limit returned values to just those created by a developer and suppress entities that are
self generated. A query such as this will accomplish this task:
