Memory Problem?

[CraigS]

We are experiencing a vexing problem. We suspect the underlying cause is that 1 GB of memory is not enough! We are running Microsoft Access 2002 and Excel 2002 on Microsoft Windows 2000 Professional.

We have a program that imports data from nearly 2000 spreadsheets. The program locates the first spreadsheet and imports a range into one Access table, then imports a different range into another table and then goes on to the next spreadsheet. The importing uses DoCmd.TransferSpreadsheet acImport.

The program works just fine if we have just a few files, but when we run it on all 2000, or even 100, problems develop. The Access program literally writes erroneous data into the tables. It always rights the correct number of records based on the parameters, but some of the data is erroneous.

By erroneous data, we mean that sometimes duplicate entries (which are ZIP Codes) are written to the table taking the place of other meaningful ZIP Codes. Sometimes a single ZIP Code gets written four times, meaning that three legitimate ZIP Codes never get imported. In other instances, purely erroneous ZIP Codes get added, which means that an equal number of legitimate ZIP Codes do not get imported. Interestingly, the erroneous ZIP Codes are similar in number to the correct ones – they usually differ only slightly from the correct ZIP Codes that are supposed to be imported. The extra weird thing about this is that the error often happens within the importation of a spreadsheet, as opposed to starting when beginning the importation of a spreadsheets.

Trying to solve the problem, we ran the program on only the first 100 files, and it still created the erroneous ZIP Codes. We ran the program 10 times to compare all 10 results, and got interesting results. Sometimes the programs produced nearly identical (but erroneous) results, sometimes it didn’t. In all cases, the first few files were processed correctly, but the program eventually went awry. Sometimes it started working again for a while, and then went awry again.

Any thoughts?

 

[bat17]

I have not tried anything on that scale! The only suggestion is to try copying all of the files to a local drive incase it is choking on the network.

Peter

 

[CraigS]

The data are all on a local drive, not a network drive, so that doesn't appear to be the issue. We guessed that the problem was that the processor was getting ahead of the machine's ability to write data to the hard drive. We "solved" the problem by importing a spreadsheet to a temporary table, and then reading the values in the temporary srpeadsheet into the main file, record by record. We then moved onto the next spreadsheet. This process takes much longer, but seems to write the correct data to the table.

We have "solved" our immediate problem, but clearly we do not understand the true cause, or the proper method of fixing it. If anyone has a good guess as to the culprit, and can point us to a website or a book that discusses the problem/solution, we would be much obliged.

Thanks!