asked on

Access SQL link table update problem

I have 2 Access databases; each has a linked table set up to the exact same SQL table. Both are trusted ODBC connections using the same DNS. One allows the table to be updated the other does not.

The only differences in the connections string are as follows:
>> One the works has: “APP=Microsoft Data Access Components”
>> One the doesn’t has: “APP=2007 Microsoft Office system”

I believe there is a table size limitation with “APP=2007 Microsoft Office system” because when the table was smaller the update worked fine.

Question: How can I update the connection sting on the one that fails to use “APP=Microsoft Data Access Components”? I tried the refresh link table but that had no effect on APP
I can’t delete and re-add the linked table because the user has a ton of queries written over it.

Thanks

Leigh Purvis

You are perfectly safe to delete and recreate the linked table. The queries in question are merely definitions. Unless you attempt to go into design view while the tables are missing and save the reformatted result - nothing will happen to them.

That said, the part of the connection string you're interested in should be playing no part.
You don't say what error the user is getting. If this is an exact copy of the same file (i.e. why are the connection strings different then)? If there is a PK identified in the local linked table.
In relinking - it may be that you are prompted for the PK if there isn't one (which would be terrible design btw ;-)

You could create your linked table via code such as here.

Bob Hoffman

ASKER

There isn't any error message, when the table is open there is message in the status bar saying updates are not allowed.

“If this is an exact copy of the same file (i.e. why are the connection strings different then)?” << and using the same DSN… great question, I have no clue

There is a PK.

I’ll copy the .mdb and try to delete and re-add the table, if it works I’ll do it over the production .mdb.
Thanks,

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Hoffman

ASKER

sorry for the delay. You're absolutely correct there is NOT a PK. Is there a way I can add one without deleting and re-adding the linked table?

Bob Hoffman

ASKER

The missing PK was the problem. Disregard my question about adding a PK without deleting and re-adding the linked table, that works fine. The user will just need to get over their fears of all there queries disappearing. Thanks.

Leigh Purvis

Unfounded fears. :-)

Cheers.