Access Data Definition Language - Creating and Altering Tables On The Fly

One of the first things an Access developer learns is how to write SQL Queries. A simple request for data might look something like this:
SELECT FullName, Address, Phone
FROM tblEmployee ORDER BY FullName
This type of query uses Data Manipulation Language (DML). While this example does not perform any great manipulation of the data, some very clever calculations are possible. The SQL language is powerful indeed.
However, once you have gotten a handle on DML, you will want to explore the world of DDL, Data Definition Language. This too is part of the SQL language and can be run from an Access query, or through VBA code, but unlike DML, these commands do not return a result set. DDL is used to create and alter database objects, such as tables. First, let's see how this is done and then consider some scenarios where this technique will save you time and in some cases, a lot of work.

The process to create a DDL query is a little different from what you might be used to. Begin in the normal way, by choosing New Query from the Query window, but when prompted, do not add any tables. You are then presented with the QBE (query by example) grid with no tables. Select Data Definition from the Query | SQL Specific menu to continue.

Selecting Data Definition will take you to a very plain, Notepad-like interface where you can paste or type in your DDL SQL script. Save the query and select Run from the Query menu to execute the script. When you run this kind of query, Microsoft Access displays the following warning:

PROBLEM:An employee table needs to have their salary updated based from an imported excel file containing the Global ID of the Employees with matching Salary changes. When importing the Excel spreadsheet it automatically set the data type of the Global ID field into numbers. This creates an error when doing an update because the Access table to be updated contains a Global ID field that is preset to data type text.

SUGGESTION:Change the Global ID Data type of the imported Excel Spreadsheet into Text on the fly, before executing the update command.

SOLUTION:
1. Invoke a query. From Object Window click Queries, Click New, Design View, OK, Close, Click Query Grid. From the Main Menu click Query, SQL Specific, Data Definition.
2. Inside the query window Type: ALTER TABLE [Source Table] ALTER COLUMN GID Text;
3. Save the query. With my example I saved it as AlterSourceTableQ
4. Call the query inside your procedure:

Private Sub CMDImportEmpFile_Click()
Dim SQL As String
DoCmd.SetWarnings False

SQL = "DELETE [Source Table].* " & _
"FROM [Source Table];"

DoCmd.RunSQL SQL


DoCmd.OpenQuery "AlterSourceTableQ"
DoCmd.OpenQuery "UpdateSourceFromImportedFileQ"
DoCmd.SetWarnings True
End Sub

To find out more....Click here