Data Definition Language: SQL

Data Definition Language (DDL)

Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.

Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.

I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.

But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.

In this three-part series, I'll be addressing both SQL and DAO.

• SQL vs. DAO
• DDL Using SQL (this post)
• DDL Using DAO
• You can download this whole series in a single document with sample database, here: http://www.rogersaccesslibrary.com/forum/topic570_post587.html

 

DDL Using SQL

The SQL DDL statements can be executed in a query window, but the most useful way to do it is in VBA code. To do that, I need to build a string holding the SQL statement and run it using the Execute method of the database object. There are two ways to do this. The first (and simplest) is to run it directly against the built-in CurrentDb object:

Dim strSQL As String
strSQL = "<The SQL Statement here>"
CurrentDb.Execute strSQL

The other is a little more complicated, but in my opinion better. Create a database object and set it to the CurrentDb:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "<The SQL Statement here>"
db.Execute strSQL

This method is particularly useful when executing multiple SQL statements as I'll show later.

Drop Table

CurrentDb.Execute "DROP TABLE BOOKSQL;"

Create Table

'create a table with 4 fields and Primary key

Dim strSQL As String
strSQL = "CREATE TABLE BOOKSQL "
strSQL = strSQL & "(ISBN TEXT(13) CONSTRAINT PKey PRIMARY KEY, "
strSQL = strSQL & "Title TEXT(100), "
strSQL = strSQL & "Price CURRENCY, "
strSQL = strSQL & "PubID TEXT(10)); "
CurrentDb.Execute.Execute strSQL

Datatype key words:

• Autonumber -- AUTOINCREMENT
• Text -- TEXT(<length>)
• Memo and Hyperlink - MEMO
• Byte -- BYTE
• Integer -- SHORT
• Long integer -- LONG
• Single -- SINGLE
• Double -- DOUBLE
• ReplicationID -- GUID
• Date/Time -- DATETIME
• Currency -- CURRENCY
• Yes/No -- LOGICAL
• OleObject -- OLEOBJECT

Alter Table

CurrentDb.Execute "ALTER TABLE PublisherSQL ADD PubAddress TEXT (50);"

CurrentDb.Execute "ALTER TABLE BookSQL ADD Cost CURRENCY;"

CurrentDb.Execute "ALTER TABLE BookSQL ADD PubDate DATETIME;"

Drop Index

CurrentDb.Execute "DROP INDEX idxTitle ON BookSQL;"

Create Index

CurrentDb.Execute "CREATE UNIQUE INDEX idxTitle ON BookSQL (Title);"

Create Primary Key

CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON Books (BookID) WITH PRIMARY;"

Drop Relationship

CurrentDb.Execute "ALTER TABLE BookSQL DROP CONSTRAINT PubBook;"

Create Relationship

Dim strSQL As String
strSQL = "ALTER TABLE [BookSQL] "
strSQL = strSQL & " ADD CONSTRAINT [PubBook] FOREIGN KEY (PubID) "
strSQL = strSQL & " REFERENCES PublisherSQL (PubID);"
CurrentDb.Execute strSQL

Examples:

Sub DropTables()
'deletes a Relationship and two tables
Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "ALTER TABLE BookSQL DROP CONSTRAINT PubBook;"
    db.Execute "DROP TABLE PublisherSQL;"
    db.Execute "DROP TABLE BookSQL;"
Set db = Nothing
End Sub

'---------------------------------

Sub BuildTables()
' creates two tables and a Relationship between them
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
'create PublisherSQL table
    strSQL = "CREATE TABLE PublisherSQL "
    strSQL = strSQL & "(PubID TEXT(10) CONSTRAINT "
    strSQL = strSQL & "PrimaryKey PRIMARY KEY, "
    strSQL = strSQL & "PubName TEXT(100), "
    strSQL = strSQL & "PubPhone TEXT(20));"
    db.Execute strSQL
'create BookSQL table
    strSQL = "CREATE TABLE BookSQL "
    strSQL = strSQL & "(ISBN TEXT(13) CONSTRAINT PKey PRIMARY KEY, "
    strSQL = strSQL & "Title TEXT(100), "
    strSQL = strSQL & "Price MONEY, "
    strSQL = strSQL & "PubID TEXT(10)); "
    db.Execute strSQL
'create One-To-Many relationship between BookSQL and table
    strSQL = "ALTER TABLE [BookSQL] "
    strSQL = strSQL & " ADD CONSTRAINT [PubBook] FOREIGN KEY (PubID) "
    strSQL = strSQL & " REFERENCES PublisherSQL (PubID);"
   db.Execute strSQL
Set db = Nothing
End Sub

'---------------------------------

Sub DropIndex_Import_CreateIndex()
' this sample demonstrates removing an index, importing data, and
' re-creating the index.
Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "DROP INDEX idxTitle ON BookSQL;"
    DoCmd.TransferText acImportDelim, "", "Books", _
         CurrentProject.Path & "\Books.txt", True, ""
    db.Execute "create unique index idxTitle on BookSQL(Title);"
Set db = Nothing
End Sub

'---------------------------------

Sub ModifyField()
'you can't directly modify a field in a table you have to:
'1) create a new field with the new properties
'2) copy the data from the old field to the new field
'3) delete the old field
'Note: if the field to be deleted is part of an index, that
'index must be dropped and then re-established on the new field
Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "alter table [Books Copy] add Title2 text(100);"
    db.Execute "UPDATE [Books Copy] SET Title2 = Title;"
    db.Execute "alter table [Books Copy] drop column Title;"
Set db = Nothing
End Sub

Next time, I'll take a closer look at specific Data Definition Language using DAO.