Execute sproc in VBA (1 Viewer)

Tupacmoche · Apr 25, 2019

Hi VBA Masters,

I have a stored procedure that I want to run from an Access button. The sproc simple gets an id_number as a parameter and adds a row/record to a table. That is it. This is the code that I use but is not working:

"EXEC dbo.usp_NewCPM_Item ='" & DonorNum '" 'Simple adds a row

How do, I execute this simple code to add a record?

theDBguy · Apr 25, 2019

Hi. I'm assuming you're using a pass-thru query for this. If so, you'll need to resolve the parameter outside of the query, which means you will probably have to use a QueryDef object to modify the SQL statement before executing it.

Tupacmoche · Apr 25, 2019

Here is the code for the entire procedure:

Code:

Private Sub txtAddDonor_AfterUpdate()
Dim DonorNum As String
Dim DonorName As String
Dim ValidDonorID As Integer
Dim StrFix As String
Dim LResponse As Integer

DonorNum = Nz(Me.txtAddDonor.Value) 'Name of textbox

'This code pads id with leading zero if not present.
If Len(DonorNum) > 1 Then
StrFix = Nz(Me.txtAddDonor.Value)
DonorNum = String(10 - Len(DonorNum), "0") & StrFix
Else
End If

Me.txtAddDonor.Value = DonorNum

DonorName = Nz(DLookup("Pref_Mail_Name", "dbo_LMC_Entity", "id_Number= '" & DonorNum & "'"))

If Len(DonorName) > 2 Then
    ValidDonorID = 1
Else
    ValidDonorID = 0
End If

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
            'Dim rst As DAO.Recordset
                      
            'Set db = CurrentDb
            'Set qdf = db.CreateQueryDef("")

            'qdf.Connect = db.TableDefs("dbo_CPM_Donors").Connect
            'MsgBox DonorName
            qdf.SQL = "EXEC dbo.usp_NewCPM_Item ='" & DonorNum '" 'Simple adds a row
            '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

The after update event of a text box takes the id_number entered and validates that exist and then simple uses the same id to add it to another table. Since, all the work is being done on the sproc do I need QueryDef? In the attached code, I could not get it to work but asked myself why do I need all this once the id is passed to the sproc it simple inserts a row and that's it.

theDBguy · Apr 25, 2019

Hi. In the code you posted, you commented out the qdf.Execute line. What happened when it wasn't commented out?

Tupacmoche · Apr 25, 2019

Run-time error 424
object required

Tupacmoche · Apr 25, 2019

I'm just wondering why, I can't just pass the parameter and be done with it. The table that being inserted to is an SQL table that is linked to this database.

theDBguy · Apr 25, 2019

Tupacmoche said:
Run-time error 424
object required

Oh, that's because you also have the following lines commented out.

Code:

'Set db = CurrentDb
'Set qdf = db.CreateQueryDef("")

Where did you get this code?

theDBguy · Apr 25, 2019

Tupacmoche said:
I'm just wondering why, I can't just pass the parameter and be done with it. The table that being inserted to is an SQL table that is linked to this database.

You are passing a parameter. The only thing is this is how you would do it from Access. Remember, if you were executing the sproc using SSMS, you would just type in the SQL statement. But since you're coming from Access, you'll sort of need a bridge to go between the two, and that's what a passthrough query is.

pbaldy · Apr 25, 2019

While I would probably use a pass through query for this too, you can use an ADO command object as well.

theDBguy · Apr 25, 2019

pbaldy said:
While I would probably use a pass through query for this too, you can use an ADO command object as well.

Which is another type of "bridge."

pbaldy · Apr 25, 2019

theDBguy said:
Which is another type of "bridge."

Certainly; each has a connection string to connect it to the SQL Server database.

Tupacmoche · Apr 25, 2019

I changed the code but now get an 3146 error - ODBC call failed.

Code:

Dim qdef As DAO.QueryDef
            Set qdef = CurrentDb.CreateQueryDef("")
            qdef.Connect = CurrentDb.TableDefs("[dbo_CPM_Donors]").Connect
            qdef.SQL = "EXEC dbo.usp_NewCPM_Item ='" & DonorNum '" 'Simple adds a row
            qdef.ReturnsRecords = False
            qdef.Execute dbFailOnError
                        
            Set qdf = Nothing
             Set cdb = Nothing

Can anyone see what wrong now?:banghead:

pbaldy · Apr 25, 2019

I use a saved pass through query and change the SQL of it. Then:

CurrentDb.Execute "QueryName"

Having a saved query would let you test it to make sure it's working.

Tupacmoche · Apr 25, 2019

I will do that once it is working so no issues going forward.

pbaldy · Apr 25, 2019

But now you don't know if the problem is in the code, the query SQL, the connection string, etc.

Tupacmoche · Apr 25, 2019

It' not the connection string since the table is part of the database and it's not the SQL because that was tested from SSMS and it works fine. So, it's something else.:banghead:

theDBguy · Apr 26, 2019

Tupacmoche said:
It' not the connection string since the table is part of the database and it's not the SQL because that was tested from SSMS and it works fine. So, it's something else.:banghead:

Hi. I would suggest going back to step 1 and make sure we eliminate every possibilites. For example, create a brand new passthrough query with something simple in it like

Code:

SELECT GETDATE();

See if this executes correctly. If it does, we know the connection works. Then, you can move on into trying to run the stored procedure. Change the passthrough query to an EXEC statement and include the parameter value. See if this runs manually. If it does, then we know the SP is good as well. That would leave with the VBA code to fix.

Galaxiom · Apr 26, 2019

Tupacmoche said:
I changed the code but now get an 3146 error - ODBC call failed.

Code:

qdef.SQL = "EXEC dbo.usp_NewCPM_Item ='" & DonorNum '""

That is not a valid SQL statement.

Try this:

Code:

qdef.SQL = "EXEC dbo.usp_NewCPM_Item '" & DonorNum & "'"

pbaldy · Apr 26, 2019

Ah good eye. I guess I got distracted by "and it's not the SQL because that was tested from SSMS and it works fine".

Execute sproc in VBA (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Registered User.

I’m here to help

I’m here to help

Wino Moderator

I’m here to help

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

I’m here to help

Super Moderator

Wino Moderator

Similar threads

Users who are viewing this thread