Databases Reference
In-Depth Information
Select Name, DateUpdate from MSysObjects where Type=5 and Name not Like ''~*.” Like
many self-generated operating system files, self-generated tables and queries in an Access database
are preceded by a tilde “~,” and this is how the query just presented identifies and suppresses them.
When all the Table Names are returned utilizing the above
function, it is
important to have a means of identifying system tables, as the developer would most likely want
to prohibit access to them. The next function accomplishes this:
GetTableNames
Function IsSystemTable(TableName As String) As Boolean
'Identifies MS Access System Tables
Select Case TableName
Case "MSysAccessObjects"
IsSystemTable = True
Case "MSysACEs"
IsSystemTable = True
Case "MSysObjects"
IsSystemTable = True
Case "MSysQueries"
IsSystemTable = True
Case "MSysRelationships"
IsSystemTable = True
End Select
End Function
Once the names of the tables that reside within the database have been determined, a method
of determining the names of the fields that reside within those tables must be developed. The
function
is similar to its predecessor function, except that it operates on a single
specified table identified in the passed parameter
GetFieldNames
function
returns an integer number of the total fields that reside within the specified table. The passed-by
reference parameter
. The
TableName
GetFieldNames
is an array that is updated within the function to contain the
names of the fields that reside within the given table.
FieldNames
Function GetFieldNames(SQLOpenString As String, TableName As
String, FieldNames()) As Integer
'Returns Total Number of Fields in Table
Dim Chan As Variant
Dim Fields As Variant
Dim i As Integer
On Error GoTo No_Table:
'Establish a DSN-less connection to the MCS Sample Data Base
Chan = SQLOpen(SQLOpenString)
'Get the field names for the TableName passed parameter
Fields = SQLGetSchema(Chan, 5, TableName)
'Fields is a two-dimension Variant array; (name, type)
For i = 1 To UBound(Fields)
TotalFieldNames = TotalFieldNames + 1
ReDim Preserve FieldNames(1 To TotalFieldNames)
FieldNames(TotalFieldNames) = Fields(i, 1)
