Alter Table in another database

[madelca100]

Using MS Access 2003 - I want to add a new field to a "linked" table and then populate the field with data.

Why am I doing this?
Another department drops a database onto my server each week. I want to add new fields to this database so developers can use them. I can't put any code in the database because it is replaced every week. And the other dept can't add the additional fields.

Normally you can't modify a linked table. I thought using the "in" keyword would allow me to create the new fields and populate them with data.


Notes:
1) the ALTER query works without the "in" keyword if the table is local. (i.e. not linked).

2) The UPDATE query, which uses the "in" keyword, works fine on the linked table.



The ALTER TABLE command tells me that I have a syntax error (3293).

the code is:

strAlterCaseSQL = "ALTER TABLE Cases IN 'p:\access\my.mdb' ADD Column [Case YYYY] varchar(50) null; "

DoCmd.RunSQL strAlterCaseSQL


The problem appears to be the "in" keyword.

I've tried it without the NULL parameter. Still doesn't work.

It works if I'm in the database and use
"ALTER TABLE Cases ADD Column [Case YYYY] varchar(50) null; "

I hope I provided enough information.

Appreciate your thoughts.

If I can't use the "IN" keyword is there another way to accomplish this?


thanks,
mike

 

[PWise]

try

Dim mydb As Database
Set mydb = OpenDatabase("p:\access\my.mdb")
mydb.Execute "ALTER TABLE Cases ADD Column [Case YYYY] varchar(50) null"

 

[madelca100]

thanks PWise for the quick response.

I tried it and got the following error:

3611 - Cannot execute data definition statements on linked data sources.

mike

 

[PWise]

try

Set mydb = OpenDatabase("pathtodatabasewhereorignaltableare\my.mdb")

 

[madelca100]

PWise,

I do point to the "path to database where original tables are".

So.....
MyDev points (links) to a table in Mydb.

The "IN" clause (or OpenDatabase) points to Mydb.

Am I missing something?

mike

 

[PWise]

well the tables in mydb are they linked tables what icon is next to them

 

[madelca100]

Mydb is where the table resides.

Icon is table.
(Some of the tables have arrows, but not this one.)

Type is Table. (Others are Table: Linked Access)


In MyDev the table is "linked". MyDev is where I'm coding the ALTER TABLE with the IN clause (or the OpenDatabase) statement.

If I manually add a field (in Mydb) I can update it from MyDev. I just can't get the ALTER TABLE to work using the same IN clause.

 

[PWise]

can you please post your whole code
by me it works with open database

 

[madelca100]

PWise,

I tested it again this morning and it WORKS!

Thanks so much for sticking with me on this one.

I don't know what happened except a reboot of my computer. (I should have tried that myself.)

mike