Communicating with Microsoft Jet by Using SQL DDL

You can pass SQL DDL statements to Microsoft Jet in three ways.

In Microsoft Access, you can use the RunSQL action or the RunSQL method of the DoCmd object to execute an SQL statement that acts on the current database. For example, the CREATE TABLE statement from the previous example can be executed in Microsoft Access by using the RunSQL method as follows:

DoCmd.RunSQL "CREATE TABLE MyTable (MyField TEXT);"

The DoCmd object and its methods are not available in other Microsoft Jet host languages such as Visual C++ and Visual J++, or when you are using Visual Basic for Applications in Visual Basic 5.0, Microsoft Excel 97, PowerPoint 97, or Word 97. In all of these languages and in Microsoft Access 97, you can execute SQL statements by using the Execute method on a Database object declared in code. In the following example, strDbPath is the path to the database, strTableName is the name of the new table, strFieldName is the name of the new field, and strFieldType is the type of field to create.

Dim dbs As Database
Dim strSQL As String

Set dbs = OpenDatabase(strDbPath)
strSQL = "CREATE TABLE " & strTableName & " (" & strFieldName _
	& " " & strFieldType & ");"

' Execute SQL command.
dbs.Execute strSQL

Note To use any DAO method, such as the Execute method, when you are using Visual Basic for Applications in Microsoft Excel, PowerPoint, or Word, you must first install the Data Access Objects for Visual Basic component by rerunning Setup. Then you can use the References command (Tools menu) in the Visual Basic Editor to set a reference to the Microsoft DAO 3.5 object library.

Another way to execute an SQL statement is to create a permanent DAO QueryDef object that contains the SQL statement, and then use the Execute method to run the QueryDef object in code. The following example creates a QueryDef object that includes a CREATE TABLE statement and then executes that QueryDef object. In this example, strDbPath is the path to the database and strQueryName is the name of the new QueryDef object:

Dim dbs As Database, qdf As QueryDef
Dim strSQL As String

Set dbs = OpenDatabase(strDbPath)
strSQL = "CREATE TABLE MyTable (MyField TEXT);"
Set qdf = dbs.CreateQueryDef(strQueryName)

' Execute query.
qdf.Execute

Note Another method of creating a table by using SQL is to use a make-table query. For a complete discussion of using make-table and other queries, see Chapter 4, “Queries.”

See Also For information about creating QueryDef objects, see the “Microsoft Jet QueryDef Objects” section later in this chapter.

For the remainder of this chapter, SQL DDL examples are given with the text of the SQL statement only. Remember, however, that to communicate your SQL statements to Microsoft Jet, you must execute the DDL statement by using one of the previously described techniques.