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.

New Sample: ListFoldersAndFiles

By AD Tejpal

    This sample db demonstrates listing of folders and files contained within the selected top directory, as per desired file specifications. Based upon user's choice, subfolders can be included or ignored. If desired, more than one types of files can be included in a single file spec in the form of a comma separated string - e.g. :  "*.htm,*.pdf,*.txt" etc.

    Process Mode For Listing Folders and files:

    Two alternative methods for listing of folders and files, under a given top folder, are covered as follows:
    (a) Non-Recursive mode - using Dir() function. Apart from being faster than (b), it has the advantage that there is no extra strain on memory resources (otherwise associated with recursive approach), thus avoiding the risk of potential hang up in case of very large and deep directory tree.
    (b) Recursive mode - using FileSystemObject

    Display Of Listed Folders And Files:

    Each run for listing of folders and files is logged in table T_ProcessLog. On the viewing form, for the selected ProcessID, path and other details of topmost folder are displayed at top of the form. Similar details for the current subfolder are displayed just below the information for top-most folder.

    Subfolders and their files are displayed in adjacent subforms. For the current file, its details (like file type, size, attributes, DtCreated / DtLastModified / DtLastAccessed) are also displayed, apart from a hyperlink to the file itself. The hyperlink label becomes active only for permitted file types. The user can edit the contents of table T_AllowHyperLink for setting such  permissions. Three alternative styles of display are provided as follows:

    Style A - View Folders and Files In Hierarchical Chain:

    The user can drill down the directory tree by expanding any of the subfolders which then assumes the role of current main folder, resulting in display of subfolders and files held by the erstwhile subfolder. This can be done indefinitely, till the last subfolder at deepest nesting level is reached. Similarly, by pressing a command button, the user can move up the directory tree. The process can be repeated till the current main folder becomes identical to the top folder (i.e. the original top most folder for which the listing was generated).

    Style B - View All Folders At A Glance - And Their Files:

    For convenient viewing, the folders are sorted as per nesting level and path. The top-most folder (nesting level: zero) is highlighted in distinct color. For other folders, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.

    Style C - View All Files At A Glance:

    For convenient viewing, the files are sorted as per nesting level and path. Files in top-most folder (nesting level: zero) are  highlighted in distinct color. For other files, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.

    General module named basCommDlg, an adaptation from Access Developer's Handbook, has been kindly provided by Bill Mosca.

    Note:
    (a) While using file system object, all types of folders and files get covered (including System, Volume, Hidden, ReadOnly etc). On the other hand, while using Dir() function, such types don't get covered unless relevant arguments are explicitly supplied.

    (b) Using Dir command via DOS command prompt, listing of folders and files can be saved to a text file, which can then be imported into access table. Such a listing is quite fast and one might be tempted to try this route. However, there is a pitfall associated with this approach. If any special characters are present in the folder or file name (say in internet files), the same might not come through faithfully. For example ® is found to come across as r - resulting in corrupted path. 

Version: Access 2000 file format.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic568_post584.html

More samples by AD Tejpal: http://www.rogersaccesslibrary.com/forum/tejpal-ad_forum45.html

Searching for a Wildcard in a “LIKE” Criteria

In a query, the LIKE operator allows the user to query for a character or group of characters anywhere within a text field.  It does this by matching the field to a string which mixes the character(s) you want to match with wildcard characters like * and ?. Some examples:

• FirstName LIKE "C*"  will return any name that starts with a C (i.e. Carlton, Clark)
• LastName LIKE "*-*"  will return any last name that has a hyphen anywhere in the field (i.e. Flickema-Carlson, Smith-Jones)
• PONum LIKE "C????"   will return and PO number that starts with a C and has exactly 5 characters. (i.e. CSIDF, C24DG)
• PONum LIKE "C##"  returns all values that start with a C, is exactly 3 characters long, and characters 2 and 3 MUST be numbers. (i.e. C45, C16)

So how can I search for a wildcard character itself?  I can simply enclose the wildcard character with brackets [].

For instance, if I wanted to find any value that has an asterisk (*) anywhere in it, I could do this:

• PO LIKE "*[*]*"

If I wanted to find any value that starts with a hash mark(#), I could do this:

• Check LIKE "[#]*"

If I wanted to find any value ending with a question mark (?), I could do this:

• Comment LIKE "*[?]"
  

Showing Query Parameters in a Report

A parameter query is one which asks the user for input.  For instance, suppose I have a query that pulls records for a date range. Suppose further, I want the query to ask me for a Start Date and End Date for the range.  I can create a query like this:

SELECT * FROM MyTable WHERE TheDate BETWEEN [Enter Start Date] AND [Enter End Date]

Running this query will bring up two dialog boxes:

 

Entering the values in the boxes will return the records in that range.

But if I create a Report based on this query, how do I show the selected date range?  After all, reports show information from records in the query’s record source, and Parameter values aren’t included.

There are a couple of possibilities.

Method 1: Read Parameters Directly

The first is to read the parameter directly.  To do that, I just put a text box on my report for each query parameter. In the control source for the text boxes, I put the query parameter preceded by an equal sign.

For instance, in the case of the above query, the control sources for my two text boxes will be:
=[Enter Start Date] and =[Enter End Date]

Alternately, I could have a single text box with your dates concatenated:
=[Enter Start Date] & " - " & [Enter End Date]

I can also fancy it up a bit like this:
=”Date Range: “ & [Enter Start Date] & " to " & [Enter End Date]

Method 2: Read Values Returned

The second option is to read the actual minimum and maximum values from the records in the record source.  I can use the Min and Max functions of the report to do that.  Again, in the Control Source property of a text box:

=Min([Enter Start Date]) & " - " & Max([Enter End Date])

I can, of course, put them in separate text boxes as well.

Comparing The Methods

So what’s the difference between the two methods?

The first method returns the date range requested.  The second method returns the date range actually returned.

Isn’t that the same thing?  Not necessarily.  Suppose I request data from 1/1/2010 to 7/1/2011, but my table only has data starting with 2/1/2011.  The first method will return 1/1/2010 to 7/1/2011 (what I requested).  But the second method will return 2/1/2011 to 7/1/2011 (what’s actually in the record source of the report).

Microsoft Releases Office 2010 SP1

Office 2010 Service Pack 1 has been released by Microsoft.  See MS Knowledgebase article: http://support.microsoft.com/kb/2460049.  The service pack can be downloaded from the article as well.

The main improvements to Access appear to be in the area of bug-fixes and stability.  There are only three improvements listed in the KB article, but in the downloadable fix list there’s a much longer list of fixes.

Everybody has different priorities, but to my mind, here are some of the more important problems that were fixed:

• Access does not activate or return the user to the correct Ribbon tab for a previously opened database object when the user returns to that object.
• Access Wizards are not loaded correctly when "Disable all controls without notification" is selected in Trust Center.
• The program crashes when you apply a sort to a query that is based on a multi-value field.
• "Reserved error -5500" occurs when you try to run a cross-tab query that would generate null values in the column names of the query.
• "Object invalid or no longer set" error occurs when you try to use an ALTER TABLE query to change a field type or size.
• You cannot relink tables in Access databases that have linked tables to other MDBs/ACCDBs that cannot be found
• The file format that is displayed in the title bar for Access 2010 databases is "(Access 2007)."
• Incorrect data is displayed when a user's query has a list that includes a combination of GroupBy and either OrderBy or Where
• "Invalid precision for decimal data type" or results are truncated when the user runs a crosstab query.

There are also a lot of “crash fixes”, all of which are important.

New Sample: NormalizingRepeatingColumnsVBA.mdb

By Roger Carlson

This sample demonstrates how to normalize a table that has repeated columns with VBA.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet with code rather than SQL statements.

Full Article Included

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html

Normalizing Repeated Columns: VBA

In my earlier blog series (The Problem of Repeated Columns), I defined repeated columns and talked about the data integrity problems associated with them. I also showed several different examples of repeated columns and the how difficult it is to query repeated columns as compared to the normalized equivalent. If you haven't read these yet, it would be worthwhile to read first. Similarly, if you are not familiar with the concept of Normalization, you should read my blog series What is Normalization?

One of the comments suggested I should discuss how to convert a table with repeated columns into a normalized table structure. I thought that was a really good idea, so I'm going to spend the next few posts doing so. The difficulty in discussing this issue, however, is that the exact solution differs with each table, so there is no single solution. Fortunately, in the last series, I showed 5 different tables, each with a slightly different structure, so while I cannot show a single solution that will work for every table with repeated columns, hopefully, one of the following will work for most cases.

Others in this series:

• Single Repeated Column
• Multiple Repeated Columns
• Yes/No Fields
• Using VBA (this post)

In my previous examples, I used purely SQL solutions. This time, I thought I'd illustrate how to do this with VBA. There's no real advantage to using VBA over the SQL solutions. In fact, in large databases, it will almost always be slower. Nevertheless, there may be situations with complex data that VBA would be the best solution, and besides, it represents another tool in your Access toolkit.

In my post Aggregating Across Repeated Columns: Summing, I discussed an example of table with repeated columns that looked like this:

Figure 1: Student Scores table with repeated columns

Normalized to the First Normal Form (1NF) to remove the repeated columns, the table would look like this:

Figure 2: Patient Symptom table (1NF)

Figure 2, however does not represent the final form of normalization. Because the student names are repeated, they should be removed to a separate table. So normalizing to Third Normal Form (3NF) I would have something like this:

Figure 3: Normalized to Third Normal Form (3NF) - Tables and Relationships Views

(For a further explanation of both First and Third Normal Forms, see The Normal Forms , What is Normalization, and Entity-Relationship Diagramming).

Normalizing to First Normal Form (1NF)

I'll start with the 1NF because it's less complex. Overall, it's a matter of looping through the records in the denormalized table (i.e. StudentScores_RepeatedColumns) and writing each column value into a new record in the 1NF table (i.e. StudentScores_1NF).

Public Sub Normalize_RepeatedColumns_VBA_1NF(ParamArray FieldNames())
'   This routine writes data from a table with repeated columns into a
'   table normalized to the First Normal Form (1NF)
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.
'   declare variablesDim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
'   Open database object to current database

Set db = CurrentDb
'   Open the denormalized table to read the values

Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")
'   Open the normalized table write the values

Set rsTarget = db.OpenRecordset("StudentScores_1NF")
'   Loop through the denormalized Source table

Do While Not rsSource.EOF
    '   Loop through the fields, i.e. the values in the parameter array    

For i = LBound(FieldNames) To UBound(FieldNames)
        '   Add a New record to the target table, write the values,
        '   and save (update) the record

        rsTarget.AddNew
            '   write the student name

            rsTarget!StudentID = rsSource!Student
            '   write the field name

            rsTarget!TestNum = FieldNames(i)
            '   write the field value

            rsTarget!Score = rsSource(FieldNames(i))
        rsTarget.Update
     Next i
     rsSource.MoveNext
Loop
End Sub
You can call the subroutine like so:
Sub test1NF()

'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize
  Call Normalize_RepeatedColumns_VBA_1N _
       ("Test1", "Test2", "Test3", "Test4")
   
End Sub

The end result looks like Figure 2 above.


Normalizing to Third Normal Form (3NF)

The process for normalizing repeated column data into 3NF is similar to the 1NF process. It does, however, require two loops, one to add records to the "one-side" table ("Student") , and an inner loop (For...Next) to write records to the "many-side" table ("StudentScores").

It is important to note that the order in which the data is moved is vital. Data must be written into the "one-side" table first, and then data can be moved into the "many-side" table. Overall, the process is to loop through the records in the denormalized table, write common values to the a record in the one-side table, store the primary key value from the new record, and then for each field in the parameter array, create a new record in the many-side table.

Public Sub Normalize_RepeatedColumns_VBA_3NF(ParamArray FieldNames())
'   This routine writes data from a table with repeated columns into two
'   normalized tables: Students and StudentScores
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.
'   declare variables

Dim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTargetOneSide As DAO.Recordset
Dim rsTargetManySide As DAO.Recordset
Dim StudentIDtemp As Long
'   Open database object to current database

Set db = CurrentDb
'   Open the denormalized table to read the values

Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")
'   Open the "one-side" table to write the values

Set rsTargetOneSide = db.OpenRecordset("Student")
'   Open the "Many-side" table to write the values

Set rsTargetManySide = db.OpenRecordset("StudentScores")
'   Loop through the denormalized Source table

Do While Not rsSource.EOF
    '   Add a New record to the "one-side" target table, save
    '   the primary key value (autonumber) for use later,
    '   and save (update) the record

    rsTargetOneSide.AddNew
        rsTargetOneSide!Student = rsSource!Student
        '   save the StudentID created by the autonumber field

        StudentIDtemp = CLng(rsSource("StudentID"))
    rsTargetOneSide.Update
   
    '   Loop through the fields in the Parameter Array    

For i = LBound(FieldNames) To UBound(FieldNames)
   
        '   Add a New record to the "many-side" target table,
        '   write the values, and save (update) the record       

             rsTargetManySide.AddNew

            '   write the saved student id\

            rsTargetManySide!StudentID = StudentIDtemp
            '   write the field name

            rsTargetManySide!TestNum = FieldNames(i)
            '   write the field value

            rsTargetManySide!Score = rsSource(FieldNames(i))
        rsTargetManySide.Update
       
    Next i
    rsSource.MoveNext
Loop
End Sub
You can call the subroutine like so:
Sub test3NF()

'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize  
  Call Normalize_RepeatedColumns_VBA_3NF _
      ("Test1", "Test2", "Test3", "Test4")
   
End Sub
The end result looks like Figure 3 above.

You can find a sample which illustrates the above here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html