Using TableDef

[Bill]

I have an existing table in which I need to check for the
existence of a field. And, if the field DOES NOT exist,
I need to add it. (Part of a process of updating existing
DB's in the field with version updates.)

This being my first experience with this task, I don't want
to shoot a "hole in my foot", as you can imagine the field
problems I would create for myself.

1) Does one check with tabledef append and handle the
error if it already exists as the means of testing for
existence?

2) Is the "append method" in fact the proper way to add
a field?

Maybe a simple code example would help. The Northwind
example doesn't quite seem to be the answer.

Thanks,
Bill

 

[Douglas J. Steele]

I have an existing table in which I need to check for the
existence of a field. And, if the field DOES NOT exist,
I need to add it. (Part of a process of updating existing
DB's in the field with version updates.)

1) Does one check with tabledef append and handle the
error if it already exists as the means of testing for
existence?

That is a possible approach, but I'd recommend checking, rather than blindly
trying to add.

2) Is the "append method" in fact the proper way to add
a field?

Dim booFound As Boolean
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("MyTable")
booNotFound = True
For Each fldCurr In tdfCurr.Fields
If fldCurr.Name = "NewField" Then
booNotFound = False
Exit For
End If
Next fldCurr

If booNotFound = True Then
Set fldCurr = tdfCurr.CreateField("NewField", dbLong)
tdfCurr.Fields.Append fldCurr
End If

Of course, another alternative is to use DDL:

Dim strDDL As String

strDDL = "ALTER TABLE MyTable " & _
"ADD NewField LONG"
CurrentDb.Execute strDDL, dbFailOnExecute

 

[Bill]

Hi Doug, thanks.
I have to leave for the day but I will assimilate your
suggestions in the morning (Friday) and report back.
Thanks again,
Bill

 

[Bill]

Doug,
With the code in a general module on the front-end mde of
a split database, does one simply supply the name of the
backend in the "Set"? I.e.,
Set dbCurr = CurrentDb("NameOfBackendDB")

Also, since one cannot generally alter table defs from the
front-end database-view of the back-end, does that preclude
running the code we're discussing here from within the front-end?
(This application is not typically networked, so the front-end/
back-end are both on the same computer.)

By the way, the application in question here is compiled on
A2K, so I don't believe that the DDL is an option. At least
my 2003 Help indicates it's not supported until 2002.

Bill

 

[Douglas J. Steele]

As long as the dbCurr object points to the correct database, you can run
that code from the front-end.

Set dbCurr = OpenDatabase("G:\Folder\NameOfBackendDB.MDB")

The DDL I posted works in Access 97.

 

[Bill]

Okay, I'll create some code around what you've shown
me and give it all a "go". I'll post back when I'm done.
Bill

 

[Bill]

Hi Doug,
Your code example worked perfectly. I used your structure
in addition to the implementation of database version control,
as relates to changes imposed by the advancement of code
versions........long overdue.
Thanks again,
Bill