Catastrophic Failure

[Jayyde]

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim index As Long

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn

strSQL = "DELETE FROM dbo_tblProductCategoryValue " _
& "WHERE iProductMasterId=" _
& id _
& " AND iProductCategoryId=" _
& intCatID & ";"

cmd.CommandText = strSQL
cmd.Execute***********

That code is throwing a catastrophic failure on the .execute command. I put
a breakpoint in and grabbed the strSQL, put it into query manager and it's
fine. The weird thing is, it's acting like it's doing the delete twice off
this one command, becuase the record's getting deleted. I have no idea
what's causing this given that I'm using the same code in another form with
no issues. (I'm sure it's something retarded again, but any help you gurus
can give me is much appreciated =) )

Thanx ahead of time!

-Jayyde

 

[Jayyde]

Ok, since it seems I've finally stumped you guys (or none of you are getting
on today) if I hypothetically tried to do this with a stored procedure
(which seems retarded, but if it'll work I don't much care atm) then what in
the marked line in the following code would I have to change the sp's name
to to get this to work. No matter what I've tried it says it can't find the
sp.

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim prm As ADODB.Parameter

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo_ProductCategoryValueDelete"
Set prm = cmd.CreateParameter("@ProductMasterID", adInteger,
adParamInput, , id)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ProductCategoryID", adInteger,
adParamInput, , intCatID)
cmd.Parameters.Append prm

cmd.Execute

So far I've tried "dbo_ProductCategoryValueDelete" and just
"ProductCategoryValueDelete" neither of which it can find. On SQL2k itself
it's called "ProductCategoryValueDelete" (obviously) but I wasn't sure if it
needed the owner designated or not. Apparently that's not its issue with it
though.

Thanx as always gurus =)

-Jayyde

 

[Brendan Reynolds]

First, your original code worked for me. The only thing I changed was the
SQL statement, because obviously I don't have your tables, but other than
that, I used your exact code, copied and pasted from your newsgroup post.
I'll post it below for reference.

Second, I'm assuming this is an MDB not an ADP? If so, the reason your code
can't find the sproc is because CurrentProject.Connection is a connection to
the MDB, not to SQL Server. The MDB 'knows' about linked tables, but it
doesn't 'know' about sprocs. You need to open a connection to the SQL Server
and assign that connection to the ActiveConnection property of the Command
object to work with the sproc.

Here's that test code that worked for me ...

Public Sub TestSQL()

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim index As Long

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn

' strSQL = "DELETE FROM dbo_tblProductCategoryValue " _
' & "WHERE iProductMasterId=" _
' & id _
' & " AND iProductCategoryId=" _
' & intCatID & ";"

strSQL = "DELETE FROM dbo_Employees WHERE LastName = 'Reynolds';"

cmd.CommandText = strSQL
cmd.Execute

End Sub

 

[Jayyde]

Ok, $64million question then--since this code was working perfectly
yesterday--what could possibly have been corrupted since then? Barring that
the only other thing I can think of doing is in the error handling catching
that particular error number and simply continuing when it occurs (since the
event fires fine, does the delete/update--oh yeah it's doing it on updates
too--then throws the error in the same command).

 

[Brendan Reynolds]

I guess corruption of the VBA project could be the problem. You could try a
decompile. Make a copy of your MDB first, just in case. It doesn't happen
very often, but a decompile can, very occasionally, go wrong and damage the
VBA project instead of repairing it.

Once you've made your copy, start up Access and your app using the
/decompile switch, like so ...

"full path/name of msaccess.exe" /decompile "full path/name of your MDB"

The quotes above are required if there are any spaces in your folder paths
or file names, but note how the /decompile switch is outside the quotes.

If that doesn't fix it, try creating a new, empty MDB and importing all of
the objects from the old one. It's a chore, because you have to re-do any
option settings and custom properties that can't be imported, but it will
sometimes fix corruption when the /decompile switch fails.

I don't think you mentioned what version of Access you were using? If it is
Access 2000, make sure you have all the service packs installed and/or
consider upgrading to a later version. While this kind of problem is not
unknown in later versions, I see it much less frequently in Access 2002 or
Access 2003 than I did in Access 2000.

 

[Jayyde]

Just my luck, but neither of those fixed it. Opened it with the /decompile
switch and made a fresh db with all the objects imported. Still got
catastrophic failure on both =\. I guess I'm just going to catch that error
in an if-else for now unless someone can suggest something else to try...

Thanx for all the help though Brendan =).

-Jayyde

 

[Brendan Reynolds]

I tried a search at Google, and the results seem to indicate that this may
be a bug in the SQL Server ODBC driver rather than anything in your code.
Take a look at the search result at the URL below, and maybe try installing
the latest version of MDAC if you haven't already done so.

http://www.google.ie/search?hl=en&q=catastrophic+failure