add field to linked table

[Guest]

I have a Access 2000 data database containing only tables linked to a Program
database. I need to alter the structure of one of the data tables. (Add a
field.) I would like to do it from code in the Programs database. ALTER
table does not work on linked tables.

Is there any way to do this?

 

[Allen Browne]

You could try adding an IN clause, e.g.:
ALTER TABLE Table1 IN 'c:\MyDatabase.mdb' ...

If that doesn't work, you could OpenDatabase(), and run the SQL statement
against that database variable.

 

[Duane Hookom]

I usually call some DAO code like the following (needs error handling):

Function AddField(pstrTable As String, pstrField As String, _
intFieldType As DataTypeEnum) As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim strRemoteDB As String

Set db = CurrentDb
Set td = db.TableDefs(pstrTable)
If Len(td.Connect) > 0 Then
strRemoteDB = Mid(td.Connect, 11)
Set td = Nothing
Set db = OpenDatabase(strRemoteDB)
Set td = db.TableDefs(pstrTable)
End If
With td
.Fields.Append .CreateField(pstrField, intFieldType)
End With
Set td = Nothing
Set db = Nothing
End Function

 

[Allen Browne]

Thanks, Duane.

That's a good approach.
Doesn't look like the DDL works.

 

[Guest]

Thanks for the sample code. I had tried a similiar function, that didn't
work. Unfortunately, I didn't save the original code, so I don't know what I
was doing wrong. But I think it was something I was doing with the
OpenDatabase. It kept wanting to open the ODBC connection box.

Thanks again,
Royce

 

[Guest]

I have used this before on linked tables (as long as they are access) and it
works

Set dbs = OpenDatabase(location of table)

dbs.Execute "ALTER TABLE invoices ADD COLUMN ordertype text(50)"

 

[Guest]

One of the things I have often found frustrating about Access is the subtle
differences about when something will work. Over the years I've learned many
of them, but I still run into things from time to time that can be very
frustrating to figure out. I know there are some documented problems with
the database object. In this case, I actually found and tried some sample
code from the Northwind databae, which didn't work in my App!

Royce