asked on

Import Into Access

Please could someone give me the code to use the ADO control to import a spreadsheet into an access database table. The field names on the spreadsheet should correpsond to the field names on the table.

When I use the code below each column is going on a different line in the database. The field names are the same as the variable names.

The reason I am not using the import manager is because my ultimate aim is to put 7 spreadsheets into one table, where each spreadsheet has different field names.

Thanks in advance

Alistair

Open "V:\assetman\asset management dot com\extracts\assetut05.csv" For Input As #1

Do Until EOF(1)
Input #1, Code, GEOG, GEOGC, INDC, INDG, INDM, INDX, INDXL, UNAME, SECD, UTYPE, ISIN, ISOCUR, GEOGN, ISINID

Adodc1.Recordset.AddNew "Code", Code
Adodc1.Recordset.AddNew "Geog", GEOG
Adodc1.Recordset.AddNew "Geogc", GEOGC
Adodc1.Recordset.AddNew "Indc", INDC
Adodc1.Recordset.AddNew "Indg", INDG
Adodc1.Recordset.AddNew "Indm", INDM
Adodc1.Recordset.AddNew "Indx", INDX
Adodc1.Recordset.AddNew "Indxl", INDXL
Adodc1.Recordset.AddNew "Name", Name
Adodc1.Recordset.AddNew "Secd", SECD
Adodc1.Recordset.AddNew "Type", UTYPE
Adodc1.Recordset.AddNew "ISIN", ISIN
Adodc1.Recordset.AddNew "IsoCur", ISOCUR
Adodc1.Recordset.AddNew "Geogn", GEOGN
Adodc1.Recordset.AddNew "ISINID", ISINID
Loop
end

ASKER CERTIFIED SOLUTION

Dan_Morris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nitin Sontakke

You are getting the result you have mentioned is because of .AddNew for each column. Although, i have rarely used this technique, as i recollect it is as follows:

adodc1.Recordset.AddNew
adodc1.Recordset.Fields("Code") = Code
all other columns as above.

I am not sure of above. For better way of doing it, fire a INSERT action query on a connection object.

objConn.Execute strQuery

Where, strQuery will be build by you as follows:

strQuery = "INSERT INTO tablename (Code, etc) VALUES ('" & Code & "', ...

I hope you see what i mean...