TransferSpreadsheet Not working consistently

[mmogul]

(This is a restatement of thread 705-1511265 which has evolved and I thought should be clarified).

I have a problem with the TransferSpreadSheet action. I am using this function to import a spreadsheet to a simple access table (10 fields, each defined as text). After importing, I process data in the text file, clear the file and then import another spreadsheet. Up to approx 100 spreadsheets can be processed.

The problem is that the sometimes the imported spreadsheet is not imported into the table row by row from the beginning of the spreadsheet to the end. It appears that the import can begin in the middle of the spreadsheet, it then goes to the end, then starts at the beginning and ends where it started.

Example: Let's say the spreadsheet had only one column and 6 rows with the following content:

A
B
C
D
E
F

The access table should show the data in this same order. But occasionally, it shows something like:

C
D
E
F
A
B

This problem is not always repeatable. I can import the spreadsheet a second time and then the data will be in the correct order.

Has anyone ever seen this issue and know how to respond to it?

My code is:

        DoCmd.TransferSpreadsheet acImport, 8, "tblTempMfgProductData", strFileName, False, strWorksheetName

where strFileName is the excel file name and strWorkSheetName is the worksheet name within the file.

ex: strFileName = ALB123.xls
strWorkSheetName = ALB123!A1:F47
Thanks.

 

[PHV]

Is tblTempMfgProductData created each time ?
I suggest to not delete the records but recreate the table.

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

 

[AHJ1]

May I suggest that you save the file as a comma separated variable.

This allows you to import the data and create an Import Specification. The import spec lets you define the types of data you are importing. Importing certain data as text, double, etc. may be helpful.

Good luck.

 

[mmogul]

Thanks for your input. I cannot use AHJ's suggestion, because the input is variable and I don't necessarily know whether the data is going to be numeric or text. That is why I am bringing the data first into a text table.

I will try creating the tblTempMfgProductData each time - rather than deleting the records and see if that helps.

 

[AHJ1]

I could be wrong, but it seems to me as if you are sorting numbers in text order sometimes. 01 sorts differently than 1 in text.

I would try using an import spec, with everything going to a table that is all text fields, and then coercing the fields that contain numbers to numbers. (The easiest way to do this is to copy the table, change the field types, save the copied table and then compare the two.)

This may not work, but it's probably a 15-minute effort.

Regards,
Alan

 

[SkipVought]

"The access table should show the data in this same order"

Really?

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

The basic idea behind the relational model is that a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables.

I'd be concerned about MISSING data but not the order, necessarily.




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AHJ1]

Skip, That's quite true.

mMogul, why not sort the data, and examine it. Perhaps we really don't have a problem.

Alan