Cannot execute data definition statements on linked data sources

[Guest]

I receive the error message in the subject line when I try to run the
following SQL

ALTER TABLE tblProgDeliv ADD COLUMN [TechID] NUMBER [LONG INTEGER]

TechID would be a foreign key to the Tech table, where it is the primary
key, and is used as a FK in existing tables as well.

Any suggestions on how to accomplish the above via some form of SQL or code
will be gratefully accepted.

 

[Allen Browne]

tblProgDeliv is an attached table in some other file.

You will need to OpenDatabase on that file to modify the table:

Dim db As DAO.Database
Dim strSql As String
strSql = "ALTER TABLE ...
Set db = OpenDatabase("C:\MyFolder\MyFile.mdb")
db.Execute strSql, dbFailOnError
db.Close
set db = nothing

 

[Guest]

Thanks for the response. I would have mentioned that I was trying to modify a
table in the back-end database, but it only dawned on me what the error
message meant about 15 seconds after I posted my question. Couldn't see the
forest for the trees. I figured I would need to use an OpenDatabase command.
Your code will save me a lot of time. Thanks again.

tblProgDeliv is an attached table in some other file.

You will need to OpenDatabase on that file to modify the table:

Dim db As DAO.Database
Dim strSql As String
strSql = "ALTER TABLE ...
Set db = OpenDatabase("C:\MyFolder\MyFile.mdb")
db.Execute strSql, dbFailOnError
db.Close
set db = nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I receive the error message in the subject line when I try to run the
following SQL

ALTER TABLE tblProgDeliv ADD COLUMN [TechID] NUMBER [LONG INTEGER]

TechID would be a foreign key to the Tech table, where it is the primary
key, and is used as a FK in existing tables as well.

Any suggestions on how to accomplish the above via some form of SQL or
code
will be gratefully accepted.