add field to linked table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
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
 
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
 
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)"
 
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
 
Back
Top