Importing Excel file: ADODB, DAO or DoCmd.TransferSpreadsheet

[Monterey_Manzer]

Hi All,



I am running into some problems when I try to automate a regularly occurring update process via VBA. I will try to spell it out as clearly as I can.

The Situation:
I update my locally hosted Access db from a web based db that contains much of the basic information we use but isn't easily changed or updated to reflect new administrative procedures. Also we don't think we need our admin work out there for all stakeholders to see. Regardless, I regularly export data from this web-based database in the form of Excel spreadsheets and update my records in Access with them. While I have a macro in Excel to handle the formatting changes necessary (everything gets output as text so I used to do a lot of DATEVALUE() conversions), it is still somewhat cumbersome to go through the import wizard so I thought I would make this even more seamless by writing my Excel formatting macro in an Access VBA module and using the DoCmd.TransferSpreadsheet command to import the Excel document into a table. I went a step further and wrote out my Update query in SQL so I could run that with a simple CurrentDb.Execute(strSQL) command. My code then deletes the table that was created using the DoCmd.TransferSpreadsheet command before exiting so I don't wind up with a bunch of out-of-date tables hanging around. All this works pretty darn well and I was feeling pretty pleased with myself until I noticed one problem.

The Problem:
There are a few date fields in my Excel spreadsheet that are mostly empty cells. We don't have data in them for most of our records but where we do, they are important. When I import them using the Access import wizard I have to find these fields and set their data type to Date/Time. It became apparent after running my code that, without this step, the DoCmd.TransferSpreadsheet method will simply set the mostly empty fields to text and when the update query is run it will result in a loss of data. My question is, can I modify the DoCmd.TransferSpreadsheet command such that it allows me to set data types for each field or can I accomplish this through an ADO or DAO type recordset?

I would appreciate any help anyone can offer. Thanks!

[June7]

TransferSpreadsheet does not allow that.

Option might be to set link to the Excel then to use an INSERT SELECT action to append records into your permanent table. The sql statement should be able to explicitely convert the Excel date values from text to date type. Example:

INSERT INTO tablename(field1, field2, field3) SELECT field1, field2, CDate(field2) AS F1 FROM linkedExcel;

[Monterey_Manzer]

Thanks June!

I've been playing around with this a little and I've figured out how to do the formatting basically but I'm running into problems with my SQL statement and the linked Excel table. What is the purpose of the AS F1 part of your SQL statement? Does the linked Excel table have to be referred to as linkedExcel or would the table name specified in the DoCmd.TransferSpreadsheet acImport string be acceptable?

Thanks again.

[Monterey_Manzer]

Never mind, I've got it working.

The current set up does require me to link to an Excel table, append it's records to the permanent Update table using the INSERT INTO SQL statement which includes the formatting changes, then use an UPDATE query to update the data I regularly have to update in the main table from my Update table. I have to loop back around and delete all the records in the Update table and unlink from the Excel table (since it will be a different Excel file each time I run this) but it all happens fairly rapidly so I'm not complaining. Thanks!