Results 1 to 7 of 7
  1. #1
    forrestapi is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    39

    Error viewing SQL tables


    We have a new server we purchased for a new order monitoring software program. On this server we installed sql server 2008. We are attempting to write to some tables in the SQL database through access. When I view the tables in the sql server through access, all the records show up as deleted. Now i know there is no issues with the SQL database, as we have another program that can view the records in those tables with no issues. I guess my question here is, is there any special settings or drivers to be able to view records from a 2008 sql database through an odbc connection and in Access? We have another server with sql 2003 and can see, read, and write to those tables without issue. Please let me know if you have any questions. Thanks!

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    How have you linked to SQL Server? Which ODBC driver have you used?

    We have another server with sql 2003 and can see, read, and write to those tables without issue.
    Well, that is definitely interesting because there is no SQL Server 2003. There is SQL Server 2000, 2005, and 2008. But no 2003.

    Also, do the SQL server tables have primary keys within the tables? You need to have them in order for Access to be able to update them.

    Also, when you set up the ODBC DSN on your computers (I'm assuming you used that and not a file DSN or DSNLess connection) did you remember to check the CHANGE DEFAULT checkbox and select in the drop down the database name instead of what the default usually is, which is MASTER?

  3. #3
    forrestapi is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    39
    Im sorry about some of the details, I posted that just as I walked out the door and was in a rush, but to answer your questions.

    I have linked to the sql server through a system dsn (and yes I did select the correct db from the drop down box) using the SQL Server type. I apologize about the SQL Server version, to be honest I havent paid much attention, though I do believe it is 2005 now that I think back (I can confirm this in the morning). The SQL tables do have primary keys (which are autonumbers). Another point here is through the odbc connection it recognizes the autonumber key fields on the SQL Sever as "text' field type when looking at design view.

    You asked which driver I use, and I am wondering if this is the issue. I only use what ever drivers are loaded from factory for xp, vista, or windows 7. Are there other odbc drivers I should look into?

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    With Windows XP and SQL Server 2005 you would normally want to use the

    SQL Native Client

    driver instead of the SQL Server driver.

    Also, make sure that the SQL Tables have their primary key field set to INT and IDENTITY is set to YES. Then you might try deleting all of the tables from the frontend (only the linked SQL Server ones) and relinking. If it prompts you to select the field(s) which are primary keys, for any of the tables, then you need to select the correct one/ones or else it will not work for updating.

  5. #5
    forrestapi is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    39
    You are correct, with xp and SQL 2005 we are using SQL Native Client and having no issues.

    I checked on the SQL server for the suggestions you had. The primary key is set as a "bigint" by the people who supplied the program (the setup there own SQL database and I have been speaking with them about the viewing issues through Access, but with little luck as they do everything through SQL itself.). The Identity Specification is set to yes.

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by forrestapi View Post
    You are correct, with xp and SQL 2005 we are using SQL Native Client and having no issues.

    I checked on the SQL server for the suggestions you had. The primary key is set as a "bigint" by the people who supplied the program (the setup there own SQL database and I have been speaking with them about the viewing issues through Access, but with little luck as they do everything through SQL itself.). The Identity Specification is set to yes.
    Access doesn't like working with BigInt. It needs to be INT, not BigInt.

  7. #7
    forrestapi is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    39
    So bigint should be the only problem here? So normally Access has no issue displaying tables from SQL 2008?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Viewing Attachments
    By tcheck in forum Access
    Replies: 3
    Last Post: 08-02-2011, 03:24 PM
  2. droid x viewing ms access
    By hls in forum Import/Export Data
    Replies: 1
    Last Post: 01-04-2011, 03:20 PM
  3. Viewing Form via Switchboard
    By MollyB in forum Forms
    Replies: 1
    Last Post: 05-20-2010, 10:05 AM
  4. Viewing Multiple Tables in Access 2007?
    By MikeT in forum Access
    Replies: 1
    Last Post: 05-16-2010, 05:38 PM
  5. Access 2.0 Viewing Code
    By wollydog in forum Access
    Replies: 0
    Last Post: 12-03-2008, 07:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums