the Manx Man's MS Access Pages
to subscribe - click here

 

New Tables and Fields via VBA - part 3 (036)

NOTE:This article has been converted to our new web site. See it here.

These several articles will look at a method of adding a new table (and its fields) to an Access database, based on instructions stored in an Excel spreadsheet. After the initial cost of designing this, I found it much simpler than using the regular new table wizard in Access.

Let's first look at a number of "chunks" of code that go into this routine. By examining them by themselves, without all the error code and code for exceptions, hopefully you will find the final product easier to understand.

This article assumes that you have an Excel workbook (spreadsheet) already created. It has 2 columns in it: (A) field names, and (B) field types. The first field is on row 2. Valid field types for now are: text, integer, long, date, yesno, and memo. We will add autonumber in the next article.

Here are the steps:

  1. open the Excel Workbook
  2. create the table
  3. read in a row from the spreadsheet
  4. create the field as described by the data from step 3
  5. repeat step 4 as required
  6. save the table and close all objects

1.  Open the Excel Workbook

In order for this code to work, you must use the Tools / References menu to add Excel to the list of references.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

'--- set a reference to Excel
Set objXL = New Excel.Application
'--- open the workbook
Set objWkb = objXL.Workbooks.Open("c:\newtables.xls")
'--- set a reference to the correct tab (I always name my worksheet tabs, rather than leave the default)
Set objSht = objWkb.Worksheets("Tables")

2.  Create the Table

For now we will hard code the name of the table. In a future article we will change the code to enable it to read the table's name from the spreadsheet. As usual, this code assumes that you have set a reference to DAO. See this article for an explanation.

Dim db As Database
Dim tdfNew As TableDef

Set db = CurrentDb

'--- if table already exists, delete it (ignore error that occurs if table does not exist)
On Error Resume Next
DoCmd.DeleteObject acTable, "MyNewTable"
'--- set error checking back on
On Error GoTo 0
'--- create the table
Set tdfNew = db.CreateTableDef("MyNewTable")

3.  Read in a Row from the Spreadsheet

This code reads the data from one row of the spreadsheet. Column A stores the field name, and column B stores the field type. The two lines that "read" the data will go inside a loop in the finished product.

Dim iRow as integer
Dim strField As String
Dim strFieldType As String

'--- we start reading at row 2 in the spreadsheet (reason will become apparent later)
iRow = 2
'--- read the name of the field (which is in the 1st column)
strField = objSht.Cells(iRow, 1).Value
'--- the field type is in the 2nd column
strFieldType = objSht.Cells(iRow, 2).Value

4.  Create the Field as Described by the Data from Step 3

The following code creates the basic text, integer, long, date, boolean, and memo field types. Later we will look at creating autonumber fields. Except for the text field, all these fields have pre-defined field sizes. For now we will use the default for text, which is 50 characters.

Select Case strFieldType
     Case "text"
          Set fldNew = tdfNew.CreateField(strField, dbText, 50)
     Case "integer"
          Set fldNew = tdfNew.CreateField(strField, dbInteger)
     Case "long"
          Set fldNew = tdfNew.CreateField(strField, dbLong)
     Case "date"
          Set fldNew = tdfNew.CreateField(strField, dbDate)
     Case "yesno"
          Set fldNew = tdfNew.CreateField(strField, dbBoolean)
     Case "memo"
          Set fldNew = tdfNew.CreateField(strField, dbMemo)
End Select

'--- save the field
tdfNew.Fields.Append fldNew

5.  Repeat Step 4 as Required

We need some method of telling the program that it has hit the end of the list of fields. Here is one method.

Do While True
     '--- read the name of the field (which is in the 1st column)
     strField = objSht.Cells(iRow, 1).Value
     '--- if the name of the field is "end of list" then exit the loop
     if strField = "end of list" Then
          Exit Do
     end if

     ...  all the other code used to create the fields

     '--- move down one row
     iRow = iRow + 1
Loop

6.  Save the Table and Close All Objects

I hope your mom taught you to always, ALWAYS, clean up after yourself. It is no different here.

'--- save the new table
db.TableDefs.Append tdfNew

'--- close all the objects (forcing memory to be cleaned up)
objWkb.Close
objXL.Quit
set objSht = Nothing
set objWkd = Nothing
set objXL = Nothing
set tdfNew = Nothing
Set db = Nothing

Further Suggestions

Assemble all the code into the proper order and test it. You'll need to create a spreasheet using Excel. This spreadsheet will have field names in column A and field types in column B. Do not use row 1 of the spreadsheet for anything.

Next Issue

What's next? Creating an autonumber field, setting size of text fields, reading table name from spreadsheet, and more.

 

index of tips click here for a laugh profit from the web

tip # 036  ||  previous tip  ||  next tip