Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2002
    Location
    Cincinnati, USA
    Posts
    4

    Unanswered: Question: Using stored query from VBA code

    I am trying to use a stored query from VBA code within MS Access application. However, I am not able to do so. I can execute a query by the builing the statement in the the VBA code (embedded query). However, not able to use an existing query (stored under [Queries]) and run it from VBA code and get the result in a result set.

    Can someone help. Would appreciate much.

    Thanks,

    Sujit

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    DoCmd.OpenQuery "YourQuery", acViewPreview, acEdit

    or just

    DoCmd.OpenQuery "YourQuery"

    Hope this helps....

  3. #3
    Join Date
    Jan 2002
    Location
    Cincinnati, USA
    Posts
    4
    Thanks. I had tried it. But, it would only display the results on the screen. I want to have the results in a ADODB.ResultSet or something equivalent - so that I can programmatically manipulate the data.

  4. #4
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    'Note that you need a reference to the DAO Library (under tools;references when you are in VBA design)

    Sub ScrollRS()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim myCount As Integer
    Dim rsCount As Integer

    Set db = CurrentDb()
    strSQL = "SELECT * from [Table]"
    Set rs = db.OpenRecordset(strSQL)
    rs.MoveLast
    myCount = rs.RecordCount
    rs.MoveFirst
    For rsCount = 0 To myCount - 1
    Debug.Print rsCount
    Debug.Print rs.Fields(0)
    Debug.Print rs.Fields(1)
    rs.MoveNext
    Next rsCount

    'close recordset
    rs.Close
    Set db = Nothing

    End Sub

  5. #5
    Join Date
    Jan 2002
    Location
    Cincinnati, USA
    Posts
    4
    Thanks. But this is not what I am looking for. My question is probably not clear enough.

    You have BUILT a query in VBA code. However, I want to use an existing query stored under 'Queries' section of the MS Access. This will allow me to use the same query from multiple places - without duplicating it.

    Does that make sense? Will appreciate any inputs on this.

    Thanks again,

    Sujit

  6. #6
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    I did not build a query, this is called a recordset.

    If you want to pull data from a table or query into VBA, this is the way to do it.

    You can replace "strSQL = "SELECT * from [Table]" by
    strSQL = "SELECT * from [YourQuery]" and now you have your query data in VBA. Simple as that.

    The result of my code is a result set that you can scroll through to do whatever. I only added an example that scrolls through the whole recordset and dumps the first two fields in your debug window.

    Hope this helps....

  7. #7
    Join Date
    Jan 2002
    Location
    Cincinnati, USA
    Posts
    4
    It does help. Thanks.

    Coming to think of it, Access treats Table objects and Query objects in the same way. So, 'SELECT * from [MyQuery]' is really intuitive and I should have thought of it.

    Thanks a lot for you help.

    Warm regards,

    Sujit

  8. #8
    Join Date
    Jan 2003
    Posts
    52
    Originally posted by Torgue
    'Note that you need a reference to the DAO Library (under tools;references when you are in VBA design)

    Set rs = db.OpenRecordset(strSQL)

    End Sub
    i m getting problem
    Set rs = db.OpenRecordset(strSQL)
    error is type mismatched, so i put double quotes ard it but then it gave me error that
    The Microsoft Jet database engine cannot find the input table or query <name>. Make sure it exists and that its name is spelled correctly. (Error 3078)

    plz help me

  9. #9
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    I think it might be a problem with the declarations.

    Can you put the decarations in so I can see? ( the rs as recordset, and the db as database and the set db = currentdb())

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and the value of strSQL. izy

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by Torgue
    I did not build a query, this is called a recordset.

    If you want to pull data from a table or query into VBA, this is the way to do it.

    You can replace "strSQL = "SELECT * from [Table]" by
    strSQL = "SELECT * from [YourQuery]" and now you have your query data in VBA. Simple as that.

    The result of my code is a result set that you can scroll through to do whatever. I only added an example that scrolls through the whole recordset and dumps the first two fields in your debug window.

    Hope this helps....
    i'm affraid he was right you did build a query and based the recordset on the query you built all he needs to do is
    recordsetVar = currentdb.openrecordset("QueryName")

    or to rewrite your code:

    Sub ScrollRS()
    Dim rs As DAO.Recordset ' not putting the DAO. in should eork but can do some very odd things
    Dim myCount As Integer
    Dim rsCount As Integer

    Set rs = currentdb.OpenRecordset("tableName")
    rs.MoveLast
    myCount = rs.RecordCount
    rs.MoveFirst
    For rsCount = 0 To myCount - 1
    Debug.Print rsCount
    Debug.Print rs.Fields(0)
    Debug.Print rs.Fields(1)
    rs.MoveNext
    Next rsCount

    'close recordset
    rs.Close
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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