Run Sproc with parameter in VBA

[Tupacmoche]

I have an Access textbox with an after Update event that run this code. Simply put it gets a ten digit string that is passed to an SQL sproc that creates a record and inserts it into a table. Here is the code:
I get a runtime error message 3265: Item not found in this collect. Does anyone see what's wrong?

If ValidDonorID = 1 Then
    
    LResponse = MsgBox("Is this the Donor your looking for " + DonorName + "?", vbYesNo, "Continue")
        
        If LResponse = vbYes Then
            MsgBox ("Adding new prospect!") '**** THIS IS WHERE THE CODE WILL GO TO UPDATE THE TABLE **** - Stored Procedure
            
            '*****Run sproc to add new donor to CPM *************************
            Set cdb = CurrentDb
            Set qdef = cdb.CreateQueryDef("")

            qdef.Connect = cdb.TableDefs("dbo_Import_MRN").Connect
            qdef.SQL = "EXEC dbo.sp_MRN_CleanUp" 'Simple runs a truncate table
            qdef.ReturnsRecords = False
            qdef.Execute dbFailOnError
            
            Set qdef = Nothing
            Set cdb = Nothing
            '******End of sproc ********************************************
            
            MsgBox ("New prospect added!")
        Else
            MsgBox ("Please try again!")
        End If
Else
    MsgBox ("Donor with Id_Number: " & DonorNum & " does not exist, try again or add to Advance!")
    Me.txtAddDonor.Value = ""
End If

End Sub

 

[Minty]

Which line is highlighted when you get the error ?

 

[Tupacmoche]

qdef.Connect = cdb.TableDefs("dbo_Import_MRN").Connect

 

[Tupacmoche]

I just realized that I had but the wrong table name in the string. But now, I getting an ODBC error. 3146 ODBC call failed.

qdef.Connect = cdb.TableDefs("dbo_CPM_Donors").Connect

 

[Frothingslosh]

ODBC call failed is a generic error that doesn't tell you what's going on. It could be a time-out, the stored procedure not existing, the procedure failing, anything.

Have you confirmed that the stored proc works when run manually?

I'd also suggest changing dbFailOnErrror to 'dbSeeChanges + dbFailOnError'. It's PROBABLY not the cause of the error, but it won't hurt anything and may help.

 

[Tupacmoche]

The sproc works fine I can run it in SSMS without issue, I'm now trying to run it from an Access form.

 

[Frothingslosh]

I know you are, I just wanted to confirm it worked fine on its own.

My next step in your shoes would be to create a saved pass-through query, update it with the correct connection string, include the EXEC code, save it, and run the query. That will tell you if it's the form's vba or if it's the syntax or something about the connection.

 

[Tupacmoche]

I revised the code but continue to get the ODBC error on this line:

 qdf.Execute dbFailOnError

Here is all the code:

If ValidDonorID = 1 Then
    
    LResponse = MsgBox("Is this the Donor your looking for " + DonorName + "?", vbYesNo, "Continue")
        
        If LResponse = vbYes Then
            MsgBox ("Adding new prospect!") '**** THIS IS WHERE THE CODE WILL GO TO UPDATE THE TABLE **** - Stored Procedure
            
            '*****Run sproc to add new donor to CPM *************************
            Dim db As DAO.Database
            Dim qdf As DAO.QueryDef
                        
            Set db = CurrentDb
            Set qdf = db.CreateQueryDef("")

            qdf.Connect = db.TableDefs("dbo_CPM_Donors").Connect
            qdf.SQL = "EXEC dbo.sp_MRN_CleanUp =" & "" & DonorNum 'Simple runs a truncate table
            qdf.ReturnsRecords = False
            qdf.Execute dbFailOnError
                        
            Set qdf = Nothing
            Set cdb = Nothing
            '******End of sproc ********************************************
                        
            MsgBox ("New prospect added!")
        Else
            MsgBox ("Please try again!")
        End If
Else
    MsgBox ("Donor with Id_Number: " & DonorNum & " does not exist, try again or add to Advance!")
    Me.txtAddDonor.Value = ""
End If

End Sub

 

[Frothingslosh]

Okay, two things:

One thing that just jumped out at me is this:

qdf.SQL = "EXEC dbo.sp_MRN_CleanUp =" & "" & DonorNum 'Simple runs a truncate table

You normally don't do 'EXEC StoredProcedure = Value'. Try changing it to this:

qdf.SQL = "EXEC dbo.sp_MRN_CleanUp " & DonorNum

Alternately, add in the parameter name like this:

qdf.SQL = "EXEC dbo.sp_MRN_Cleanup @ParamName=" & DonorNum

Obviously, you exchange ParamName with the actual parameter name.

Also, parsing in an empty string between a string and a number really does absolutely nothing, so there's no need for the & "" after the equals sign.

Another possibility: does the stored procedure not only require a number, but the same TYPE of number that DonorNum is? If it is looking for a bigint and you're feeding it a long int (int), you could have an issue. If it's looking for a string of some kind and you're feeding it a long int, you DEFINITELY have an issue.

 

[Tupacmoche]

I have revised the code as you suggested but still get the same error message. Also DonorNum is a String in vba and on the SQL side the parameter is a nvarchar(10) so the same data type.

 

[Galaxiom]

Pass through queries must use the server syntax. The string delimiter needs to be a single quote.

qdf.SQL = "EXEC dbo.sp_MRN_CleanUp [COLOR="Red"]'[/COLOR]" & DonorNum & "[COLOR="red"]'[/COLOR]"

 

[Galaxiom]

BTW Names starting with sp_ should not be used for Stored Procedures.

https://sqlity.net/en/961/the-mysterious-sp_-system-procedure-prefix/