Creating Query Programatically

[Madhuri]

Hi all

I have a button on my msaccess form which ports data into excel but I want
excel to port only a particular set of fields and only the record which has
been selected when the excel button is pressed. for that I am trying to build
a query in my code which looks like this

CurrentDb.CreateQueryDef "xlqry", "select * from qkpwc_recoup where
rec_isn='" & Me.REC_ISN & "'"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "xlqry",
"C:\FileNameIs.xls", True

But the first statement dosent seem to be creating a query when I execute
the command. How do I create a dynamic query stored in access database to
enable the above functioning.

Thanks in advance
Madhuri

 

[Madhuri]

I repeatedly executed and refreshed the pane and found that the query was
built and stored in msaccess but now my problem is how to delete it
programatically so that when I recreate the same query it dosent give me a
message of object already exists.

Thanks
Madhuri

 

[Dirk Goldgar]

I repeatedly executed and refreshed the pane and found that the query
was built and stored in msaccess but now my problem is how to delete
it programatically so that when I recreate the same query it dosent
give me a message of object already exists.

You'd be better off leaving the query in place, but just changing its
SQL property each time:

CurrentDb.QueryDefs("xlqry").SQL = _
"select * from qkpwc_recoup where rec_isn='" & _
Me.REC_ISN & "'"

DoCmd.TransferSpreadsheet ...

 

[Madhuri]

Thank you That was great....

You'd be better off leaving the query in place, but just changing its
SQL property each time:

CurrentDb.QueryDefs("xlqry").SQL = _
"select * from qkpwc_recoup where rec_isn='" & _
Me.REC_ISN & "'"

DoCmd.TransferSpreadsheet ...


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)