locked
Copying SQL Server stored procedure results into a query in Access 2010 using ADODB RRS feed

  • Question

  • Hi,

    I am trying to connect to a SQL database, run a stored procedure, retrieve a recordset and set the recordset to a query saved in an MS access frontend. I know how to do this with DAO like this:

        Dim dbs As DAO.Database
        Dim stSQL As String
        Dim qdf As DAO.QueryDef

        Set dbs = CurrentDb
        Set qdf = dbs.QueryDefs("pqryPlan")

        stSQL = "Exec spSourcePlan_Lineitems " PlanID

    qdf.SQL = stSQL

    I can then open the query: pqryPlan in MS access which will have all the records returned by the stored procedure.

    My question is that can I achieve this with ADODB? I'm familiar with the connection, command, command parameters through which I'm able to execute stored procedures but not sure how to get the retrieved recordset into an access query.

    Thanks for the help in advance

    Anand

    Wednesday, June 3, 2020 10:24 AM

Answers

  • Well, to be fair?

    Yes, using ADO most certainly gives you strong data typing of parameters sent to SQL server, and that of store procs.

    So, the BEST case and reason to use ADO with parameters is exactly your case. So, 100% agree that beyond say 1 or 2 parameters? Then a string  concentration is messy, and less ideal. And for store procs that do not return a data set (table/select), but have return parameters? Then once again, ADO is a better choice here.

    So, in "many" cases the above dao and leveraging built in objects is the  least amount of code, and you don't have to deal with connection setup or strings in code.

    So, using ADO? Yes, I am not  trying to discourage.

    Having nice strong typed parameters in code as opposed to a

    "exec -- build your params string here"

    Is certainly LESS then ideal.

    is not all that great.

    And you CAN use ADO to set a forms data source.

    You load up a ADO reocodset, and then go:

    me.RecordSet = MyADORst

    So, some ADO to call a store  proc would/could look like this:

          
       Dim rs            As New ADODB.Recordset
       Dim cn            As New ADODB.Connection
       Dim cmd           As New ADODB.Command
       
       Dim intPassValue  As Long
       
       intPassValue = 10
       
       cn.ConnectionString = "Provider=SQLOLEDB;Server=.\SQLEXPRESS;Database=TEST3;UID=TEST3;PWD=TEST3"
       cn.Open
       With cmd
          .ActiveConnection = cn
          .CommandType = adCmdStoredProc
          .CommandText = "Times2"
          .Parameters.Append .CreateParameter("@MyValue", adInteger, adParamInput, , intPassValue)
       End With
       
       Set rs = cmd.Execute
    

    You can now set the forms recordset to the ADO result.

    eg:

      Set Me.Recordset = rs

    So, you can load up the ado recordset, and "bind" it, or set it to the form.

    R

    Albert


    • Marked as answer by AnandBhatta Saturday, June 13, 2020 10:00 AM
    Thursday, June 4, 2020 10:24 PM

All replies

  • You  can  use even less code like this:

    currentdb.QueryDefs("pqryPlan").SQL = ""Exec spSourcePlan_Lineitems " & PlanID

    docmd.OpenReport "myReport",acViewPreview

    or

    docmd.OpenForm  "myForm"

    So its only two lines of code.

    If you need this as a dao recordset, then

    dim  rst       as  DAO.recordSet

    currentdb.QueryDefs("pqryPlan").SQL = ""Exec spSourcePlan_Lineitems " & PlanID

    Set rst = currentdb.OpenRecordset("xddfds")

    So, in the above, it really only one line of code to setup the  PT query, and one more to launch the form/report (just simple base the report/form on that PT query and you are done).

    So, using the internal DAO objects saves boatloads of code, because a form/report can have its data source set directly to the PT query. 

    You can use ADO to load up a ado recordset, but you can't use ADO to "create" a query, since the built in objects are DAO objects.  Using ADO has very little to do with creating a query.

    You can  certainly create some on the fly SQL query, and load that result into a ADO recordset but for the built in objects? No, you can't use ADO to create a built in query - they are simply built in objects - but behind the scenes they are mostly DAO objects anyway.

    So, you can certainly pull the results of an ADO query in code, or the results of a store proc into a ADO recordset, but creating actual access objects, or using the nice 2 lines of code above to setup and pull data into a report? No, ADO will not help  you.

    It is possible that you just wanting to call the store proc and load up a ADO reocrdset - you certainly can do that (and I might  have miss-understood your question). There is no performance advantage to use the above or ADO, but with ADO you have to pull the data into a ADO recordset as opposed to having a form (or report) based directly on the PT query like the above example(s).

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Wednesday, June 3, 2020 11:50 PM
  • Thanks Albert. I have gained a lot of Access knowledge from reading your posts in the past. 

    I am trying to create an in built PT query and set is as the recordset of a form. I also use the same PT query to filter the Form by sending optional parameters to the Stored Proc. I've done this with DAO and it works fine but I find that passing parameters, especially if there are several of them and some are optional in the Stored Proc then the DAO solution is tedious (if I may say so).

    Hence the attempt to use the ADO Command object (which helps clearly define the parameter types). I may have missed the same features in DAO - please let me know if this is possible.

    Regards,

    Anand


    Thursday, June 4, 2020 6:13 AM
  • I've done this with DAO and it works fine but I find that passing parameters, especially if there are several of them and some are optional in the Stored Proc then the DAO solution is tedious (if I may say so).

    Hi anandb,

    Is it an option to construct the whole SQL-string, including the formatting for dates, text and reals, and use that SQL-string to populate a DAO-recordset?

    All my SQL-strings are constructed on the spot context-dependant, in this way, and I never had the need to use the parameters.

    Imb.

    Edit: I just realise that you have a stored procedure. I have no experience with that, so probably this does notwork for you.

    Imb.

    • Edited by Imb-hb Thursday, June 4, 2020 12:51 PM
    Thursday, June 4, 2020 12:39 PM
  • Link the relevant sql server table or view to MS Access. Run a query against the linked table using MS Access syntax and update the staging table.

    It is also possible to update an MS Access table using a connection string for SQL Server in-line in your query.

    SELECT *
    INTO   newtable
    FROM   [odbc;filedsn=Z:\DSN\test.dsn].table1
    Working from the SQL Server end, you can use MS Access as a linked server or run a query and update from there.

    INSERT INTO
    OPENDATASOURCE(
       'Microsoft.ACE.OLEDB.12.0', 'Data Source=z:\docs\test.accdb')...[table1] 
       ( atext )
    SELECT atext FROM table1 WHERE id=2
    Thursday, June 4, 2020 3:08 PM
  • Well, to be fair?

    Yes, using ADO most certainly gives you strong data typing of parameters sent to SQL server, and that of store procs.

    So, the BEST case and reason to use ADO with parameters is exactly your case. So, 100% agree that beyond say 1 or 2 parameters? Then a string  concentration is messy, and less ideal. And for store procs that do not return a data set (table/select), but have return parameters? Then once again, ADO is a better choice here.

    So, in "many" cases the above dao and leveraging built in objects is the  least amount of code, and you don't have to deal with connection setup or strings in code.

    So, using ADO? Yes, I am not  trying to discourage.

    Having nice strong typed parameters in code as opposed to a

    "exec -- build your params string here"

    Is certainly LESS then ideal.

    is not all that great.

    And you CAN use ADO to set a forms data source.

    You load up a ADO reocodset, and then go:

    me.RecordSet = MyADORst

    So, some ADO to call a store  proc would/could look like this:

          
       Dim rs            As New ADODB.Recordset
       Dim cn            As New ADODB.Connection
       Dim cmd           As New ADODB.Command
       
       Dim intPassValue  As Long
       
       intPassValue = 10
       
       cn.ConnectionString = "Provider=SQLOLEDB;Server=.\SQLEXPRESS;Database=TEST3;UID=TEST3;PWD=TEST3"
       cn.Open
       With cmd
          .ActiveConnection = cn
          .CommandType = adCmdStoredProc
          .CommandText = "Times2"
          .Parameters.Append .CreateParameter("@MyValue", adInteger, adParamInput, , intPassValue)
       End With
       
       Set rs = cmd.Execute
    

    You can now set the forms recordset to the ADO result.

    eg:

      Set Me.Recordset = rs

    So, you can load up the ado recordset, and "bind" it, or set it to the form.

    R

    Albert


    • Marked as answer by AnandBhatta Saturday, June 13, 2020 10:00 AM
    Thursday, June 4, 2020 10:24 PM
  • Albert,

    That sure was informative. I can see the difference between a quick and less-than-ideal DAO and a more elegant ADO. Each has its merits.

    Just one more question: I've seen how to set the recordset of a form to a ADODB recordset. Can I do the same to a query? Set the recordset fetched by the "set rs=cmd.execute" as the recordset of an inbuilt query?

    Not sure if this can be done though.

    Friday, June 5, 2020 10:39 AM