Access 2007 - Upgrade Problem- TransferSpreadSheet

[mmogul]

I have code that was developed in Access 2002 that was working fine. But I have problems when I load it into Access 2007.

This application loads numerous excel spreadsheets into an access database and then creates a series of reports. I have two issues:

1) When I try to read an excel file, I get a message that says: Microsoft Office Excel recalculates formulas when opening files last saved by an earlier version of Excel. Cancel / Try Again / Continue

How do I avoid this message? I do not have control over the version of excel files that are submitted by outside vendors for this application.

2) When I try to actually upload the spreadsheet using the TransferSpreadsheet command, I get the follwoing error:

Run-Time Error 3349:

You cannot record your changes because a value you entered violates the settings defined for the table or list (for example, a value is less than the minimum or greater than the maximum). Correct the error and try again.

It is failing on this command. (I have also tried it with an '8' in the second parameter).

DoCmd.TransferSpreadsheet acImport, 9, "tblTempMfgProductData", strFileName, False, strWorkSheetName

(where strFileName is the name of the file and strWorkSheetName is the name of the worksheet)

Look forward to your ideas.

Thanks.

 

[mmogul]

Additional Note: As I was looking around Access 2007, I noted that when I looked at the dropdown control for Macros, TransferSpreadsheet was not listed. Looking in Help, I found the following note:

"This action will not be allowed if the database is not trusted. For more information about enabling macros, see the links in the See Also section of this article."

But when I looked at the See Also section, I could not figure out how to resolve the problem ... if caused by some kind of security setup.

 

[mmogul]

More notes: I found the TransferSpreadsheet method (it was hidden). I ran a test transfer and received the same error with an error code of 2950. This indicates that the database is not "trusted" (I think) -- but I have it in a trusted zone.

????

 

[mmogul]

OK - I believe I found out why this is happening. First I increased the field size of all the fields (all text) to 255. Problem still occurred.

Then I discovered that Microsoft thinks they can determine whether I am importing a text or numeric field based on the first record. In one column of the excel sheet, the first record had numeric data. Therefore, in row 12 where data was text, they expected to see more numeric data and the import fails. I confirmed this by adding text in row 1 of the problem column. Now the import works. So Microsoft Access thinks I am trying to import text data into a numeric field.

This problem did not exist in 2002 and 2003 access.

I still need to create a workaround. I think this will work as a csv import. I am importing about 100 spreadsheets so I need to programmatically save these sheets as csv before I begin my import process.

Is there an easy way to do that? Does anyone have a better approach?