Databases Reference
In-Depth Information
End If
'Debug.Print Tables(i)
Next i
End Sub
The GetTableNames function returns an integer that is the total number of tables present in the
selected database (including system tables). The names of the tables are returned by means of a
passed by reference array variable TableNames(). It is the XLODBC function named
SQLGetSchema
that actually returns the table names from the selected database.
Function
GetTableNames
(SQLOpenString As String, TableNames())
As Integer
'Returns the Total Number of Tables and their Names in
TableNames
Dim Chan As Variant
Dim TName As Variant, GetSchema() As Variant
Dim DatabaseName As Variant
On Error GoTo No_Database:
'Establish a connection to the data source.
Chan = SQLOpen(SQLOpenString)
'Get the name of the database on the connection
DatabaseName = SQLGetSchema(Chan, 7)
'Get the table names for the connected database
GetSchema = SQLGetSchema(Chan, 4, DatabaseName & ".")
For ii = 1 To UBound(GetSchema)
ReDim Preserve TableNames(ii)
TableNames(ii) = GetSchema(ii, 1)
Next ii
GetTableNames = UBound(GetSchema)
' Close the connection to the 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
function simply takes a table name as a passed parameter and compares
it to a list of known system table names. If the table is a system table, the function returns the
Boolean value True, or else it returns False.
The
IsSystemTable
Function
IsSystemTable
(TableName As String) As Boolean
'Identifies MS Access System Tables
Select Case TableName
Case "MSysAccessObjects"
