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.


Bob HoffmanDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
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 HoffmanDeveloperAuthor Commented:
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.
Leigh PurvisDatabase DeveloperCommented:
>> There is a PK.
In the local Access linked table?  (i.e. if you the linked table in design view - there is a PK identified?)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bob HoffmanDeveloperAuthor Commented:
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 HoffmanDeveloperAuthor Commented:
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 PurvisDatabase DeveloperCommented:
Unfounded fears. :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.