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 2

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.

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)

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.

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.