locked
best way to execute sql stored procedure with parameters from access 2010 RRS feed

  • Question

  • Hello,

    After scowering the internet, what is the BEST way to execute a stored procedure from within access 2010?

    I am trying to execute the following and it gives me a run time 3129 error....   I am using a .accdb access file type...

    Private Sub DeleteRecord_Click()
    
    DoCmd.SetWarnings False
    If MsgBox("You are About to Delete a Record", vbYesNo, "Warning") = vbYes Then
     
     CurrentDb.Execute "exec sp_delete_investor" & " " & ID.Value, dbFailOnError
    End If
    DoCmd.SetWarnings True
    
    
    
    End Sub
    
    

     

    Thanks!

     


    ddd
    Friday, June 10, 2011 3:18 PM

Answers

  • Hi,

    you should use a pass-through queries for direct server communication. Create a pass-through query with any line of code you want, it may be

     

    exec sp_delete_investor 1
    

    it doesn't matter because you will change this code before executing.

     

    Call it, for example, PTQ. Set the connection with DSN or without it. If you already have some tables linked to this server, you can just open an Immediate Window (Ctrl+G), type

     

    CurrentDb.QueryDefs("PTQ").Connect = CurrentDb.TableDefs("MyTable").Connect
    

     


    and press Enter.

    Now you have a pass-through query and we can go to the button code.

    Private Sub DeleteRecord_Click()
    Dim qdf as QueryDef, err_msg$, err as DAO.Error
    If MsgBox("You are About to Delete a Record", vbYesNo, "Warning") = vbYes Then
    On Error GoTo err_lbl
      Set qdf = CurrentDb.QueryDefs("PTQ")
      qdf.SQL = "exec sp_delete_investor" & " " & ID.Value
      qdf.Execute dbFailOnError
    Else
      Exit Sub
    End If
    exit_lbl:
      Set qdf = Nothing
      Exit Sub
    err_lbl
      For Each err in DBEngine.Errors
        err_msg = err_msg & Err.Number & ": " & Err.description & vbCrLf
      Next
      MsgBox err_msg, vbCritical, "MyApp"
      Resume exit_lbl
    End Sub
    




     


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by dbansal Friday, June 10, 2011 5:21 PM
    Friday, June 10, 2011 4:49 PM

All replies

  • Hi,

    you should use a pass-through queries for direct server communication. Create a pass-through query with any line of code you want, it may be

     

    exec sp_delete_investor 1
    

    it doesn't matter because you will change this code before executing.

     

    Call it, for example, PTQ. Set the connection with DSN or without it. If you already have some tables linked to this server, you can just open an Immediate Window (Ctrl+G), type

     

    CurrentDb.QueryDefs("PTQ").Connect = CurrentDb.TableDefs("MyTable").Connect
    

     


    and press Enter.

    Now you have a pass-through query and we can go to the button code.

    Private Sub DeleteRecord_Click()
    Dim qdf as QueryDef, err_msg$, err as DAO.Error
    If MsgBox("You are About to Delete a Record", vbYesNo, "Warning") = vbYes Then
    On Error GoTo err_lbl
      Set qdf = CurrentDb.QueryDefs("PTQ")
      qdf.SQL = "exec sp_delete_investor" & " " & ID.Value
      qdf.Execute dbFailOnError
    Else
      Exit Sub
    End If
    exit_lbl:
      Set qdf = Nothing
      Exit Sub
    err_lbl
      For Each err in DBEngine.Errors
        err_msg = err_msg & Err.Number & ": " & Err.description & vbCrLf
      Next
      MsgBox err_msg, vbCritical, "MyApp"
      Resume exit_lbl
    End Sub
    




     


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by dbansal Friday, June 10, 2011 5:21 PM
    Friday, June 10, 2011 4:49 PM
  • andrey, this worked perfectly. What is the point of putting that code in the immediate window???
    ddd
    Friday, June 10, 2011 5:22 PM
  • It's just a sort of fast query connection setting. When you create a pass-through query, the wizard asks you to choose a DSN. This was so in Access 2003, I don't know how it works now in A2010. So, I prefer DSN-less connection and just use this line of code to make it a bit faster. It is not necessary.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Friday, June 10, 2011 5:37 PM
  • right, but the immediate window resets every time yes? so that line does not get saved.
    ddd
    Friday, June 10, 2011 5:40 PM
  • Immediate window - yes, but not the .Connect property of the QueryDef object you are setting via it. So, it is not the line of code that is got saved but the .Connect ptoperty is. 
    Andrey V Artemyev | Saint-Petersburg, Russia
    Friday, June 10, 2011 5:46 PM