Import from csv to ms access tables using macro

Question

Can some provide me a VBA macro to import data from a CSV file into ms access tables ?

Answer 1

See if Docmd.TransferSpreadSheet helps.

Answer 2

INSERT INTO tblSomeTable ( [TRADE DATE], REP, REPID, [ACCOUNT/POLICY],
                          CUSTOMER, [REP# COMPANY], [PRODUCT NAME], QUANTITY, [FACE AMOUNT],
                          [GROSS COMMISSION], [CUSTOMER SSN] )
SELECT [TRADE DATE], REP, REPID, [ACCOUNT/POLICY],
        CUSTOMER, [REP# COMPANY], [PRODUCT NAME], QUANTITY, [FACE AMOUNT],
        [GROSS COMMISSION], [CUSTOMER SSN]
FROM [Text;
      FMT=Delimited;
      HDR=YES;
      CharacterSet=437;
      DATABASE=C:\Documents And Settings\spauldb\Desktop].Transactions_Jan1_to_Jan30.csv
WHERE [TRADE DATE] Is Not Null;

Answer 3

I would use TransferText for CSV:

DoCmd.TransferText acImportDelim, , "NewTable2", "c:\docs\ltd.csv", True

It is often a good idea, but not always necessary, to use an import specification or to import to an existing table when working with text files.