Access SQL link table update problem

Posted on 2011-10-24
Last Modified: 2012-05-12
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.


Question by:HBHoffman
    LVL 44

    Expert Comment

    by: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.
    LVL 8

    Author Comment

    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.
    LVL 44

    Accepted Solution

    >> 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?)
    LVL 8

    Author Comment

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

    Author Closing Comment

    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.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Unfounded fears. :-)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now