none
Connection to Access 2010

    Question

  • Hi,

    I connect to SQL server 2008 with Access 2010 through ODBC connection.  I can see the server, connect to the database I want, and even import the table.  After importing, all records in the gtable are imported correctly. 

    But when I link the table, and then open it, all fields in all records show as #DELETED.  I can even add new records through the linked table, they show up when I browse the table through the SQ server Management studio.  But as soon as they're added, Access shows them as #Deleted.  When I use the Refresh all data button, the first record (dependent on the sort order)  is shown correctly, but all others remain #DELETED.  I had this problem already with Access 2007 on the SQL 2008 server, but hoped it would be solved after upgrading to Access 2010, so now I suppose it's a problem on the SQL server side.

    Hope someone has an idea....

    greetings

    NG

     

    Monday, September 06, 2010 12:19 PM

Answers

  • When you created a linked table did you properly indicate the unique records while linking.

     

    I tested the data import and linked data and both of them work. I have Access 2010 Beta.


    mysorian
    • Marked as answer by Ai-hua Qiu Wednesday, September 15, 2010 1:27 AM
    Sunday, September 12, 2010 12:49 AM

All replies

  • Hi,

    Did you delete all records of your table through SQL Server Management Studio? According to your description, I tested on my machine (SQL Server 2008 Developer, Access 2010). I found that there are two buttons, “Refresh All” and “Refresh”. When I delete or insert some records through SQL Server Management Studio, if I use “Refresh” button, I will get the results as you described.

    "Refresh” button is used to record only updates the data that already exists in your datasheet or form. It does not reorder records, display new records, or remove deleted records. In this case, we should use “Refresh All” button.

    For more information, please see:

    http://office.microsoft.com/en-us/access-help/refresh-or-requery-data-HA010256400.aspx

     

    Thanks,

    Ai-Hua Qiu


    Constant dropping wears away a stone.
    • Edited by Ai-hua Qiu Thursday, September 09, 2010 12:37 PM
    Wednesday, September 08, 2010 9:28 AM
  • Hi,

     

    yes I did use refresh all. 

    Now I just found out with a test table that changing the PK field from bigint to int on the SQL server solves the problem.  But I cannot alter the existing keys in the existing tables, so I am wondering if there is another solution to this problem...

     

    thx for any answer

    NG

     

     

    Thursday, September 09, 2010 8:48 AM
  • When you created a linked table did you properly indicate the unique records while linking.

     

    I tested the data import and linked data and both of them work. I have Access 2010 Beta.


    mysorian
    • Marked as answer by Ai-hua Qiu Wednesday, September 15, 2010 1:27 AM
    Sunday, September 12, 2010 12:49 AM
  • Hi,

     

    I did indicate the primary key when connecting.  But I think I found part of the problem: the PK in SQL server are Bigint, when I try with a test table with an int field as PK the problem doesn't occur.  Sadly, my problem remains because I can't go changing the PK field type of the existing tables...

    Any sugestions there?

     

    thx

    NG

    • Proposed as answer by Licenciao Tuesday, January 18, 2011 6:00 PM
    Friday, September 17, 2010 4:41 PM
  • Could you provide a sample of the table structure in MS ACCESS?

    mysorian
    Wednesday, October 13, 2010 3:30 AM
  • Your apreciation is correct, there is a problem when linking tables from SQL to Access when the type of PK is bigint, in fact when you see at the table design in access the type is TEXT, so I think the type is not a valid type for access. Changing the type in SQL is enouggh solution for me, but I didn´t try for example just setting  the Identity Specification to No en SQL to solve the same issue.
    Buena Suerte! blog: http://hespinosamedina.spaces.live.com/blog/ Si las respuestas te son de utilidad marcalas como buenas!
    Tuesday, January 18, 2011 6:00 PM