Creating Tables

Using the Microsoft Jet 4.0 CREATE TABLE statement, you can easily create a table in a database. This statement can only be used with Microsoft Jet database engine databases. The two examples that follow illustrate how to create and delete a table named tblSchools in the currently open database and in a new database.

Part III

©Hands-On 19-2: Creating a Table in the Current Database

1. Open the Acc2003_Chap19.mdb file from the book's downloadable files or create this file from scratch using the Microsoft Office Access user interface.

2. Make sure that the ANSI SQL query mode is set to ANSI-92 (see Hands-On 19-1).

3. Switch to the Visual Basic Editor window and insert a new module.

4. In the module's Code window, enter the CreateTable procedure as shown below.

Sub CreateTable() ' you must set up a reference to ' the Microsoft ActiveX Data Objects Library ' in the References dialog box Dim conn As ADODB.Connection Dim strTable As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" conn.Execute "CREATE TABLE " & strTable & _ "(SchoolId AUTOINCREMENT(100, 5), " &_ "SchoolName CHAR," & _ "City Char (25), District Char (35), " & _ "YearEstablished Date);"

Application.RefreshDatabaseWindow ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

The procedure above specifies the seed and increment values of AutoNumber columns by using the following syntax:

Column_name AUTOINCREMENT (seed, increment)

Notice that the table, tblSchools, has an AutoNumber column with a seed of 100 and an increment of 5. When you switch to a Database window and open this table in the Datasheet view, the SchoolId for the first record will be 100, the second will be 105, the third will be 110, and so on.

When you examine the code of the CreateTable procedure and compare the resultant table in Figure 19-3, you will notice that Access SQL uses different data types than those available in the Table Design window. Table 19-1 presents the equivalent SQL data types.

Creating, Modifying, and Deleting Tables and Fields | 399 Programming with the Jet Data Definition Language

Figure 19-3: The tblSchools table was generated by the CreateTable procedure in Hands-On 19-2 using the SQL statement CREATE TABLE.
Table 19-1: Table Design data types and their Access SQL equivalents

Table Design Data Types

Access SQL Data Types

Text

TEXT, ALPHANUMERIC, CHAR, CHARACTER, STRING, or VARCHAR

Memo

LONGTEXT, MEMO, LONGCHAR, or NOTE

Number (Field Size = Byte)

BYTE or INTEGER1

Number (Field Size = Integer)

SHORT, INTEGER2, or SMALLINT

Number (Field Size = Long Integer)

COUNTER, INTEGER, INT, or AUTOINCREMENT

Number (Field Size = Single)

SINGLE, FLOAT4, or REAL

Number (Field Size = Double)

DOUBLE, FLOAT, or NUMBER

Date/Time

DATETIME, DATE, TIME, orTIMESTAMP

Currency

CURRENCY or MONEY

AutoNumber (Field Size = Long Integer)

AUTOINCREMENT or COUNTER

AutoNumber (Field Size = Replication Id)

GUID

Yes/No

BOOLEAN, BIT, LOGICAL, LOGICAL1, or YESNO

OLE Object

LONGBINARY, OLEOBJECT, or GENERAL

The procedure in Hands-On 19-3 demonstrates how to create a table in a brand new database.

©Hands-On 19-3: Creating a Table in a New Database

1. Switch to the Visual Basic Editor window and insert a new module.

2. In the module's Code window, enter the CreateTableInNewDb procedure as shown below.

Sub CreateTableInNewDb()

' use the References dialog box to set up a reference to ' Microsoft ADO Ext. for DDL and Security Object Library ' and Microsoft ActiveX Data Objects Library

Part III

Dim cat As ADOX.Catalog Dim conn As ADODB.Connection Dim strDb As String Dim strTable As String Dim strConnect As String

On Error GoTo ErrorHandler

Set cat = New ADOX.Catalog strDb = CurrentProject.Path & "\Sites.mdb" strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDb

' create a new database file cat.Create strConnect

MsgBox "The database was created (" & strDb & ")."

' set connection to currently open catalog Set conn = cat.ActiveConnection strTable = "tblSchools" conn.Execute "CREATE TABLE " & strTable & _ "(SchoolId AUTOINCREMENT(100, 5), " &_ "SchoolName CHAR," & _ "City Char (25), District Char (35), " & _ "YearEstablished Date);" ExitHere:

Set cat = Nothing Set conn = Nothing Exit Sub ErrorHandler:

If Err.Number = -2147217897 Then

' delete the database file if it exists Kill strDb

' start from statement that caused this error Resume 0

Else

MsgBox Err.Number & ": " & Err.Description GoTo ExitHere End If End Sub

The CreateTableInNewDb procedure shown above creates a new database named Sites.mdb in the current folder. As you recall from Chapter 10, you can create a new Microsoft Access database by using the Create method of the ADOX Catalog object. Before we can create a table in this new database we must set the conn object variable to the currently open catalog, like this:

Set conn = cat.ActiveConnection

Next, we use the Connection object's Execute method to create a new table named tblSchools in the Sites.mdb file. Notice that this table will contain an AutoNumber field with a sequence starting at 100 that will be incremented by 5 as new columns are added.

Programming with the Jet Data Definition Language

0 0

Post a comment