Use VBA or Macro to Change Data Type

[Nitescar]

To give a little background I import a table every morning from an excel spreadsheet exported from an Oracle database that contains datetime fields, if the first row of one of these fields is null then it imports to Access as text (The table is deleted first, then readded).

With this I have to stop the automation at this point and then go to design view and change the data types, then run another macro to finish the process.

I have not found a way to automate the data types at all though through any of my books or online.

Thanks for any help;
Rob

 

[pbaldy]

Personally I'd probably empty the table and repopulate it, rather than create it again. In any case, you could use a DDL query (ALTER TABLE...) to change the data type of the field. You can also use DAO, though I don't know the syntax off the top of my head.

 

[Nitescar]

Personally I'd probably empty the table and repopulate it, rather than create it again. In any case, you could use a DDL query (ALTER TABLE...) to change the data type of the field. You can also use DAO, though I don't know the syntax off the top of my head.

That would work as well I will see if I can figure that out, been trying to teach myself access for the last week or so. Thank you much for the help.

 

[mdb4me]

Other ways to avoid this problem would be to link to the spreadsheet rather than import it, so if the columns of the spreadsheet never change and you use the same file name then everytime you open the linked spreedsheet in Access it will display the current data. Then depending on how you want to use the data you could create a query to append or populate to another table so you don't inadvertently change the data in the linked spreadsheet.

Another method would be to save the spreadsheet as a csv or txt file that can be imported, then you can create an import specification in Access specifically for that file and each time you import the file you use that saved spec. which tells Access how to treat each column or field of data.