Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    SQL Server 2005 Table only shows Rows as Deleted (Access 2000 and SQL SERVER 2005)

    I am getting an odd issue in an Access 2000 database linking to SQL Server 2005 tables with ODBC
    All Tables link OK
    The ODBC Connection string is shown below

    Description=Example Connect;DRIVER=SQL Server;SERVER=svr-sql;UID=username;PWD=usrpwd;APP=Microsoft Data Access Components;DATABASE=Training;LANGUAGE=us_english;

    All tables work with this string ok

    Two tables with a very large number of rows however cause issues (they have approc 350,000 rows)

    If the table has NO Primary key field then data retrieved is visible but obviously cannot be edited
    If the table is given a Primary key on an Identity Field then the data exists and appears in SQL server just fine
    BUT when viewed in Access all rows show as deleted in both table and query view.

    Am I missing something here that can prevent this.
    I need the data to be editable so I really need the Primary Key to exist.

    Many Thanks

    Andrew
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Server 2005 Table only shows Rows as Deleted (Access 2000 and SQL

    It could be a time-out problem. Open Regedit and navigate to the following registry key:

    HKEY_LOCAL_MACHINESOFTWAREMicrosoftJET4.0EnginesOD BC

    Note the current value of the ConnectionTimeout entry, then set its value to 0. A zero value means that the connection will never time out.

    If it doesn't help, you can set the value back to the original value.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SQL Server 2005 Table only shows Rows as Delet

    I resolved this problem.

    It appears that the Access link cannot cope if the Data Type of the Primary key is bigint

    I changed it back to int and everything is rosy in the garden again.

    Changing the timeout made no difference
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •