'Serving the Microsoft® Access
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.
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.
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.