Disasterous Error

[BCullenward]

I get a critical error (don't remember exactly what it is off the top of my head but it's one of those very vague ones) when trying to execute a simple update statement through VBA on a SQL Server linked table.

When it crashes, it has actually executed the command but still pops up the Disasterous error message. When I make a new query (in both Access AND SQL Server) and type in the same syntax (substituting the actual value of the variable instead of hte variable name) it runs just fine, however when putting that same query (with the explicit value in it) back in the code it crashes.

Any advice would be appreciated.

Thanks

 

[RuralGuy]

How about posting the two SQL statements so we can see what works and what blows up.

 

[BCullenward]

How about posting the two SQL statements so we can see what works and what blows up.

Here is what is trying to be executed in the code (where the numbers are built from combobox values).
UPDATE AP_Dev SET Title_ID = 1 WHERE Title_ID = 18201

It pops up a Catastrophic Failure message box.

When I type that exact same thing as a query in Access it works fine.

When this query is built through the same comboboxes (VBA) as the other it does not give me a catastrophic failure:
UPDATE AP_Dev SET Name_ID = 41 WHERE Name_ID = 54

I have three other Update Statements that are just as simple that work fine, it's just this one that is causing the problem and they all have the same execute statement (2 share a statement) which is CurrentProject.Connection.Execute sqlUpdate

 

[RuralGuy]

Any chance Title_ID is a Primary Key?

 

[BCullenward]

no, it's not a primary key. I think I solved the problem when I hit enter, re-typed the exact same CurrentProject.Connection.Execute statement and commented out the previous one.

The weird thing is, I've had it pause during execution in other modules from where I formerly had a breakpoint (even rebooting did not help) the only thing that fixed it was hitting enter to cause a blank line be at that point in the code and the problem was fixed. (call it a MS Access quirk).

 

[RuralGuy]

It sounds to me as though you would benefit from some, if not all of the methods described in this link: Recovering from corruption
Especially the /decompile and creating a new db with Name Auto-Correct turned OFF.