New Tables and Fields via VBA

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

New Tables and Fields via VBA - Part 5

In part 4 we learned how to create autonumber fields, define text field sizes, and read the table name from the spreadsheet. This article shows how to modify some other properties of the fields.

One thing that had me stuck for a while when I first studied using VBA to add fields, is that some properties cannot be modified (or created) until the field has been saved. Let's look at the easy ones first.

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. Before Save Properties

In column "G" of the spreadsheet put any desired default value for the field.
In column "I" put the value "yes" if you want the field to be required.
In column "J" put the value "yes" if you want the bfield to be allowed zero length. This only works for text or memo fields.

'--- required property (column "I")
If objSht.Cells(j, 9).Value = "yes" Then fldNew.Required = True

'--- allow zero length property only for text or memo (column "J")
If objSht.Cells(j, 10).Value = "yes" Then
     If strFieldType = "text" Or strFieldType = "memo" Then
          fldNew.AllowZeroLength = True
     End If
End If

'--- default value property (column "G")
If objSht.Cells(j, 7).Value <> "" Then fldNew.DefaultValue = objSht.Cells(j, 7).Value

2. After Save Properties

In my testing, it seemed that some properties could not be set until AFTER the field and table had been saved. Two examples are the format property and the caption property. I do not usually set the caption property, however, I almost always set the format property for dates.

The method I use is to loop through the spreasheet a second time, looking for these properties.

In column "D" put any desired format property. I use "mmm d, yyyy" for dates.
In column "F" put any desired caption property.

Dim prp As Property

'--- the following properties need to be added after the table exists
j = 1
strTable = objSht.Cells(j, 1).Value
'--- set a reference to the table
Set tdfNew = db.TableDefs(strTable)
'--- fields for that table
Do While True
     j = j + 1
     strField = objSht.Cells(j, 1).Value
     If strField = "" Or IsNull(strField) Then Exit Do
     Set fldNew = tdfNew.Fields(strField)

     '--- the format property is in the 4th column ("D")
     If objSht.Cells(j, 4).Value <> "" Then
          Set prp = fldNew.CreateProperty("Format", dbText, objSht.Cells(j, 4).Value)
          fldNew.Properties.Append prp
     End If

     '--- the caption property is in the 6th column ("F")
     If objSht.Cells(j, 6).Value <> "" Then
          Set prp = fldNew.CreateProperty("Caption", dbText, objSht.Cells(j, 6).Value)
          fldNew.Properties.Append prp
     End If

     '--- save the properties
     tdfNew.Fields.Refresh
Loop

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 columns to hold the properties.

Next Tip Issue

What's next? So far, our spreadsheet has allowed us to create one table at a time. Let's add some looping so that we can create more than one table from the same spreadsheet.

We will also have a sample database available with all the code properly assembled and working.

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.