MS Access showing #deleted in a table linked to SQl server

Posted on 2004-11-03
Last Modified: 2008-03-05
Hi all

doing my head in showing #deleted in all records for a linked table to a SQL 2000 back end using odbc. Other tables using the ODBC are fine

can anyone help!!

Question by:davoman
    LVL 16

    Expert Comment

    possibly this may help;en-us;264766;en-us;323201

    also i found that if the sql table name has the following togther, the problem you have may happen:

    1, the table name has spaces
    2, the field name have spaces
    3, there plenty of records and fields in the table

    the above can be tackled by removing the spaces from fiels names and the table name


    LVL 1

    Author Comment

    None of these seem to help.

    To elaborate, '#Deleted' appears in all records when viewing the table through Access. This has worked before, but after moving the database to a new server the problem occured. It also only seems to happen to this table - there are several other linked tables that all work perfectly.
    LVL 7

    Expert Comment

    Has the primary key of the table changed, or didn't it appear not unique for any reason in Access? Try to read the records through a query with the property "Snapshot" instead of "Dynaset". If you can see the records correct, it may a problem with the key or non-unique entries in the table. Another possible reason is a problem with your local odbc-driver, if the server changed to another version.
    LVL 1

    Author Comment

    Thanks for the suggestions but we found the solution in the end.

    In case anyone else runs across this problem, the reason was that a field type had been changed to bigint in SQL, and Access doesn't seem to support numbers of that size. Changing the field type back to int solved it (and it was just coincidence that it all happened when we moved servers - it was the first time the linked tables had been refreshed since the change)

    Accepted Solution

    PAQed with points refunded (500)

    Community Support Moderator

    Expert Comment

    I have exactly the same problem but I don't have any BigInt data type on any of my primary keys.
    Any suggestion?

    LVL 50

    Expert Comment

    by:Steve Bink

    This question has been closed for about 3 years.  You would do better to post a new question in the appropriate zones.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now