Need Code to create new field in table

  • Thread starter Thread starter Michael San Filippo
  • Start date Start date
M

Michael San Filippo

Was wondering if there was any way to add a field / desc / type - all that
good stuff to a table without having to open up the backend and edit the
table directly.

See the thing is, sometimes I work on one thing using a test backend and add
fields or change types or something like that, then when I relink to the
live backend I forget to add those fields or change the type. So I was
wondering if I could put the name of the field, destination table, type, and
any additional properties into a temp table and then run a
macro/query/something to automatically add/update/delete those fields into
tables.

Any ideas?
 
What you propose will work fine.

Start with this:

dim db as database
set db = dbengine.opendatabase ("<path to back-end database>")

Now you can create a table using SQL:

db.execute "CREATE TABLE BLAH ...", dbfailonerror

or using VBA methods like createfield, createtabledef & so on. Check the
examples of those methods in online help. Just make sure you execute them
all against the reference to the back-end database (db), >not< the executing
database (currentdb).

As for things like column default values, descriptions, & so on, be aware
that most of those are stored as custom properties of the field. Use
createproperty to create those props.

HTH,
TC
 
Back
Top