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!