There are several ways in which you can create objects in a database. Below are outlines of the different techniques, this does not include the obvious method!

Using a Make Table Query

This is by far the simples way in which to create a table of data based upon existing tables of data. Not only can you create queries to do this, but you can also write this type of query from program code, and then execute the query. For some interesting variations on this see ACC2000: How to Create a Make-Table Query with a Union Query.

Importing data

When you import data, the data can be either appended to an existing table or used to create a new table. If you need tight control on how data is imported then take a look at specifications, and if you want to start automating this process then there are macro commands such as TransferText, TransferSpreadsheet and TransferDatabase to support these operations.

Developers will often start by using these macro commands to import data and then a series of queries to transfer the data to the applications main tables. Using VBA you can also use the RunCommand with the acCmdImport to activate the built menu import dialogs, or use the DoCmd.TransferText or DoCmd.TransferDatabase or DoCmd.TransferSpreadsheet.

DoCmd.TransferText acImportDelim, , "TestTable", "TheFile.txt", True

Top of Page

Using DDL : Data Definition Language

When it comes to creating tables you can if desired use SQL, a set of commands in SQL referred to as the DDL enables tables to be created. The only time I tend to use with Access, is when I need to manipulate structures in a web based database and I can't use a remote session on the server.

If you search in the help system for "CREATE TABLE" or "ALTER TABLE", then you will find examples of the syntax. In general creating tables, adding indexes and altering tables is fairly straight forward, but defining relationships requires slightly more complex syntax. One thing that is not very apparent from the help system is how to create a table with an autonumber data type, so here is a simple example of the SQL.

CREATE TABLE tblVisitLog (ActionId counter CONSTRAINT PrimaryKey PRIMARY KEY , DateLogged Date, IpAddress TEXT(20), BrowserType TEXT(15), BrowserVersion TEXT(10), Frames Logical, Cookies Logical, VBScript Logical, JScript Logical,IsRegistered Logical, Action TEXT(20))

To create queries which do this you need to be viewing the SQL in a query, and access does not have any wizards to help you with this process. See How to use common Data Definition Language (DDL) SQL statements for the Jet database engine.

Top of Page

Using DAO : Data Access Objects

The DAO hierarchy is the object model that defines the JET database engine which Access normally uses. DAO is a fantastic structure, and this was really the defining technology which made Access blow away the competition. It is not only fundamentally simple to use, but rich and diverse in its functionality.

However, when it comes to creating tables DAO involves a lot of steps. For example, to start creating a table you need to create a tabledef object, you then need to create each field and then append each field into the tabledef's fields collection. You will soon get bored with this process and likely wish you had never started. In contrast with this creating queries DAO is very simple. One advantage of using DAO to create tables is in setting properties which are not directly available through the SQL (such as validation rules), although you will need to invest substantial time in getting the initial code working.

For an example of using DAO to copy a table see How To Copy a DAO TableDef Including User-Defined Properties

Top of Page

Using ADOX : Active Data Objects Extension

If creating tables using DAO does not seem too daunting, then you can also have a go using ADOX, which is an extension to ADO supporting operations which is the addition to ADO that enables the database scheme and structure to be manipulated. To find out more visit Creating and Modifying Access Tables

Choosing ADO or DAO for Working with Access Databases

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz