#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    18
    Rep Power
    0

    Problem with Access Linked Table


    Hi,

    I have an Access 2000 table linking to a SQL Server table. When I open the table in Access, the column names are correct, but all the values = "#deleted". The data is find when I open it with my SQL client. I've tried refreshing the table with the Linked Table Manager with no success. I've also tried deleting the linked table and re-linking it.

    When I 'import' the table from SQL into Access the data shows up fine.

    Thanks for any help!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    Is this MS-SQL or MySQL Server? I have run into this before using MySQL ODBC driver in Access. The best solution I would recommend is update your MDAC. It could also be a config issue with your driver. Are you using any "big int" columns ie 64 bit integers? http://www.microsoft.com/downloads/d...DisplayLang=en
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    18
    Rep Power
    0
    Sorry, you'll have to bare with me. I'm a scratch beginner with SQL Server dbs.

    Yes, it is MS SQL Server. The primary key field of the table is the data type is "bigint". I noticed this problem with the linked table after I changed the data type from nvarchar to bigint.

    Should this update to the MDAC be given to the server or the client PC, or both?

    Thanks for your help!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    It should be given to the client. I am not exactly sure what the issue is except I know Access has issues with 64 bit integers. Unless it is needed I would just recommend using an int not a bigint. The max value of a signed integer is over 2 billion.... that is a lot of rows! If you need the bigint I would just stick to a char field if you are using Access. If you search the Microsoft Knowledge Base you might be able to find more articles regarding this problem. http://dbforums.com/arch/66/2002/11/528816
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    18
    Rep Power
    0
    Cool! Changing from 'bigint' to 'int' fixed the problem. Thanks for your help!

IMN logo majestic logo threadwatch logo seochat tools logo