locked
MS ACCESS -Pass through query, giving error. RRS feed

  • Question

  • I have one MS-Access application (.accdb format) and accessing database sql server 2012. I want to execute parameterized stored procedure through pass through query. I followed following steps

    1.     Created query using menu option (Design-> Pass-through )
    2.   Entered below script inside newly created query and save it as sp_SysCheckUserRole

     EXEC  dbo.spSysCheckUserRole 'sttli', 'rolAdressAdmin'

    Where SP name is dbo.spSysCheckUserRole and two parameters are passed

    (I am not sure if above approach is correct or not. As stored procedure recommend some parameter due to this I am passing some default value to query)

     3. Below code written

    Dim sqlquerystring As String

    Dim qd As dao.QueryDef

    Set qd = CurrentDb.CreateQueryDef(vbNullString)

    qd.Connect = "ODBC;DRIVER=SQL Server;SERVER=IN1149065W0;UID=sttli;PWD=Password123;DATABASE=PWP"

    qd.ReturnsRecords = True

    sqlquerystring = " sp_SysCheckUserRole"

    For i = 1 To bytSpIn  'Erst alle Input Parameter

       sqlquerystring = sqlquerystring & "'" & varPara(i, 5) & "', "

    Next

    ‘Creating sqlquery string as  sp_SysCheckUserRole ‘Admin’, 'rolAdressAdmin'

    sqlquerystring = Left(sqlquerystring, Len(sqlquerystring) - 1)

    qd.SQL = sqlquerystring

    qd.Execute

    But I am getting error

    Pass through Query nvalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE

    Can you please guide me how can we execute parametrized stored procedure (sql server) through pass through query.

    Friday, October 9, 2015 5:07 PM

Answers

  • Thank you for response, I investigated my code and I found my stored procedure is returning record, and I was using execute statement in place of that I had to you below code

     Dim qd As DAO.QueryDef
       
      Dim rs As DAO.Recordset
      
       Set qd = CurrentDb.CreateQueryDef(vbNullString)
       qd.Connect = "ODBC;DRIVER=SQL Server;SERVER=IN1149065W0;UID=sttli;PWD=Password123;DATABASE=PWP;"
     
         
        sqlquerystring = "EXEC dbo.spSysCheckUserRole"
        For i = 1 To bytSpIn  'Erst alle Input Parameter
            sqlquerystring = sqlquerystring & "'" & varPara(i, 5) & "', "
       Next
       sqlquerystring = Left(sqlquerystring, Len(sqlquerystring) - 1)
       qd.SQL = "EXEC dbo.spSysCheckUserRole 'sttli', 'rolAdressAdmin'"    'sqlquerystring
        qd.ReturnsRecords = True
       'qd.Execute
       Set rs = qd.OpenRecordset
       rs.Close
       'qd.Execute
       qd.Close

    Saturday, October 10, 2015 12:58 PM

All replies

  • You need to initialize the variable sqlquerystring to

      sqlquerystring = "EXEC dbo.sp_SysCheckUserRole "

    Also note the trailing space.

    Matthias Kläy, Kläy Computing AG 

    Saturday, October 10, 2015 7:06 AM
  • Thank you for response, I investigated my code and I found my stored procedure is returning record, and I was using execute statement in place of that I had to you below code

     Dim qd As DAO.QueryDef
       
      Dim rs As DAO.Recordset
      
       Set qd = CurrentDb.CreateQueryDef(vbNullString)
       qd.Connect = "ODBC;DRIVER=SQL Server;SERVER=IN1149065W0;UID=sttli;PWD=Password123;DATABASE=PWP;"
     
         
        sqlquerystring = "EXEC dbo.spSysCheckUserRole"
        For i = 1 To bytSpIn  'Erst alle Input Parameter
            sqlquerystring = sqlquerystring & "'" & varPara(i, 5) & "', "
       Next
       sqlquerystring = Left(sqlquerystring, Len(sqlquerystring) - 1)
       qd.SQL = "EXEC dbo.spSysCheckUserRole 'sttli', 'rolAdressAdmin'"    'sqlquerystring
        qd.ReturnsRecords = True
       'qd.Execute
       Set rs = qd.OpenRecordset
       rs.Close
       'qd.Execute
       qd.Close

    Saturday, October 10, 2015 12:58 PM