Non repeatable errors

[mmogul]

This one is a bit difficult to describe so pls be patient.

I have a program that reads up to approx 100 spreadsheets into an access database. The system first loads the spreadsheet into a generic table that mimics the spreadsheet (same columns and rows). Then it uses another table that defines the rules for the spreadsheet -- that is, it identifies where certain data is found on the spreadsheet, so it can be loaded to the appropriate table in the database.

The problem I am having is I get errors such as "data mismatch". But these errors are not repeatable. If I load the same exact data a second time, the same error does not occur. Or it may reoccur, but not in the same place. When I work in debug mode, stepping through the code, I don't get any errors. This makes me think the problem has something to do with the speed of the processor, or some kind of memory overload.

Could the code be somehow getting ahead of itself? Has anyone ever seen this problem? Any suggestions how to handle it?

 

[PHV]

Which code ?
Which data ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

Try adding DOEVENTS into the loop to see if that helps....

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[mmogul]

Thanks for your comments. I believe I have isolated the problem to the TransferSpreadSheet action. I am using this to import the spreadsheet to a simple text table (10 fields, each defined as text).

What should happend is row 1 of the spreadsheet gets imported into row 1 of the access table. Row 2 of the spreadsheet into row 2 of the table, etc. When a problem occurs which appears to be random, the import starts at the middle of the spreadsheet and which is then followed by the beginning of the spreadsheet.

Example: Spreadsheet Row 20-49 are imported into table row 1-30. Rows 1-19 go to table rows 31-49.

Before I begin the transferspreadsheet, I clear the target query with a delete query.

So this appears to be some kind of problem with TransferSpreadsheet. When this import problem occurs, I have certain data in unexpected places, which causes the errors (Data Mismatch) which I reported.

Anyone have any thoughts?

 

[PHV]

Sorry, but a table have no row number.

 

[mmogul]

PHV - I probably did not explain clearly. I am not looking for row numbers. I just want the spreadsheet to be imported into the table in the same order that it is on the spreadsheet.

Here's an example of what happens:

Let say the spreadsheet has 8 rows and just 1 column with the following data:


A
B
C
D
E
F
G
H

When I import the data into my access table, sometimes I get something like:

F
G
H
A
B
C
D
E

Hope this explains the problem more clearly.

 

[mmogul]

This problem is occuring in Access 2002 and 2003 - in case that is relevant.

 

[PHV]

Again, a table have no peculiar row order.

 

[mmogul]

I assume by "peculiar", you meant "particular". My experience is to the contrary when it comes to using the TransferSpreadSheet method. The data is normally imported in the same order as it appears on the spreadsheet.