Getting '#Deleted' when querying ODBC table

[itchyII]

Hi Everyone,
Does anyone know why I get '#Deleted' for all fields and all records when I am querying a certain ODBC (SQL server) table? The query is a simple select query with only the one table. The table is on SQL server and I have other tables on the same server that I query without this problem. The table is large, over 8 million records and is actually a dump from another database. It gets updated every day. I assume they are dropping the table and then reloading it. I thought that maybe this is what was causing it but I have many other tables from the same souce that they dump and reload and I don't have the problem with them. Any ideas?

Thanks!

 

[dhookom]

I would check primary key information. Does this table have one? Does Access find the primary key?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[itchyII]

Yes, the table does have a primary key but it does not change from the previous days dump. I wouls only see new records with new primary keys.

 

[dhookom]

What happens if you refresh the linked table or relink it?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[itchyII]

Same.

 

[dhookom]

Sorry I can't be of much more assistance. The only time I have seen that particular problem is when a primary key field in a linked table contains null values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[itchyII]

That is not the case here, there are no nulls in the key field.

Thanks anyway!

 

[BPhilb]

I've had the problem in the past when the table you are trying to append to sets up a different field type than the ODBC table. It's usually a text to a number field or vice versa where I see the issue.

 

[hovercraft]

#Deleted happens when two clients try to update the same record at the same time. When the second person tries to update (I.E. moving to a new record) the record becomes corrupt and the field values = "#Deleted"

The only thing I can do when this happens is to remove any indexes and then delete the offending records, compact/restore and then append the records I deleted from a good backup set and then re-enter the indexes.

Hope this helps,
Hovercraft

 

[PHV]

No BitField allowing null if the SQL server table ?
A workaround is to create a Timestamp field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[itchyII]

Hi Everyone, thanks for the replies.

BPhilb - already checked this, its not the case.

hovercreaft - the table I am talking about is an archive table. The only updating that takes place is once an hour when they drop the table and reload it.

PHV - I'm not sure what you mean by 'NO BitField' but there is a date/time stamp field, in the table but it is the date/time of the original record, not the hourly load.

Thanks! I'm still trying to find a solution to this.

One other thing that I've realized, if I do a join with another table on just one field, the records appear normally. Go figure!!!

 

[hovercraft]

Can you open the table without using a query from the tables menu? Is the data OK?
If you can and the data looks fine then the problem is in the query itself.

If you try to run a query while an update is happening you'll get #delete. Make sure that no other process or script is running when you run your query.

 

[hovercraft]

...or could someone else be accessing the original table at the time it is being copied? That would do it too.

 

[hovercraft]

...also, have a look at this
[link]http://support.microsoft.com/kb/128809[link]

(sorry, I'm having a bad case of "afterthought" tonight)

 

[itchyII]

Good morning hovercraft,
If I open the ODBC table directly, I get the same problem. I took a look at the link you provided and saw some interesting information, but I'm not sure it really applies here. The table I am looking at is, for all practical purposes, read only. This table only gets updated once an hour and no one is ever updating any records in this table. All updates get done on the live table. The live table is an audit trail with an enormous amount of activity. This is why we created the dump. So that we could run all of our queries and reports without weighing down the live table. As for your idea about someone accessing the original table when it is being vopied, this is most definitly happening. As I said, it is an audit trail table and captures all transactions taling place in the system. But if this is the case, then the there would be '#Deleted' records in the table on the SQL server. But if I go directly to the SQL server and query or open the table from there, all records are complete and I don't get the '#Deleted'. This only happens if we try to connect through Access and ODBC.

I took a look at the indexes of the table and there are some fields that are indexed (non unique) that may have null values. Do you think that that could be it? But even if it is, how do I get around this? The table is over 8 million records long. If I remove those indexes, it will take forever to query!

Any more 'afterthoughts' ;-)

 

[hovercraft]

8 million records x number of fields every hour! Great Horny-Toads!!
Are you compacting after each copy? The way I see the issue, you should not delete the entire table and then remake it. Rather you should have a field on the original table such as a boolean [IsUpdated] or something whenever you have a change occur on a record and instead of recreating 8 million records per hour, you only update/append the changed/new ones. Else you are probably trying to query against a table that is somewhere in the process of either being deleted or copied or created.
As an after, afterthought, have you tried to create a brand new .mdb (perhaps on a different machine) and create a new odbc to the sql server, run the same query on both and see what you get.

 

[itchyII]

Yeah! tell me about it! The problem is this app is not home grown. And we have no control over the original table structure. I would agree that this is probably not the best way to do this, but changing procedures around here is like like trying to get the govt to reduce taxes!

But why wouldn't I experience the same thing when querying
through SQL?

As for the new mdb, etc. I've done this inadvertently. I have changed PC's and I have also created different mdb's accssing the same table. All with the same result. And as I mentioned, if I access the table through multiple queries, the data comes out ok, its only if I query the table without joining to antother table that I get this problem.