Table Defs and Fields

TableDefs correspond to tables in the interface. Although it is much more common to need to create a query programmatically, sometimes you may need to create a table in code. When you create a new table, you also need to create fields for it. The following code creates a new table in an external database, with several fields of different data types. Each field is created (and its default value set, for two of them), and is then appended to the new table. An error handler returns the user to the input box where the new table name is entered, in case a table of that name already exists in the database. Finally, all the TableDefs in the database are listed to the Immediate window, with the new table as the last entry in the list:

Private Sub NewTable()

On Error Resume Next

Dim dbsNorthwind As DAO.Database Dim tdfNew As DAO.TableDef Dim fld As DAO.Field

Dim strDBName As String

Dim strDBNameAndPath As String

Dim strPrompt As String

Dim strTitle As String

Dim strTable As String

Dim strCurrentPath As String

Dim fso As New Scripting.FileSystemObject

Dim fil As Scripting.File strCurrentPath = Application.CurrentProject.Path & "\" strDBName = "Northwind 2007.accdbM strDBNameAndPath = strCurrentPath & strDBName

Attempt to find database, and put up a message if it is not found.

Set fil = fso.GetFile(strDBNameAndPath) If fil Is Nothing Then strPrompt = "Can't find " & strDBName & " in " _ & strCurrentPath

& "; to create this database, double-" _ & "click the Northwind.accdt template in the " _ & "C:\Program Files\Microsoft Office\Templates\1033\Access folder"

MsgBox strPrompt, vbCritical + vbOKOnly GoTo ErrorHandlerExit End If

On Error GoTo ErrorHandler

Set dbsNorthwind = OpenDatabase(Name:=strDBNameAndPath)

NameNewTable:

strPrompt = "Please enter new table name" strTitle = "Table name"

strTable = InputBox(prompt:=strPrompt, Title:=strTitle, Default:="tblNew")

With dbsNorthwind

Create new table.

dbsNorthwind.CreateTableDef(Name:=strTable)

Create fields and append them to new table.

With tdfNew

Set fld = .CreateField(Name:="EmployeeID", _ Type:=dbLong)

.Fields.Append fld Set fld = .CreateField(Name

Type:=dbText, Size:=14) .Fields.Append fld Set fld = .CreateField(Name

Type:=dbText, Size:=20) .Fields.Append fld Set fld = .CreateField(Name

Type:=dbCurrency) fld.DefaultValue = 500 .Fields.Append fld Set fld = .CreateField(Name

Type:=dbBoolean) fld.DefaultValue = False .Fields.Append fld End With

Add the new table to the TableDefs collection.

.TableDefs.Append Object:=tdfNew End With

List the TableDefs in the database after appending the new table.

Debug.Print "TableDefs in " & dbsNorthwind.Name For Each tdf In dbsNorthwind.TableDefs

Debug.Print vbTab & tdf.Name Next tdf dbsNorthwind.Close

ErrorHandlerExit: Exit Sub

ErrorHandler:

If Err.Number = 3 010 Then strPrompt = "Table name already used;

& "please enter another name" strTitle = "Duplicate table name" MsgBox prompt:=strPrompt, _

Buttons:=vbExclamation + vbOKOnly, GoTo NameNewTable Else

MsgBox "Error No: " & Err.Number & ";

& Err.Description Resume ErrorHandlerExit End If

End Sub

:="Department", _ :="Shift", _ :="AnnualBonus", _

:="ShiftSupervisor",

Title:=strTitle

Description: " _

Here is the list of tables as printed to the Immediate window by the NewTable procedure:

MSysAccessStorage MSysACEs

MSysComplexColumns

MSysNavPaneGroupCategories

MSysNavPaneGroups

MSysNavPaneGroupToObjects

MSysNavPaneObjectlDs

MSysObjects

MSysQueries

MSysRelationships

Order Details

Orders

Products

Shippers

Suppliers tblNew

If you open tblNew in datasheet view, you will see a zero in the ShiftSupervisor Boolean field. If you prefer to see True/False or Yes/No values, you will have to select the format of your choice manually; the CreateField method does not have an argument for setting the display format for a field.

Other Ways of Creating Tables Programmatically

In addition to the CreateTableDef method, there are three other ways to create Access tables in VBA code:

■ The CopyObject method of the DoCmd object in the Access object model creates a new table based on an existing table.

■ Executing a make-table query using the OpenQuery or RunSQL method of the DoCmd object, or the Execute method of QueryDef object, creates a new table as the output of the query.

■ A Jet SQL CREATE TABLE statement, as in the following code, can be used to create a table:

strSQL = "CREATE TABLE " & "tblForms" & _

"(FormName TEXT (100), Use YESNO);" DoCmd.RunSQL strSQL

Compared with these techniques, the CreateTableDef method gives you the maximum control over the new table's fields and their properties. However you create a table, it will be appended to the TableDefs collection, and it will show up in the Tables group of the database window.

The ADO Object Model

The ADO object model is much simpler than the DAO object model; it is used to connect to a wide variety of data sources, so it is not customized to Access data, as the DAO object model is. However, with a few exceptions (working with form recordsets and creating tables programmati-cally), you can manipulate Access data with ADO much the same as with DAO.

0 0

Post a comment