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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Royce" <(E-Mail Removed)> wrote in message
news:2A852F31-93A3-4A53-8E23-(E-Mail Removed)...
>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?
>

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


--
Duane Hookom
MS Access MVP


"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Royce" <(E-Mail Removed)> wrote in message
> news:2A852F31-93A3-4A53-8E23-(E-Mail Removed)...
>>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?
>>
>
>

Thanks, Duane.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:(E-Mail Removed)...
>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
>
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>>
>> "Royce" <(E-Mail Removed)> wrote in message
>> news:2A852F31-93A3-4A53-8E23-(E-Mail Removed)...
>>>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?

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

"Duane Hookom" wrote:

> 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
>
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Royce" <(E-Mail Removed)> wrote in message
> > news:2A852F31-93A3-4A53-8E23-(E-Mail Removed)...
> >>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?
> >>
> >
> >
>
>
>

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)"

"Royce" wrote:

> 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?
>

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

"Allen Browne" wrote:

> Thanks, Duane.
>
> That's a good approach.
> Doesn't look like the DDL works.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
> news:(E-Mail Removed)...
> >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
> >
> >
> > --
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "Allen Browne" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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.
> >>
> >>
> >> "Royce" <(E-Mail Removed)> wrote in message
> >> news:2A852F31-93A3-4A53-8E23-(E-Mail Removed)...
> >>>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?
>
>
>