Joe said:
How does one add a field to a linked table programmatically? That is, I
have a back end and a front end; I'd like to add a field to the back end via
code in the front end.
I've tried using TableDefs("tblName").Fields.Append and running a SQL
command ("ALTER TABLE ... ADD COLUMN..." but neither approach has worked.
Is there a way, or do I have to open the backend to do it?
First we have to warn you that this is only a valid(?)
operation as part of a new version install process. If
you're just trying, for example, to add another month field
to a spreadsheet like table, you have much bigger problems
that should be addressed by properly normalizing your tables
so field don't have to be added.
With the obligitory lecture out of the way ;-)
Regardless which of the two approaches you described above,
you need to open the backend mdb file to do this. The DAO
TableDef in the front end is just a link to the backend so
there are no fields. The ALTER TABLE approach rquires the
backend db object so the query can operate on the proper
object. You can easily obtain the path to the backend from
any linked table's Connect property:
Dim dbFE As DAO.Database
Dim dbBE As DAO.Database
Dim tdf As DAO.TableDef
Dim strPathBE As String
Set dbFE = CurrentDb()
Set tdf = dbFE.TableDefs!somelinkedtable
strPathBE = Mid(tdf.Connect, 11) 'Jet backend
Set dbBE = OpenDatabase(strPathBE)
Now you can use dbBE for either of your approaches.
You can also use the following syntax in an ALTER TABLE
query to specify the backend mdb file without opening the
backend:
dbFE.Execute "ALTER TABLE [" & _
strPathBE & "].thetable ADD COLUMN . . . ", dbFailOnError
