Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2002
    Posts
    75

    Unanswered: Execute Stored Procedure from VBA

    Hi everyone!

    How can I execute a SS stored procedure from VBA code?
    The name of the stored procedure is "sp_setapprole" and the parameters I need to pass are "role_name" and "role_pwd".

    Thanks a lot in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I don't have any code, but I have seen examples of this using ADO and the Command object.

  3. #3
    Join Date
    Apr 2002
    Posts
    75
    I am looking for DAO example: we are not moving to ADO at this stage yet.

    Thanks.

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Create a pass-though query. For example, you can name it the same name as the SS stored procedure. The SQL for this pass through query would be:
    exec sp_setapprole ActualRoleName, ActualRolePassword Because you want these parameters to be able to change, you will need to set this SQL in VBA code. Because a password is involved, I would suggest that you change the SQL right after you exec to. To change the SQL from VBA, here is the (from memory, therefore untested) code
    Code:
    dim qrySQL as DAO.querydef
    set qrysql = Currentdb().QueryDefs("sp_setapprole")
    qrysql.SQL = "exec sp_setapprole " & role_name & ", " & role_pwd 
    qrysql.execute
    qrysql.SQL = "exec sp_setapprole role_name_here, role_pwd_here"
    set qrysql = nothing
    HTH,

  5. #5
    Join Date
    Apr 2002
    Posts
    75
    Hi GolferGuy!

    Thanks a lot for the code. I kind of don't get the second to the last line: why would I have this statement again after I execute the query?

    Code:
    dim qrySQL as DAO.querydef
    set qrysql = Currentdb().QueryDefs("sp_setapprole")
    qrysql.SQL = "exec sp_setapprole " & role_name & ", " & role_pwd 
    qrysql.execute
    qrysql.SQL = "exec sp_setapprole role_name_here, role_pwd_here"
    set qrysql = nothing
    Will "exec sp_setapprole role_name_here, role_pwd_here" be a "as-it-is" statement or I need to substitute role_name_here and role_pwd_here with something else? (Can it be just "" and "" ?)

    Thanks a lot and sorry for so many questions -- this is my first time dealing with connectivity issues and seems like there are not many sources out there to look for a guidance on how to implements all that.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i think the plan is to smash the .SQL so the pwd is not retained in the saved qdef

    meanwhile, you can also use a temporary qdef (example returns records, but you get the idea)
    Dim dabs As DAO.Database
    Dim qdef As DAO.querydef
    Dim recs As DAO.Recordset
    Set dabs = CurrentDb
    Set qdef = dabs.CreateQueryDef("")
    qdef.Connect = yourConnectionString
    qdef.SQL = "EXEC sp_who"
    qdef.ReturnsRecords = True
    Set recs = qdef.OpenRecordset(dbOpenSnapshot)



    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    izy,
    Great answer, thanks!

  8. #8
    Join Date
    Apr 2002
    Posts
    75
    When I'm trying to run the code

    Dim qrySQL As DAO.QueryDef
    Set qrySQL = CurrentDb().CreateQueryDef("sp_setapprole")
    qrySQL.SQL = "EXEC sp_setapprole " & "MY_Role" & ", " & "MY_Role_PW" & ";"
    qrySQL.Execute
    qrySQL.SQL = "exec sp_setapprole role_name_here, role_pwd_here"
    Set qrySQL = Nothing


    it gives me Rt error 3129:
    Invalid SQL statement; expected “DELETE’, ‘INSERT’, ‘PROCEDURE’,
    ‘SELECT’, or ‘UPDATE’.

    What can be a problem here? Please!!!! Thanks!

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Have you written the stored procedure named "sp_setapprole" in SQL Server? Or is it a system stored procedure? The error message makes me think the strored procedure is not in SQL Server.

    Plus, the
    & "MY_Role" & ", " & "MY_Role_PW" & ";"
    needs to have the double quotes (") taken away from MY_Role and MY_Role_PW. I am assuming that those two are variable names in your VBA code. Putting the double quotes around them makes then constants and I really doubt that your user role is MY_Role and I also doubt that the actual password is MY_Role_PW. I really do think those are the variable names that hold the actual role and actual role password.

  10. #10
    Join Date
    Apr 2002
    Posts
    75
    I've taken those quotes, but it still has the same problem.

    sp_setapprole is the stored procedure in the master db. I'm thinking, probably the problem is in my connection -- I am connected to my db with the string: "ODBC;Database=MY_DB;DSN=" & CurrentProject.Path & "\MY_DB.dsn".

    The code goes as following:Dim db As Database
    Dim qdf As DAO.QueryDef
    Dim str As String
    Dim role As String
    Dim pw As String

    role = "myRole"
    pw = "myPW"
    str = "EXEC sp_setapprole '" & role & "', '" & pw & "'"
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("sp_setapprole")
    qdf.Connect = "ODBC;Database=MY_DB;DSN=" & CurrentProject.Path & "\MY_DB.dsn"
    qdf.SQL = str
    qdf.ReturnsRecords = false
    qdf.ExecuteThe red line gives me run-time error '3265': Item not found in this collection.

    If I change the code to
    Code:
    str = "EXEC sp_setapprole '" & role & "', '" & pw & "'"
    Set db = CurrentDb()
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = "ODBC;Database=MY_DB;DSN=" & CurrentProject.Path & "\MY_DB.dsn"
    qdf.SQL = str
    qdf.ReturnsRecords = True
    qdf.Execute
    then the last red line gives this error:
    Run-time error ‘3065’: Cannot execute a select query.

    I am totally lost in-between Access and SS....

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    This stored procedure does not return records. You have qdf.ReturnsRecords = True. Try chaning that to False.

  12. #12
    Join Date
    Apr 2002
    Posts
    75
    I did change it to false... and that's what I'm getting now at the same qdf.execute line:

    '3151': ODBC connection to 'C:\MY_DB.dsn' failed.

  13. #13
    Join Date
    Apr 2002
    Posts
    75
    Thank you very much for your kindness, time and knowledge!!! Everything seems to be working now!

  14. #14
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    What was the change you made so it would start working?

  15. #15
    Join Date
    Apr 2002
    Posts
    75
    I cut the CurrentProject.Path from the "DSN..." part of the qdf.connect statement, and, instead, hardcoded the path into the string. I'm not sure that was the solution, but after that change the code started to run without exceptions. I'll test the procedure in the "real" env tomorrow, and if I have any issues again, I'll be back!

    Again, I so much appreciate your help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •