Error 86 Could not delete from specified tables

[gransbpa]

Hi there,

I am running code which deletes all records from a table using SQL. This worked before, but now all of a sudden the code does not work anymore: I get the error message 86: could not delete from specified tables. This is strange, especially because the code empties 8 tables, and only 4 tables generate cause this error to be raised. I can't seem to find out why, however. Does anyone have an idea?

Any help will be greatly appreciated, thanks.

 

[Trevil]

Is this using the ADO object? Are your tables linked to SQL server?

What happens if you open one of the tables that gives you the problem, highlight one or more rows and press the delete key? Does it still fail? If yes, and SQL server, do you have the necessary permissions?



"Hmmm, it worked when I tested it....

 

[gransbpa]

Hi Trevil

I use ODBC linked tables from a MSAccess 2003 front end approaching SQL server tables. I am not using ADO, but plain SQL in combination with the VBA command DoCmd.run(strSql). I can delete the data without any problem manually or using wizard made queries. And indeed: "Hmmm, it worked when I tested it...."
All of a sudden it stopped working. I approach 8 tables, from 4 of them I can't delete anymore, and I can't seem to find a logical attribute which distinguishes these 4 tables from the other 4....

 

[Trevil]

A couple more questions:
1. Have you tried changing the order in which the queries are run? i.e. instead of 1, 2, 3, 4... try 8, 7, 6 ...
2. You said "I can delete the data ....using wizard made queries" -- does that mean you can build 8 queries and they all run just fine? If so, what's the difference between your saved sql and the 'generated' sql? Have you tried saving one of the generated queries and using that in your 'DoCmd...' (but only for table 5, 6, 7, 8)?

"Hmmm, it worked when I tested it....

 

[gransbpa]

Hi Trevil, I tried changing the order, and indeed I found that table nr. 5 caused the problem, the code crashed and 6, 7, and 8 were neither deleted. When moving the code deleting table 5 to the end, table 6, 7, and 8 could be deleted without any problem. The strange things remains that, indeed: queries worked, but sql called from vb code not.

Anyway, I have found the problem: in table nr. 5 I use access memo fields. These can sometimes exceed the number of 4000 characters. Apparently, 4000 is some kind of magical border when using ODBC, even if you use SQL server "mega-data types" like ntext. The fields were filled when the number of characters exceeded 4000, but the contents was reflected as <LONG TEXT>, and for some strange reason could not be deleted anymore from my code.
However, I can bypass the problem by preventing the user from entering more than 4000 characters in the aforementioned field, and then the problem seems to be solved.

So, you set me on the right trail by mentioning the importance of the order of the queries, since that made me realize in which table the problem originated.

Thanks for the help!

 

[Trevil]

Glad you were able to sort this thing out!

"Hmmm, it worked when I tested it....