Using recordset from a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JoeA2006 - 12 Mar 2007 22:04 GMT

I am unable to run code to export data from a query to excel using
Set rs = CurrentDb.OpenRecordSet("qryLocations"), _
dbOpenSnapshot

I am using a DAO connection in Access 2000.
My code works fine when I create a SQL string :

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)

I have data from several queries I need to export and do not want to have to
write a sql statement for each one. I would like to be able to just set the
connection to the query object itself if possible.

Reply to this Message

Jason Lepack - 12 Mar 2007 22:12 GMT

You ahve a small bracketing oops...

Set rs = CurrentDb.OpenRecordSet("qryLocations"), _
dbOpenSnapshot

this should be:
Set rs = CurrentDb.OpenRecordSet("qryLocations", _
dbOpenSnapshot)

Cheers,
Jason Lepack

On Mar 12, 5:04 pm, JoeA2006 <JoeA2...@discussions.microsoft.com>
wrote:

> I am unable to run code to export data from a query to excel using
> Set rs = CurrentDb.OpenRecordSet("qryLocations"), _
[quoted text clipped - 9 lines]
> write a sql statement for each one. I would like to be able to just set the
> connection to the query object itself if possible.

Reply to this Message

JoeA2006 - 13 Mar 2007 13:58 GMT

Thanks Jason. Now I need to pass two parameters to the query. I have a form
with txtLocation and txtMonYr

The declaration of the function is:
Public Function ExportDetail(Locn As String, MnYr As String) As String

The click event on the form is:
Private Sub cmdExport_Click()
Call ExportDetail(txtLoc, txtPeriod)
End Sub

Can I still use the OpenRecordSet Method using parameters?

> You ahve a small bracketing oops...
>
[quoted text clipped - 23 lines]
> > write a sql statement for each one. I would like to be able to just set the
> > connection to the query object itself if possible.

Reply to this Message

Jason Lepack - 13 Mar 2007 15:42 GMT

The key is to use a querydef to set the parameters then open the
recordset from that.

I created a query called "Query2" with two parameters "val1" and
"val2" and two fields, "field1" and "field2"

Query2:
SELECT field1, field2
FROM Table1
WHERE field1 Between [val1] And [val2];

' This code demonstrates use of parameters with a querydef
Public Sub testthis()
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset

' reference to current database
Set db = CurrentDb

' load the query to the querydef
Set qdf = db.QueryDefs("Query2")

' set the parameters
qdf.Parameters("val1") = 4
qdf.Parameters("val2") = 8

' open the query in the recordset
Set rs = qdf.OpenRecordset(dbOpenDynaset)

' print the results of the query to the immediate window
Do While Not rs.EOF
Debug.Print rs!field1 & " " & rs!field2
rs.MoveNext
Loop

' clean up
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub

Cheers,
Jason Lepack

On Mar 13, 8:58 am, JoeA2006 <JoeA2...@discussions.microsoft.com>
wrote:

> Thanks Jason. Now I need to pass two parameters to the query. I have a form
> with txtLocation and txtMonYr
[quoted text clipped - 38 lines]
>
> - Show quoted text -

Reply to this Message

JoeA2006 - 13 Mar 2007 16:46 GMT

It seems when I try to use querydef, I get a 3265 error "item not found in
thiscollection". I created the table and the query then I pasted your code
and still got the error.
at qdf.Parameters("val1") = 4

> The key is to use a querydef to set the parameters then open the
> recordset from that.
[quoted text clipped - 85 lines]
> >
> > - Show quoted text -

Reply to this Message

Jason Lepack - 13 Mar 2007 17:03 GMT

Do you have a parameter called "val1" in your query?

Post your SQL.

Cheers,
Jason Lepack

On Mar 13, 11:46 am, JoeA2006 <JoeA2...@discussions.microsoft.com>
wrote:

> It seems when I try to use querydef, I get a 3265 error "item not found in
> thiscollection". I created the table and the query then I pasted your code
[quoted text clipped - 92 lines]
>
> - Show quoted text -

Reply to this Message

JoeA2006 - 13 Mar 2007 17:22 GMT

Sorry, I had a table1 in my database. When I resolved that I no longer got
the error.
Now I have a new error
When it got to
Set rs = qdf.OpenRecordset(dbOpenDynaset)
I got a run time error 13 type mismatch.
Could these be caused but the data types in the table?

> Do you have a parameter called "val1" in your query?
>
[quoted text clipped - 101 lines]
> >
> > - Show quoted text -

Reply to this Message

Jason Lepack - 13 Mar 2007 18:20 GMT

Yes, if you're using strings then you need ' ' and if you're using
dates you need # # around your variables.

Cheers,
Jason Lepack

On Mar 13, 12:22 pm, JoeA2006 <JoeA2...@discussions.microsoft.com>
wrote:

> Sorry, I had a table1 in my database. When I resolved that I no longer got
> the error.
[quoted text clipped - 111 lines]
>
> - Show quoted text -

Reply to this Message