Data Definition Language (DDL) DAO vs. 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 (this post)
• DDL Using SQL
• 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

DAO vs. SQL

In Access, the two major methods are DAO methods and SQL Statements. DAO (Data Access Objects) is the object model that Access uses to programmatically manipulate the database and its data. (There are other object models you can use like ADO and ADO.net, but DAO is recommended for use with Access.) However, in many cases, you can also manipulate your database with in SQL statements. In general, SQL is more efficient than other methods, so if you can, it's recommended.

SQL DDL statements have a number of advantages over other methods of modifying the database structure. For one thing, is independent of the object model you're using (ADO, DAO, ADO.Net) and can be executed from different platforms like VBA, C++, C#, and so forth. And while there are minor differences in implementation, DDL is fairly standard to most database platforms like Access, SQL Server, Oracle, and Sybase. It is also easier to read and understand.

For instance, in SQL, I can create a simple table like so:

Sub CreateTableSQL()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
    strSQL = "CREATE TABLE NewTable2 " & _
        "(NewField1 TEXT(100), " & _
        "NewField2 SINGLE);"
    db.Execute strSQL
End Sub

By comparison, in DAO, I need to do the following:

Sub CreateTableDAO()
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field
    Set tblNew = db.CreateTableDef("NewTable")
    Set fld = tblNew.CreateField("NewField1", dbText, 100)
    tblNew.Fields.Append fld
    Set fld = tblNew.CreateField("NewField2", dbSingle)
    tblNew.Fields.Append fld
    db.TableDefs.Append tblNew
End Sub

SQL DDL statements have a number of disadvantages as well. Most importantly, you cannot set a number of Access specific table properties, like Validation Rules, Validation Text, Default values, and so forth. To do that, you have to use an object model like DAO.

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