Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    Durban South Africa
    Posts
    13

    Unanswered: how do u use recordset results in a select query (ADO)?

    I would like to use the results of a select query like so:
    "select * from rssomers where somefield ='" & something & "';"
    rssomers is the recordset.
    Is there a way to do this?
    Disk space, the final frontier...

  2. #2
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    The syntax looks fine to me. If you want to use that recordset in VBA you can use DAO or ADO. I don't know the pros/cons of using either but I find DAO simpler, here's an example of DAO:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDB
    Set rs = db.openrecordset ("your sql code here or a table name", choose open type*)

    * open types are "dbopensnapshot" (read-only) or "dbopendynamic" (editable).

    other code can go here. Example:

    rs.add, rs.update, rs.fields!fieldname, etc.

    Set rs = nothing
    Set db = nothing

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe randycarpet is answering a different question.

    my reading of your question is that you want to run SQL against an existing recordset as in (DAO):
    dim dabs as database
    dim recs1 as recordset
    dim recs2 as recordset
    dim strSQL1 as string
    dim strSQL2 as string
    strSQL1 = "SELECT * FROM aTable WHERE myID < 100;"
    set dabs = currentdb
    set recs1=dabs.openrecordset(strSQL1)
    strSQL = "SELECT * FROM recs1 WHERE myID = 1;"
    set recs2 = whatOnEarthGoesHere.openrecordset(strSQL2)

    ...far as i know, this is not possible (would be nice tho).

    work around is to use the first SQL to make a temporary table then run your second SQL against the temporary table.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Nov 2002
    Location
    Wyoming
    Posts
    48
    I agree with izyrider; the temporary table is probably the best solution.

    It may also be possible to use a For..Next loop to step through the recordset and find what you need. I would need to more about what you are trying to do.
    Cheers,
    Ken

  5. #5
    Join Date
    Dec 2003
    Location
    Durban South Africa
    Posts
    13
    Thanks for that. I did go the temp table root, although I'm sure that you can run a select statement within a select statement. That should also solve the problem. Does anybody have an example of this or even know whether it can be done?
    Disk space, the final frontier...

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    another way: code up a saved query and run the second SQL against the query.

    as in:

    Code:
    Dim qryd As QueryDef
    Dim dabs As Database
    Dim strSQL As String
    strSQL = "SELECT * FROM blah;"
    dabs.QueryDefs.Delete "theName" 'maybe you want to check it exists first!
    Set qryd = dabs.CreateQueryDef("theName")
    qryd.SQL = strSQL
    'then
    strSQL = "SELECT * FROM theName WHERE whatever.....
    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2003
    Location
    Durban South Africa
    Posts
    13
    Thanks again izy, I'll see if I can get that going in ADO. I'll let you know the outcome.
    Disk space, the final frontier...

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    currently using SS 2008R2

Posting Permissions

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