Avatar of --TripWire--
 asked on

Repated data import - MS Access


I previously asked a question about importing data from a csv to MS Access, found here:  http://www.experts-exchange.com/Database/Miscellaneous/Q_26781566.html

During this thread, someone asked me if this would be a one time thing, or repeated.
To which I answered, one time - but I've found out since then that it will be a repeated action after all.

Someone suggested using: DoCmd.TransferText transfertype:=acImportDelim, _
      tablename:="tblMonthlyData", _
      FileName:="D:\Documents\Examples\Monthly Data.csv", _

But I would like a step-by-step procedure as to how to do this if possible.
(Screenshots would help please)

Thank you
DatabasesMicrosoft AccessSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon
Scott McDaniel (EE MVE )

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Ok thanks, I want to try that out.  But I only want to import certain data.

For example.  Lets say my Access table has 15 fields, but I only want to import 5 fields from my csv.  How would I go about this?
Scott McDaniel (EE MVE )

You can't do so easily, but you can import to a Temporary table, and then use Access methods to move the data from your 5 fields to your "live" Access data. To do that, you'd run code like this AFTER your call to TransferText:

Currentdb.Execute "INSERT INTO YourLiveTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM YourTemporaryTable"

Obviously you'd need to change the Table and Column names to match your project.

Ok Thanks.  Is that run in a Macro as well?
Will the auto-number PK I have in my table populate by itself when I insert this data?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Thanks for your help.  So far what you've suggested has worked.
Only, I've noticed the newly imported values have appended "3775" to the front of my auto-number field.

For example, my records will auto-number (incrementing by one each time) and then suddenly they'll change when it gets to the new values.

2065, 2068, 37752069, 37752070, 37752071...

Please help