ado stop fetching data into excel after 100 records

[ac277]

I used ado recordsets to fetch data from an access 2003 queries into excel spreadsheets. The module will automatically open a Excel template and populate the data from Access onto 3 different spreadsheets and then print the workbook directly to adobe pdf file. The program only successfully published approx. 200 workbooks and pdf files and then eventhough the program continues to run without errors, the workbook are all empty after that. I have approximately 2000 records in the recordsets and need to create 2000 English and French version reports. Each workbook has 3 worksheets.
Can anyone suggest what is the problem and is there any solution to fix it?

 

[SkipVought]

Hi,

"...after 100 records"

"The program only successfully published approx. 200 workbooks and pdf files and then..."

I guess a typo?

"I have approximately 2000 records in the recordsets and need to create 2000 English and French version reports."

One record per report, like a mail merge?

"Each workbook has 3 worksheets."

Are you putting data on each of the three sheets?

Maybe a peek at your code might be helpful.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ac277]

Yes, of the 2000 records, only 200 records were published to the worksheet successfully. The program continue to run but only empty worksheets were created.
The program is very long but basically, it just use ado recordset to fetch records from a query and then put data in each record onto an Excel Template line by line.
After it has been successfully published, the code save the workbook and then print to pdf files.

 

[SkipVought]

I guess a nebulous question deserves a nebulous answer...

"Can anyone suggest what is the problem and is there any solution to fix it?"

The problem seems to be your lack of being willing to adequately describe your problem and provide enough specific and relevent information. The solution to such a problem rests firmly in your hands.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[danvlas]

Try
Rst.MoveLast
Rst.MoveFirst

right before exporting the data



Daniel Vlas
Systems Consultant

 

[missinglinq]

The point Skip is trying to male is that no one can help you fix your code if we can't see your code! You wouldn't expect your mechanic to fix your car if he was at his garage and your car was in your driveway, would you?

Post the code you're using!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[ac277]

I have found the solution myself. The problem being that the execution ran too fast and that the previous steps cannot be committed before the next steps kick in. I have tried using a timer to pause the execution for 5 second and use the DoEvents to allow the procedures in the memory to be executed. It works fine now. All the records have been posted.
The reason why I didn't post the codes is that the program is very very long. I could not find the way to attach the program as a word document.