New Tables and Fields via VBA

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

New Tables and Fields via VBA - Part 4

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

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

I came up with three (3) possibilities:

(article continues after sponsor spot)

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. part 5

Happy Coding

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.