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

New Tables and Fields via VBA - part 4 (037)

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

In part 3 we learned the basics of using an Excel spreadsheet to define a new table and its fields. This article shows how to:

  • create autonumber fields
  • define text field size
  • read table name from spreadsheet

1.  Create Autonumber Fields

An autonumber field is a long integer field with a special property set. Thus, you first create the long integer field and then set the autonumber property of the field on.

     Case "auto"
          Set fldNew = tdfNew.CreateField(strField, dbLong)
          '--- 2 steps to autonumber field; define as type Long, then make autonumber
          fldNew.Attributes = dbAutoIncrField

2.  Define Text Field Size

To do this we will have to add another column to our spreadsheet. Make column C available for this purpose. This column will have a number in it for text fields.

Dim intFieldSize as Integer

     Case "text"
          intFieldSize = objSht.Cells(j, 3).Value
          '--- if invalid field size, set to 1 and notify user
          If intFieldSize < 0 Or intFieldSize > 255 Then
               MsgBox "invalid field size " & strField & " (" & intFieldSize & ")"
               intFieldSize = 1
          End If
          Set fldNew = tdfNew.CreateField(strField, dbText, intFieldSize)

3.  Read Table Name from Spreaedsheet

This is what that blank row 1 is for. Put the name of the table in cell "A1" and modify the code to read it.

Dim strTable as string

'--- read table name from cell A1 (row 1 column 1)
strTable = objSht.Cells(1, 1).Value
'--- if already exists, delete
On Error Resume Next
DoCmd.DeleteObject acTable, strTable
On Error GoTo 0
'--- create the table
Set tdfNew = db.CreateTableDef(strTable)

Further Suggestions

Assemble all the code into the proper order within the code from the previous tip, and test it. You'll need the same spreadsheet as before, with the added column to hold the text field sizes. You will also need the table name in cell A1.

Next Tip Issue

What's next? Adding some other properties to the fields, like format, zero-length, caption, and default value.

 

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

tip # 037  ||  previous tip  ||  next tip