help converting querydef from dao to ado RRS feed

  • Question

  • Hi

    I have is code and need to convert from dao to ado

     Set dbs = CurrentDb()
                    Set qdf = dbs.QueryDefs("Get Selected Growers")
                    qdf.PARAMETERS("[Forms]![Faxes]![Year]") = Forms!Faxes!Year
                    qdf.PARAMETERS("[Forms]![Faxes]![Order]") = Forms!Faxes!Order
                    qdf.PARAMETERS("[Forms]![Faxes]![Suffix]") = Forms!Faxes!Suffix
                    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
                    'Set rst = dbs.OpenRecordset(ctl.RowSource, dbOpenSnapshot)

    the statement in question is

    Set rst = qdf.OpenRecordset(dbOpenSnapshot)

    can anyone help?


    Tuesday, June 20, 2017 9:32 PM

All replies

  • Hi Mike,

    See if this old article helps. Why do you need to use ADO?

    Tuesday, June 20, 2017 9:42 PM
  • Hi Mike A. Lin,

    You could get SQL string from QueryDef.SQL property. If you want to use the SQL string with parameter, I also suggest you use a command object as record sets source, Parameters Collection could help you set parameters. Here is the example.

    Dim QDF As QueryDef

    Set dbs = CurrentDb()

    Set QDF = dbs.QueryDefs("NewQueryDef")

    sqlstr = QDF.SQL

    Dim cmd As New ADODB.Command

    cmd.ActiveConnection = CurrentProject.Connection

    cmd.CommandText = sqlstr

    cmd.Parameters("[Forms]![Faxes]![Year]").Value = Forms!Faxes!Year

    Dim rst As New ADODB.Recordset

    rst.Open cmd

    Best Regards,


    Wednesday, June 21, 2017 10:12 AM
  • Keep in mind that your QueryDefs will be parsed and executed differently under ADO, especially if you are using wildcard characters:

    INFO: Issues Migrating from DAO/Jet to ADO/Jet


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, June 22, 2017 1:52 PM