Q: How to export from a querydef?

  • Thread starter Thread starter MarkD
  • Start date Start date
M

MarkD

Hi,

I'm using Access 2000.

I have a query that is filtered off data from a form.
Because of this, I can't export the query itself (even if
the form is open). So, I have code that puts in the
parameters:

======================
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Set qdf = db.QueryDefs("CL EXPORT")
qdf.Parameters(0) = Forms![COMPANY_ENTRY]![CLIENT_NUMBER]
qdf.Parameters(1) = Forms![COMPANY_ENTRY]![COMPANY_ID]
Set rs = qdf.OpenRecordset()
======================

this all works, but what I need to do is export rs to a
comma delimited (no qualifiers) file. I have the export
spec ready and I thought docmd.transfertext would work,
but it doesn't.

So, how would I accomplish this?

THANKS!
-Mark
 
What you will need is to put the data from the query's recordset into a
table, and then export that table.

Easiest way to do this is to have a table in your database that will receive
the data, create an append query from your current query and save this
append query, then execute the querydef after you give it the parameters,
then export the table, then run a delete query to remove the data from that
table.
 
Thanks for your reply Ken.

Unfortunately, I can't do that. These files will be
created multiples times a day by simultaneous users. I'd
rather not create/delete that many objects as I think it
increases the chance of Access corrupting.

What I've decided to do is run the query in code (it will
always only return one record), then step through the
colums writing to a string and adding the commas. Then
I'll create the export file using FileSystemObject and
write the string to that file.

-M
-----Original Message-----
What you will need is to put the data from the query's recordset into a
table, and then export that table.

Easiest way to do this is to have a table in your database that will receive
the data, create an append query from your current query and save this
append query, then execute the querydef after you give it the parameters,
then export the table, then run a delete query to remove the data from that
table.
--

Ken Snell
<MS ACCESS MVP>



Hi,

I'm using Access 2000.

I have a query that is filtered off data from a form.
Because of this, I can't export the query itself (even if
the form is open). So, I have code that puts in the
parameters:

======================
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Set qdf = db.QueryDefs("CL EXPORT")
qdf.Parameters(0) = Forms![COMPANY_ENTRY]! [CLIENT_NUMBER]
qdf.Parameters(1) = Forms![COMPANY_ENTRY]![COMPANY_ID]
Set rs = qdf.OpenRecordset()
======================

this all works, but what I need to do is export rs to a
comma delimited (no qualifiers) file. I have the export
spec ready and I thought docmd.transfertext would work,
but it doesn't.

So, how would I accomplish this?

THANKS!
-Mark
Click to expand...


.
Click to expand...
 
That will work, too.

--

Ken Snell
<MS ACCESS MVP>

MarkD said:
Thanks for your reply Ken.

Unfortunately, I can't do that. These files will be
created multiples times a day by simultaneous users. I'd
rather not create/delete that many objects as I think it
increases the chance of Access corrupting.

What I've decided to do is run the query in code (it will
always only return one record), then step through the
colums writing to a string and adding the commas. Then
I'll create the export file using FileSystemObject and
write the string to that file.

-M
-----Original Message-----
What you will need is to put the data from the query's recordset into a
table, and then export that table.

Easiest way to do this is to have a table in your database that will receive
the data, create an append query from your current query and save this
append query, then execute the querydef after you give it the parameters,
then export the table, then run a delete query to remove the data from that
table.
--

Ken Snell
<MS ACCESS MVP>



Hi,

I'm using Access 2000.

I have a query that is filtered off data from a form.
Because of this, I can't export the query itself (even if
the form is open). So, I have code that puts in the
parameters:

======================
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Set qdf = db.QueryDefs("CL EXPORT")
qdf.Parameters(0) = Forms![COMPANY_ENTRY]! [CLIENT_NUMBER]
qdf.Parameters(1) = Forms![COMPANY_ENTRY]![COMPANY_ID]
Set rs = qdf.OpenRecordset()
======================

this all works, but what I need to do is export rs to a
comma delimited (no qualifiers) file. I have the export
spec ready and I thought docmd.transfertext would work,
but it doesn't.

So, how would I accomplish this?

THANKS!
-Mark
Click to expand...


.
Click to expand...
Click to expand...
 
You need to use code to modify the *SQL String* of the Query so that it
becomes a NON-parametrised Query.

What I normally do is to create 2 Queries. Let name them:

qselCLEXPORT_Base
qselCLEXPORT_Custom

in qselEXPORT_Base, you simply have the start of the SQL String yo need for
export excluding the parameters. Let say you have something like:

SELECT Field1, Field2, ....
FROM YourTable

The SQL String ofr qselCLEXPORT_Custom is not important since you will
modify it just before exporting every using your code.


In your code, you modify the SQL String of the qselCLEXPORT_Custom like:
****Untested****
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb

Set qdf = db.QueryDefs("qselCLEXPORT_Custom")
qdf.SQL=QueryDefs("qselCLEXPORT_Base").SQL & _
" WHERE [ClientNo] = " & Forms![COMPANY_ENTRY]![CLIENT_NUMBER] & _
" AND [CompanyID] = " & Forms![COMPANY_ENTRY]![COMPANY_ID]
qdf.Close
DoEvents

DoCmd.TransferText ... "qselCLEXPORT_Custom"

Set qdf = Nothing
Set db = Nothing
********

Thus every time, you actually modify the SQL String of the
qselCLEXPORT_Custom to use a custom SQL String exactly matching your
requirements *without* Parameters and you can use this for for the
TransferText statement.
 
Back
Top