Databases Reference
In-Depth Information
Chan = SQLOpen(SQLOpenString)
'Build SQL to Create a new Table
SQLStatement =
"ALTER
TABLE " & TableName & " ADD COLUMN ["
& FieldName & "]
TEXT;"
'Create the Table
SQLExecQuery Chan, SQLStatement
'Close the connection to the database
SQLClose (Chan)
CreateFieldSQL = True
Exit Function
Table_Exists:
Debug.Print Error$, Err
MsgBox SQLOpenString, vbOKOnly + vbCritical, "Error: The Table
Name Already Exists!"
CreateFieldSQL = False
'Close the connection to the database
SQLClose (Chan)
End Function
Notice that the SQL Statement generated and utilized in the above function makes use of the
ALTER command to generate new columns (or fields) in an existing table. Further notice that the
type of column to be added (data type) is specified at the end of the SQL statement. The data type
at the end of this sample statement can be altered to reflect whatever data type the user wishes for
the field to be created.
Removing fields from an existing table adds one more degree of complexity to the operation
in that the existing field names must be identified and presented to the user. In this instance, a
second combo box is utilized to hold all the field names for the table of interest. The function
was created to return the number of fields in a specified table and pass a string
array back that contains the names of all the fields in the specified table. If an error occurs, the
specified table does not exist, or the specified table cannot be accessed, the function returns 0 and
the string array is returned empty.
GetFieldNames
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)
