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

 

What Your Computer Consultant Doesn't Want You to Know
Click here to find out

 

New Tables and Fields via VBA - part 2 (022)

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

In part one we looked at creating a new back end mdb, copying all the data to it, deleting the old one, and renaming the new one. Now, let's look at the use of VBA to create new tables and fields.

All the code segments assume that you have DAO references active. If you are not sure what this means, and you are using Access 2000 or higher, click here.

Code to create a new table with several fields

Dim db As Database
Dim tdfNew As TableDef
Dim fldNew As Field

'--- set a reference to the database
Set db = CurrentDb

'--- create the table
Set tdfNew = db.CreateTableDef("MyNewTable")

'--- add text field (length 20)
Set fldNew = tdfNew.CreateField("MyNewTextField", dbText, 20)
'--- save the new field
tdfNew.Fields.Append fldNew

'--- add integer field
Set fldNew = tdfNew.CreateField("MyNewIntField", dbInteger)
'--- save the new field
tdfNew.Fields.Append fldNew

'--- save the new table design
db.TableDefs.Append tdfNew

'--- reclaim the memory
set fldNew = nothing
set tblNew = nothing
set db = nothing

Further Comments

As usual, I have shown "stripped down" code, with no error checking. I leave it to the reader to add this.

In part 3 of this series we will see about adding a new field to an existing table, and also about removing a field from an existing table.

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

tip # 022  ||  previous tip  ||  next tip