[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Linking to SQL Server 2005 from Access 2003

Posted on 2007-07-26
Medium Priority
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
  • 2
  • 2
LVL 77

Expert Comment

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

Author Comment

ID: 19574117
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

ID: 19574157
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

peter57r earned 750 total points
ID: 19575276
All comments are retained.
I thought a PK was required - maybe that's only for updates, then.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

873 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