Databases Reference
In-Depth Information
FIGURE 8.5
Deleting field names in tables.
'Build SQL to Create a new Table
SQLStatement = "ALTER TABLE " & TableName & " DROP " & FieldName
& ";"
'Create the Table
SQLExecQuery Chan, SQLStatement
'Close the connection to the database
SQLClose (Chan)
DeleteFieldSQL = True
Exit Function
Table_Exists:
Debug.Print Error$, Err
MsgBox SQLOpenString, vbOKOnly + vbCritical, "Error: The Table
Name Already Exists!"
DeleteFieldSQL = False
'Close the connection to the database
SQLClose (Chan)
End Function
Figure 8.5 shows the example program ready to delete the field YRSOFSERVICE in a table
named Employees in the sample MCS.mdb database using DAO.
8.6
ADDING RECORDS TO SPECIFIC FIELDS IN DATABASE TABLES
Having a database with specific tables and fields means nothing unless something is stored within
them. That “something” to be stored within the fields of the tables are individual records.
A sample application was constructed to show how records can be added to a database using
Excel VBA. Figure 8.6 shows a sample record consisting of a first, middle, and last names being
added to the sample MCS.mdb database, which is included on the accompanying CD-ROM
.
Keep in mind that a record may contain far more than three fields; in fact, it may contain as many
fields as there are columns in the database. Also note that not every field in a record must contain
data; it is perfectly acceptable to write values to only a subset of the given fields in a database.
SQL is by far the most efficient means of adding records into a database. This is because
using SQL allows the developer to populate a multitude of fields with a single SQL statement.
