Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    open recordset (Access 2000)

    How can i create a recordset instead of a query and view this? I have tried to build the function but since i am not acqainted with
    VB perhaps i do not some fundamentals. I want to build an sql and view it in the same way as i open a query and view it.My function does not do that:


    Public Function Fwrd()
    Dim sql As String
    sql = " SELECT customers.Customerid, orders.OrderID, orders.orderdate " & _
    " FROM customers INNER JOIN orders ON customers.Customerid = orders.customerid"
    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql)
    End Function





    Public Function Fwrd()
    Dim sql As String
    sql = " SELECT customers.Customerid, orders.OrderID, orders.orderdate " & _
    " FROM customers INNER JOIN orders ON customers.Customerid = orders.customerid"
    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql)
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: open recordset (Access 2000)

    You can't "view" recordsets; they are objects you manipulate in VBA.

    You can create a stored query based on the SQL string and then open it as any other saved query:

    Dim strSQL As String
    strSQL = "SELECT ..."
    CurrentDb.CreateQueryDef "qryMyQuery", strSQL
    DoCmd.OpenQuery "qryMyQuery"

    But if you are building this database for others to use, it is not a good idea to open queries. It's much better to open a form. You can manipulate the RecordSource of a form in code if necessary - the RecordSource can be the name of a table, the name of a stored query, or a SQL string.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: open recordset (Access 2000)

    Has this changed in XP?
    I have a book about XP that includes an example where it has a form with no recordsource, and in its onload event it creates an ADODB recordset (rst) , then uses the line:
    Set me.recordset = rst
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: open recordset (Access 2000)

    The hitch here is that recordsets in DAO and recordsets in ADO are rather different things. If you are using ADO, the code you referenced is essentially creating a form that is as close as you can come to a form bound to an ADO recordset. This can be further compounded if you are using an ADP project instead of a MDB (jet) database. Most Access developers are still working with the bound form and MDB files because they usually involve less development and are well understood.
    Wendell

Posting Permissions

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