Avatar of --TripWire--
--TripWire--
 asked on

Repated data import - MS Access

Hello,

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", _
      hasfieldnames:=False

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
--TripWire--

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
--TripWire--

ASKER
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.
--TripWire--

ASKER
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
--TripWire--

ASKER
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