Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Access black listing an ODBC source?

    Howdy,

    Using Access 2003 in XP, linked 5 tables to an SQL source through ODBC. It worked fine at first.

    During a lengthy query on two of the tables, Access crashed. Upon reopening Access, the two tables in question now return #Deleted in every field of every record (all 898,000 records).

    The rest of the tables display their data correctly - only these two display the correct number of records, but every field #Deleted

    1) Tables still contain data and display properly on other workstations.
    2) The problem persists after a reboot.
    3) The problem persistes even in seperate Access MDB files on this workstation.
    4) The problem persists on these specific tables even when a new ODBC data source is setup.

    Here's the kicker: running Microsoft Query through Excel displays the data correctly - only Access on this specific machine has the problem with those specific tables.

    Does Access have a "black list" at some core level?

    Thanks,
    TC "If I wanted problems I'd use Vista" Ace

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    could there have been design-changes in the source tables since the original link? if any risk of YES, drop the linked tables and relink.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ayup, I've seen this before and relinking the tables was always the answer.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Tried deleting the link and relinking.
    Tried creating a new Machine Data Source pointing to the tables and linking to that.
    Tried creating a new Access DB and linkin from that.
    Tried the previous two together.

    The problem persists on this machine (unfortunately, it's my laptop which I'm partial to).

    I'm at a loss since the connection is obviously good (MS Query pulls it through Excel, the only hitch being there's more records than Excel can handle).

    Sorry Teddy - didn't mean to marr the "always" record ...

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try adding a TimeStamp (data type = TimeStamp) to the SQL Server table(s).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    BUG: Records display #Deleted when you use Access 2002 to link to a SQL Server table that contains ROWGUID
    View products that this article applies to.
    Article ID : 290332
    Last Review : August 10, 2004
    Revision : 4.1
    This article was previously published under Q290332
    Moderate: Requires basic macro, coding, and interoperability skills.

    This article applies only to a Microsoft Access database (.mdb).


    For a Microsoft Access 2000 version of this article, see 244872 (http://support.microsoft.com/kb/244872/EN-US/).
    Dale Houston, TX

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    One more thing

    RESOLUTION
    This problem was corrected in Microsoft Jet 4.0 Service Pack 5 and later. For additional information about how to obtain the latest Jet 4.0 service pack, click the following article number to view the article in the Microsoft Knowledge Base:
    239114 (http://support.microsoft.com/kb/239114/) How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
    Dale Houston, TX

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good call axsprog - I'd be curious if the table mentioned has a unique identifier.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    We may have found the problem; the table has an index of data type BIGINT, and appearently Access does not support this.

    This doesn't explain why it worked for a while and then stopped. The developers here are trying to decide the best path forward, so I can't even try and see if that's truly the problem.

    I'll keep you guys posted - that's for the feedback so far.

    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Well, that seems to be the problem. The SQL Admin created a query and changed the format ofd that field to Long Integer and now Access reads it no problem.

    Microshaft strikes again...


    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  11. #11
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Too late. I had the same problem when I was setting up my Access ADP. I don't remember the article but I remember why it was happening. When a table in SQL Server has a Unique Identifier, then the first thing that happens when retrieving the row information is Jet retrieves that field. In your case it tries to put the BIGINT into a Long data type and the data is truncated. Jet then uses the Long field to retrieve the information for each row. But since each value has been truncated there is no match for the row identifier and therefore #Deleted is returned.

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    DC, thanks for the post - I was wondering why I returned #Deleted instead of #Name, which is the typical Access response to something it doesn't like in an ODBC data source.

    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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