March 2, 2005 at 3:35 pm
When i run a query in access I return a bunch fields and some may be null and are left blank in the query but i have one field that instead of being blank it shows #Deleted. If i use the SQL analyzer it shows blank like it should but in access it shows teh other way. Iwonder if it has to do that teh field is a primary key in another table. How could you fix that.
March 2, 2005 at 6:28 pm
I already saw that once. I was using a memo field. This bug occured when that memo field suddenly grew from only a few bytes to a few mb. The update would fail and once I requeried that field it was showing #Deleted instead of my text. I then compacted/repaired the db but then I lost the entire row. The only solution that could have helped me in this case was BLOBING but I never actually got around to fully checking this out.
I hope this can help you, if not then I have no idea of what is causing that.
March 3, 2005 at 1:32 am
March 3, 2005 at 2:55 am
As Habib says, it could be that your table definition in Access is not in line with SQL Server. Pay special attention to the Primary Key, as I have seen this error occur if the key is out of date.
March 3, 2005 at 3:15 am
Yes this is common problem... had it loads of times.
It's related to changes in the SQL data, when Access can't work out what's going on.
A really common cause of this is that Access doesn't know which column has the primary key. You tell it this when you first link the table.... you must select which column or columns define each unique row (whey actually don't need to be the real primary key but it's not sense to ignore that).
To get over this delete the link to the table and re-link it, choosing your primary key when it asks about the columns (it's actually been ages since I did this so can't remember exactly how the dialogue goes).
Hope that helps
Sam
March 3, 2005 at 9:44 am
I believe you can also see this message if you have some bit columns have no default value... well, something along those lines.
March 3, 2005 at 11:15 am
I have also seen that in Access when I have the table open while I delete records. When I switch back to the table I will see #Deleted until I either close and reopen the table or refresh the table. It may be that the display is not refreshing properly.
Don
March 3, 2005 at 10:16 pm
Relink your table to SQL, delete link then link again, the column schema could of been changed in SQL or corrupted in Access.
This may happen if any changes, even minor, like an index was changed on the SQL table and the Access table links were not refreshed.
Primary keys are good, a basic neccesity, make sure your table in SQL has them, much easier for Access to know how to update a row correctly.
adding a timestamp column to your SQL table, Access will use it to optimize data refreshing, this may help with your problem.
Sometimes I have seen certain trigger behaviours on or against the SQL table give this symptom. Modifying or temporily disabling the trigger would eliminate the symptom.
Your connection string or ODBC properties may need to be fined tuned, this really depends on your network, on how to do this, experiment and testing for each enviroment worked out the best way for me to do it.
Dan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply