Linking to SQL Server 2005 from Access 2003

Posted on 2007-07-26
Last Modified: 2010-03-19
For the first time, I am trying to link tables from a SQL Server 2005 database into Microsoft Access 2003.  Everything looks good when I create the ODBC connection, then I link the tables, which also appears to work.  When I open the table however, Access is aware of the table structure because the column headings appear, but the data is missing - instead I have only the Access !deleted! in every row and column.

Anyone know why?
Question by:AIBMass
    LVL 77

    Expert Comment

    Your SQL tables must have a PK and that PK must be  a datatype that maps to an Access datatype.

    Author Comment

    You have put me on the right track.  The SQL Server table need not have a PK, but having a PK of type BIGINT doesn't work.  I changed the datatype to INT and Access was able to view it.  Then I removed the PK designation and Access could still view it.

    So I conclude that PK of BIGINT and possibly other datatypes is a no-no for Access.


    Author Comment

    Peter, if you will incorporate my comments into another reponse, I would be happy to mark your reply as the solution.  If I mark your last answer as the solution, then the clarification I added would be lost - as I understand EE...
    LVL 77

    Accepted Solution

    All comments are retained.
    I thought a PK was required - maybe that's only for updates, then.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    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

    22 Experts available now in Live!

    Get 1:1 Help Now