Use an Excel Macro in Access VBA - Import text file in Access Table

In Access there is a VBA function that does this for you. Look at the DoCmd.TransferText function

Here is a link to understand its functionality.

http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

Originally Posted by alansidman

In Access there is a VBA function that does this for you. Look at the DoCmd.TransferText function

Here is a link to understand its functionality.

http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

I think he is wanting to update data to Access from Excel, for that he might want to go thru ADODB referencing.

There is no need to take the intermediary step. DoCmd.transfertext will import .txt or .csv files directly to a new or existing table. It is clean and quick. As to the formatting of each field, this can be done in Access by setting up Import Specification in the Import Wizard and naming it and saving it. The import Spec can then be used in your VBA DoCmd function.