'Serving the Microsoft® Access
New Tables and Fields via VBA - Part 3
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.
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.
(article continues after sponsor spot)
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
In order for this code to work, you must use the Tools / References menu to add Excel to the list of references.
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.
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.
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.
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.
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.
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.
Creating an autonumber field, setting size of text fields, reading table name from spreadsheet, and more. part 4