TransferText macro to export query to csv makes access hang!

[ollyb303]

Hi,

I am having a bit of a problem with TransferText macro.

I am using TransferText, Export Delimited (no field names) to export the results of a query as a .csv file.

The query is based on a linked table and uses a CDate([Nom_Date]) expression to convert the timestamp field (Nom_Date) from the table to a date type and the criteria:
Between (Now()-183) And Now() to narrow results to the last 6 months.

The query works just fine on its own and returns the expected results.

The macro, if run using the underlying table rather than the query, also works fine - it does take around 40 minutes (hence trying to trim the data down to the last 6 months!) but it does work.

When I run the macro using the query, it just hangs. Access stops responding, the status bar shows "Exporting" but never gets beyond 1 bar and task manager shows Access as not responding.
In fact it freezes my whole machine up, sometimes even stops updating the system time in the systray.

Access 2002 running on XP.

Does anyone have any idea why this might be.

Any help gratefully received.

Cheers,

Olly

[FishVal]

Hello, Olly.

Do you try to export a huge amount of records?
If so, then have you tried to determine a maximum amount you could export without freezing computer?
It could be a good idea to export data in chunks and concatenate output files later.

Regards,
Fish

[ollyb303]

Thanks for replying Fish,

Yes, it is a lot of records, but when I am exporting ALL the data from the underlying linked table using the same macro (well, same apart from the table name), it does it. The .csv file generated is a big one (around 250Mb).

The query inludes only data from the last 6 months whereas the table includes data from the last 8 years, so I expect the number of records in the query to be (very roughly) 1/16 of the number in the table.

I wonder why my machine is happy to export 8 years of data, but refuses to export 6 months worth?

[ollyb303]

I have just had another thought with regard to this:

I am using the same method to export data from a different linked table in the same database; that is I am using a query which has the same:
Between (Now()-183) And Now() criteria for the date to narrow to the last 6 months.
The only difference is that with this query I am NOT using the CDate expression as the date in the table is in the correct format.
This table is actually bigger than the one I am having problems with, yet the macro runs no problem.

Could it be related to the CDate expression? I'm stumped TBH.

Thanks,

Olly

[PianoMan64]

I have just had another thought with regard to this:

I am using the same method to export data from a different linked table in the same database; that is I am using a query which has the same:
Between (Now()-183) And Now() criteria for the date to narrow to the last 6 months.
The only difference is that with this query I am NOT using the CDate expression as the date in the table is in the correct format.
This table is actually bigger than the one I am having problems with, yet the macro runs no problem.

Could it be related to the CDate expression? I'm stumped TBH.

Thanks,

Olly

Hello Olly,

One of the obvious problems that I'm seeing with your query is that with using the Now() function call as your set of criteria, your query is being updated (changed) every second. This in itself, would cause not only a lot of resources to be used, but will introduce unreliable results because one second, you may have one record that you thought should be included, and the next you won't. Not sure that using the Now() function is such a good idea. you may want to replace it with the Date Function, assuming that there isn't any time values that are associated with your date ranges?

as for the reason for the difference in performance issue, between exporting all the data, and exporting six months goes back to my previous explanation. Your query is most likely being updated every second, and it is causing your computer to really work at excatly what 6 months you want to export, since that changes every second.

Try changing the Now() function call with the Date() function call and see if you notice a change.

Let me know,

Joe P.

[ollyb303]

One of the obvious problems that I'm seeing with your query is that with using the Now() function call as your set of criteria, your query is being updated (changed) every second. This in itself, would cause not only a lot of resources to be used, but will introduce unreliable results because one second, you may have one record that you thought should be included, and the next you won't. Not sure that using the Now() function is such a good idea. you may want to replace it with the Date Function, assuming that there isn't any time values that are associated with your date ranges?

as for the reason for the difference in performance issue, between exporting all the data, and exporting six months goes back to my previous explanation. Your query is most likely being updated every second, and it is causing your computer to really work at excatly what 6 months you want to export, since that changes every second.

Try changing the Now() function call with the Date() function call and see if you notice a change.

As it happens, the Nom_Date field does include a time value as well, though this is not crucial to the query so I tried using the Date() function call instead and TBH I noticed absolutely no difference. The other macro/query I mentioned previously also includes a time value and I am using the Now() function call succesfully there, which compounds my belief that it may be related to the CDate expression.

Thanks for the reply,

Olly

[FishVal]

Hello, all.

@PianoMan64.
Though your guess is brilliant and I really like it, it is incorrect.
Access optimizes query execution by calculating expressions not dependant on record fields values only once.

@Olly.

It is really hard to find out a problem from what you've posted.

• Post please more information.
  • Full SQL of the problematic query.
  • Metadata od table the query build on.
  • Code of export procedure
• Try to localize error source by:
  • Applying different criteria in query.
  • Sequential removing of query fields.

Regards,
Fish

[ollyb303]

Fixed it.

Removed the CDate([Nom_Date]), replaced with just Nom_Date and used the criteria Between CDbl(Now()-183) And CDbl(Now())

Thanks for your help.