Introduction to Jet SQL s DDL

Introduction to Jet SQL's DDL

In addition to row-returning queries and action queries, Jet SQL also supports a data definition language (DDL). Developers can use this language to create database objects, such as tables and queries. Because DDL and ADOX both let you create database objects, you can use them interchangeably in many circumstances. DDL offers a series of statements for creating and modifying objects. For example, you use the CREATE TABLE statement to make a new table; you run an ALTER TABLE statement to modify an existing table; and you run a DROP TABLE statement to delete a table. Because DDL is not a hierarchical model and is not VBA-based, Access developers might prefer ADOX. Nevertheless, Jet SQL's DDL has a very intimate relationship with Jet databases, and you will find that learning its syntax yields many advantages, such as improved performance.

Creating a Table with Custom AutoNumber Settings

One advantage of using Jet SQL to create tables is that your application does not require a reference to the ADOX library. This is because you can run Jet SQL statements from Command objects. Recall that Command objects are members of the ADODB library. Because applications require the ADODB library for basic data access functions and Access creates a default reference to the library, the ADODB library is likely to be available without you having to perform any special measures, such as creating a reference.

The syntax for creating a table requires the CREATE TABLE keywords, followed by a table name . For example, to create a table named Contacts , specify a CommandText property equal to " CREATE TABLE Contacts ". This statement creates an empty table with no columns . The table name will appear in the Database window, but there will be nothing for the Datasheet view to show ”not even an empty row.

You need to add one or more columns in the CREATE TABLE statement. Column specifications appear in parentheses after the table name in this statement. Within parentheses after the table name, you separate column specifications with commas. At a minimum, you must designate a column name and data type for each column. You are likely to reference the INTEGER and the CHAR data types. An INTEGER data type can have an IDENTITY setting. This enables the column to behave as though it contained AutoNumber field values. However, you have more control over AutoNumber field values through the Jet SQL IDENTITY setting than through the Access user interface because you can specify start and step values. Column specifications can also include constraints that restrict the column values. For example, you can include the PRIMARY KEY keywords after the data type for a column to indicate that the column is a primary key for a table.

The following code sample illustrates a very basic task: creating a table named Contacts . The table has two columns, one named ContactID and the other named ContactName . The ContactID column has an INTEGER data type with an IDENTITY setting. Recall that these settings correspond to an AutoNumber data type setting. As is typical for columns with AutoNumber data types, the ContactID column is a primary key. The ContactName field has a CHAR data type with a maximum size of 50 characters .

This sample does considerably more than just create the Contacts table; it also adds data and prints the table. Creating the table requires just two steps. First, you need to specify a connection. You can accomplish this by instantiating a Connection object and then opening the object so that it points at a database file. Second, you need to instantiate a Command object, set its properties, and then execute the command. Because the Command object includes an ActiveConnection property, you can omit the first step if you set the command's ActiveConnection property directly. The CommandText property accepts the SQL statement. This statement implements the table design described in the preceding paragraph.

 SubSetStartAndStep() OnErrorGoToStartAndStep_Trap Dimcnn1AsADODB.Connection Dimcmd1AsADODB.Command DimstrSrcAsString DimstrSQLAsString DimstrTempAsString     'Instantiateaconnection,andpointitatadatabase. Setcnn1=NewADODB.Connection strsrc="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb" cnn1.OpenstrSrc     'Instantiateacommandforaddingadatabaseobject 'andaddingdatatothedatabase. Setcmd1=NewADODB.Command Withcmd1 .ActiveConnection=cnn1 'Firstcreateatablewithtwocolumns. 'AssignIDENTITYsettingtoPRIMARYKEYcolumn. 'Setitsstartvalue(2)anditsstepvalue(4). .CommandType=adCmdText .CommandText="CREATETABLEContacts(ContactIDINTEGER"&_ "IDENTITY(2,4)PRIMARYKEY,ContactNameCHAR(50))" .Execute     'Aftercreatingthetablewiththeautoincrement/identity 'column,youshouldadddata. .CommandText="INSERTINTOContacts(ContactName)"&_ "Values('KevinCharney')" .CommandType=adCmdText .Execute .CommandText="INSERTINTOContacts(ContactName)"&_ "Values('DougFink')" .CommandType=adCmdText .Execute .CommandText="INSERTINTOContacts(ContactName)"&_ "Values('NeilYoder')" .CommandType=adCmdText .Execute EndWith     'Closeconnectiontoflushchangesthrough cnn1.Close Setcnn1=Nothing     'EchoContactstabletoImmediatewindowusing 'queryselectingallfieldsfromtheOrderstable. strSQL="SELECT*FROMContacts" PreviewRecordsetSourcestrSQL     StartAndStep_Exit: Setcmd1=Nothing ExitSub     StartAndStep_Trap: IfErr.Number=-2147217900Then 'Iftableexists,dropit 'beforeresuming. strTemp=cmd1.CommandText cmd1.CommandText="DROPTABLEContacts" cmd1.Execute cmd1.CommandText=strTemp Resume Else Debug.PrintErr.Number,Err.Description MsgBox"Error;seeImmediate window diagnostics.",_ vbCritical,"ProgrammingMicrosoftAccessVersion2003" EndIf     EndSub 

This sample also implements an error trap for trying to create the Contacts table if it already exists in a database. In this case, the procedure saves the CommandText setting in a string variable, strTemp . Then, it executes a command to drop the old version of the Contacts table from the database. Finally, the trap copies the strTemp string back to the CommandText property and resumes at the point that generated the error.

After the first Execute method creates the table, the procedure adds three contacts to the table using the INSERT INTO keywords so that we can observe the behavior of the AutoNumber data type in the ContactID column. Notice from the Jet SQL statement for the table that the AutoNumber values have a start value of 2 and a step value of 4. Therefore, the ContactID column values should be 2, 6, and 10. This behavior is not possible with the Access user interface, which always has start and step values of 1. After populating the table with records, the procedure prints the records to the Immediate window to confirm the results.

Changing AutoNumber Settings

One especially cool feature of Jet SQL is its ability to reset AutoNumber columns, such as ContactID , on the fly. You can reset both the start and the step values. To perform this task, alter the column settings for the AutoNumber column. You can do this by embedding an ALTER COLUMN statement within an ALTER TABLE statement. After the ALTER COLUMN statement, designate the column name. Follow that with the IDENTITY keyword, adding the start and step values in parentheses. You can execute the ALTER TABLE statement with its nested ALTER COLUMN statement from either a Command or a Connection object.

The next sample demonstrates the syntax for altering an AutoNumber column on the fly. It starts by instantiating a connection and pointing it at the database with the AutoNumber column to update. Next, it sets new start and step values for the AutoNumber column and passes these values, the database connection, and the AutoNumber column name to the ResetCounter procedure. ResetCounter constructs a string with the proper syntax for the ALTER TABLE statement and executes the statement using the Execute method for the connection. When ResetCounter returns control to the procedure that called it, the sample inserts three more records into the Contacts table. Then, the code prints the contents of a recordset based on the Contacts table to the Immediate window. This output starts with three records that use the initial start and step values of 2 and 4. Then, the output shows three additional records with start and step values of 100 and 10. See Figure 4-6 for the display of the records in the Immediate window. Note that the start and step values you specify should not cause duplicate indexes; otherwise , the new records you add will be rejected.

 SubCallAndDemoResetCounter()  Dimcnn1AsADODB.Connection Dimcmd1AsADODB.Command Dimint1AsInteger Dimint2AsInteger Dimstr1AsString     'Instantiateaconnectionandpointitatadatabase. Setcnn1=NewADODB.Connection cnn1.Open"Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource=C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb"     'ResetAutoNumberstart(int1)andstep(int2)values;then 'passthesealongwithtablenameanddatabaseconnectionto 'theproceduretoresetthecounterforatable. int1=100 int2=10 str1="Contacts" ResetCounterint1,int2,cnn1,str1     'Adddatawithnewstartandstepsettings. Setcmd1=NewADODB.Command Withcmd1 .ActiveConnection=cnn1     .CommandText="INSERTINTOContacts(ContactName)"&_ "Values('TonyHill')" .CommandType=adCmdText .Execute .CommandText="INSERTINTOContacts(ContactName)"&_ "Values('VirginiaDobson')" .CommandType=adCmdText .Execute .CommandText="INSERTINTOContacts(ContactName)"&_ "Values('DeeSimmons')" .CommandType=adCmdText .Execute EndWith     'Closeconnectiontoflushchangesthrough cnn1.Close Setcnn1=Nothing     'EchoContactstabletoImmediatewindow. str1="SELECT*FROMContacts" PreviewRecordsetSourcestr1     'Cleanupobjects. Setcmd1=Nothing     EndSub     SubResetCounter(int1AsInteger,_ int2AsInteger,cnn1AsADODB.Connection,_ str1AsString) Dimstr2AsString     'Revisestartandstepautoincrementvalues 'accordingtoint1andint2settings/ str2="ALTERTABLE"&str1&vbCrLf&_ "ALTERCOLUMNContactIDIDENTITY("&_ int1&","&int2&")"     cnn1.Executestr2     EndSub 

Figure 4.6: Output from the CallAndDemoResetCounter procedure that confirms programmatic changes to start and step AutoNumber values.

Creating Check Constraints for Columns

Column check constraints enable the designation of a range of legitimate values for a column through an expression. The Access user interface (and the Data Access Objects [DAO] model) allows you to set these constraints through the validation rule property. The ADOX library exposes a validation rule property as well, but this property is buried in the provider-specific properties for Column objects. ADO developers using Jet SQL's DDL can take advantage of the CHECK keyword when creating or altering a table. The CHECK keyword enables you to write an expression that restricts the range of legitimate values for a field. Although the expression constrains the values in a column and acts like a normal validation rule, it does not appear within the Access user interface.

Note  

In ADOX, the syntax for adding provider-specific properties has a somewhat different appearance than that of standard property settings. Here's how you might create a validity rule and its corresponding text in ADOX:

 col.Properties("JetOLEDB:ColumnValidationRule").Value= ">Date()"_ col.Properties("JetOLEDB:ColumnValidationText").Value=_ "Mustbegreaterthantoday." 

The next sample shows the syntax for creating a column check constraint with the CHECK keyword and demonstrates the constraint's behavior. In the first CommandText string, notice the CHECK keyword at the end of the table definition. A comma separates it from the last column definition, which denotes the EAddress column. This column contains e-mail addresses, and the column values should therefore include the at sign (@). The CHECK keyword and expression causes Access to use this rule to verify the value for the EAddress column before accepting a row into the table. A failure of the rule generates a run-time error with a number of -2147467259. An error trap toward the end of