Solved

Repated data import - MS Access

Posted on 2011-03-09
5
319 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:--TripWire--
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 350 total points
ID: 35085784
The code that you reference is just a single line of code. YOu could "call" that code in the Click event of your button, for example, or during some other "event".

As to step-by-step, that would depend on exactly when you need to do this (i.e what Event you would use), but if you're going to use a Button:

1) Add a button to your form (name it "cmImport")
2) In Properties dialog for the button, select the Event tab
3) Locate the "Click" event, and select "[Event Procedure]" in the dropdown next to it
4) Click the build button to the right of that dropdown

This will throw you into the VBA Editor, and Access will build the Stubs for you. It will look like this:

Sub cmImport_Click()

End Sub

Your code would go between the "Sub" and "End Sub" lines:

Sub cmImport_Click()

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

End Sub

You would obviously have to change the Tablename and Filename to match your project.
0
 

Author Comment

by:--TripWire--
ID: 35087102
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?
0
 
LVL 84
ID: 35089119
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.
0
 

Author Comment

by:--TripWire--
ID: 35089151
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?
0
 

Author Comment

by:--TripWire--
ID: 35097339
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
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question