Solved

MS Access 2010 - How to Create a Macro to Import External Data

Posted on 2012-03-22
29
7,734 Views
Last Modified: 2016-03-30
I am new to Access 2010, and I'm not a developer or advanced user.  I know how to import external data (Excel, txt files, etc) to create new tables or append data to existing tables.  I would like to make this a 'push-button'/menu type action using a macro.  I have done this in the past in earlier versions of Access, but I can't find an appropriate 2010 macro action to assign to this task.  

Any ideas?    Thanks.   WallyTee
0
Comment
Question by:WallyTee
  • 14
  • 13
  • 2
29 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754583
Is using Macros a *Must*?

Why not just continue to use code and you say you have done before?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754599
In any event, the macro actions have been the same for a while:
TransferSpreadsheet
TransferText
...etc

and also:  OutputTo
0
 

Author Comment

by:WallyTee
ID: 37754649
Thanks boag2000.   I'm sure you're right about the macro actions.   But when I try to create the macro, and I pull down the Add New Actions menu, I see about 60 options, but none of them mention "transfer" or "Import" as previous versions of Access did.  Is it possible they are hidden or disabled somehow, and I have to unhide, activate or enable them?  I have used Transfer/Spreadsheet many times in the past, but I just can't find it in my new 2010 version.

Also, using a macro in this case is not a 'must' per se, I am just trying to make the process a bit easier for the ultimate end user.

Any more ideas?   Thanks again.   WallyTee
0
 
LVL 12

Expert Comment

by:danishani
ID: 37754676
If you don't see all the Macro's, press the button Show All Macro Actions, so you have all the option available to you.

You will see the TransferSpreadsheet, TransferText actions available for use.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754713
danishani,

Yeah, I know that MS tried to "Hide" the potentially "dangerous" commands.

I just thing a listbox with a note in a column to denote "dangerous" commands would have been easier to figure out...

;-)

Jeff
0
 

Author Comment

by:WallyTee
ID: 37755011
To boag2000:  Many tnx again.  

 I found the "button Show All Macro Actions" easily enough and found Import Text (what I want.)  Now I'm trying to actually create the macro, but am having trouble specifying 'source' and 'destination.'   The macro design form doesn't seem to have a "Browse" function to find the source.

Any more ideas.  You have been a big help.    WallyTee.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37755634
<I'm trying to actually create the macro, but am having trouble specifying 'source' and 'destination.'   The macro design form doesn't seem to have a "Browse" function to find the source.>

No automation will let you "browses" to the source.
If you want something like this you will have to, (again), dump macros and use VBA...
...But this is another topic, lets stick to your original question...

The Destination is just the table you want to create, so there is no need to "browse" to anything there...just make a name.

<Any more ideas.>
We need to know what type of import you are doing... Excel or Text...
A textfile requires you to create a "Import Specification"
0
 
LVL 12

Expert Comment

by:danishani
ID: 37755682
Mostly what I do I do a manual import first, so I have the import specification available.
Also you know the Table in where you want to import the data.

Then you can create the Macro using the TransferText action.

You set the required options;
- Specification name
- Table name
- File Name (location where the text file is stored, complete path)
- Has Field names (yes /no, depends on your choices)

With that you pretty much set to run the import via the Macro.

Hope this helps,
Daniel
0
 

Author Comment

by:WallyTee
ID: 37756576
To boag2000 and daniel ---  Many thanks to you both.  I'm getting closer and closer.  Please bear in mind that I'm just barely above a novice user, and I have never done a txt import macro before, although I've done a number of spreadsheet imports triggered by a macro.  

Right now I'm hung up on creating the 'specification.'  I think I'm doing it correctly  (see steps below), but when I try to run the macro, I keep getting an error message that says something like, "The specification (1) does not exist."   And my specification name does not contain a "(1)".  I'm stumped for the moment.  Any ideas?   Thanks again.   WallyTee

Creating Specification:

1.      In the Get External Data dialog box, enter the path of the source text file in the File name box.
2.      Click the option you want for storing the data (import, append, or link), and click OK.
3.      In the Import Text Wizard dialog box, click Advanced.
4.      Specify the options you want for this specification, then click Save As.
5.      Enter the name you want for the specification, then click OK.
6.      You can manage existing specifications by clicking Specs in the specification dialog box.
7.      Click OK to close the specification dialog box.
0
 

Author Comment

by:WallyTee
ID: 37757282
More from WallyTee

 Regarding my specification problem.  I now think the problem is related to running the macro.  I believe I am creating and saving the specification and macro OK, and when just before I run the macro in design view, I see the specification name I created during the 'advanced' import, etc.   Here's what I see:  

ImportExportText
Transfer Type:  Import Delimited
Specification Name: Weblog Test Data Import Specification-a
Table Name:  tbl Weblog-Quote Data
File Name: F:\Logfile-Access Project_Mar-April 2012\Weblog test data 03-22-12.txt
Has Field Names:  Yes
HTML Table Name:
Code Page:  

Howerver, when I try to run the macro (after saving it), I get this error message, "The text file specification ‘3’ does not exist.  You cannot import, export, or link using the specification."  The specification I want is third in the listing of available specifications.

Even though I saved the specification, and it shows up in the macro design as being available, when I run the macro it can't seem to find the specification.  Any ideas.  Thanks again.    WallyTee
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37757398
WallyTee,

Not sure,
Creating the import Spec is tricky, you may have to do it a few times at first, until you get it just right.
Then it is set forever, (until you need to change it)


Again,
Please consider NOT using macros and instead switch to VBA...
There are many reason for this, ...the confusion you are having is only one of them.

With vba it would simply be something like this:
DoCmd.TransferText acImportDelim, "YourSpecificationName", "YourTableName", "C:\YourFolder\YourFile.txt"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37757442
Try this:

Unzip the text file and create a folder:
c:\YourFolder
Then put the text file in it.
Then unzip the DB to wherever you like.
Then open the form and you will see two button...
Then both work fine for me for importing the textfile...
ImportText.zip
0
 

Author Comment

by:WallyTee
ID: 37757582
Hi boag ---- Thanks again.  Re VBA, I've never used it before (I'm not a real programmer or developer) but I don't mind learning new skills.

Also, I'm embarrassed to say, I'm unable to understand your zip/unzip suggestion.  

Right now I'm considering converting the txt file to an Excel spreadsheet and then importing that.  Seems to me it's a lot easier to import a spreadsheets than a text file.  

WallyTee
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37757665
<BA, I've never used it before (I'm not a real programmer or developer) but I don't mind learning new skills.>
I'll summarize...

MS is pushing Macros as the default in Access 2007--2010, because it may be easier for "Users" to have some sort of access to "automation".
...and if you create Web databases, this is your only option.

Unfortunately, macros have some drawbacks:
1. Macros are only used in MS Access, so learning them will not help you in any other program (unlike VBA which you can port to VB6, .net, ...et al)
2. Macros are not as flexible (No recordsets, no references, unorthodox syntax)
Look at all you had to do to post the macro details "Manually"...
In vba, I just copied and pasted...
3. 90% of solutions here will be in VBA, Not Macros.
...etc
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37757669
<Also, I'm embarrassed to say, I'm unable to understand your zip/unzip suggestion.  >
So where did you get stuck...?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37757689
<I'm considering converting the txt file to an Excel spreadsheet and then importing that.  Seems to me it's a lot easier to import a spreadsheets than a text file.  >

If that is an option, fine, ...but this may not always be possible, if you hand this app off to someone else.
If the file is originally text, converting it to Excel just adds another "un-needed" step, ...that may even introduce other issues...
0
 

Author Comment

by:WallyTee
ID: 37757861
Hi again Boag ---- Re zip/unzip.   Bear with me.  I'm just barely above a novice.  

Here's where I got stuck.  I'll put my remarks in bold font:

Unzip the text file and create a folder:  THE FILE I'M WORKING WITH IS NOT ZIPPED.  DO YOU MEAN CREATE A FOLDER FIRST AND THEN SAVE THE .TXT FILE IN THAT FOLDER?

c:\YourFolder

Then put the text file in it.  SEE ABOVE.

Then unzip the DB to wherever you like.  HOW DO YOU UNZIP A DB?   DO YOU JUST MEAN "OPEN" IT?  ALSO, I'M NOT SURE OF THE MEANING OF "WHEREVER YOU LIKE."

Then open the form and you will see two button...  I HAVE NOT CREATED ANY FORMS YET.

Then both work fine for me for importing the textfile...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37758142
<Unzip the text file and create a folder:>
Meaning unzip the file I attached to the post:
http://filedb.experts-exchange.com/incoming/2012/03_w12/562427/ImportText.zip
Insde this file there are two files, the text file and the Database file.
0
 

Author Comment

by:WallyTee
ID: 37760394
Hi again boag,

MY GOOF!  I failed to spot the attachment.  I'm going to give it a try now.

BTW, I've decided to take your advice and try to achieve the objective with VBA --- even though I don't know VBA --- I'm willing to learn.  The objective itself is really very simple. I need to import two separate text files into two separate and corresponding Access tables on a daily basis.  Before starting the daily operation, we will populate the two tables with six months of historical data.  The objective is to append the fresh daily data from the text files to the Access tables with some type of simple 'push button' operation on a form (menu??).  

This will make the data available for analysis via pre-designed or ad hoc queries.  

Any ideas or help you can offer are greatly appreciated, but I don't want to take up too much of your time.  Thanks again.   WallyTee
0
 

Author Comment

by:WallyTee
ID: 37760435
Hi boag -- I was able to open your DB and txt file with no problem.  I looked at all objects and I'm beginning to see some hope.  LOL.  

One small glitch ---- I did not see any buttons on Form1.  I only see a small pull down menu; am I doing something wrong?

BTW, my macro design is exactly the same as yours; it just doesn't work.

I assume the VBA code is in the 'module' ---  is that correct?  And does the button on the form activate the VBA code which then imports the txt file data into the table?  

If I'm correct, would the import action over-write the table or just append the txt file data to it?  I need to do an 'append.'

Any more thoughts?  

Thanks again.   WallyTee
0
 

Author Comment

by:WallyTee
ID: 37760690
Hi again Boag -----  I'm getting there (I think). I used your module example and created the module shown below.  It looks like yours, but i can't get it to run and do its job (I double-clicked and single-clicked on it in the Navigation Pane). I checked all spelling very carefully.  -------   Any ideas?  Are the quote marks required?   Thanks again for all your help.   Wally Tee
***************************************************************
Option Compare Database

Private Sub Command12_Click()
DoCmd.TransferText acImportDelim, "Weblog test data Import Specification-a", "tbl Weblog-Quote Data", "F:\Logfile-Access Project_Mar-April 2012\Weblog test data 03-22-12.txt"
End Sub
Private Sub Form_Current()
End Sub

*************************************************************
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37760803
<double-clicked and single-clicked on it in the Navigation Pane>
"it"?
What is "It"?

Anytime you create "code":

DoCmd.TransferText acImportDelim, "Weblog test data Import Specification-a", "tbl Weblog-Quote Data", "F:\Logfile-Access Project_Mar-April 2012\Weblog test data 03-22-12.txt"


...You have to put it on an "Event"
The event is when you want it to run.

For example, if you want to make this code run when you click on a button on a form then:
Create the form
Drop a button on the form
(When the button wizard starts up, cancel out of it)
Right-click the button and select: Build Event
Select: Code Builder
Then paste the code in.
Save the form
Open the form in Form View
Click the button
0
 

Author Comment

by:WallyTee
ID: 37760875
Hi again Boag!   Man, do I appreciate your help!   The "it" I referred to was simply the name of the module as listed under MODULES in the Navigation Pane.  I thought you could run a VBA module just by clicking on its name, the same way you open a table.

I'll work on your fix right now.  BTW, did my code look OK to you?

Tnx again.   WallyTee
0
 

Accepted Solution

by:
WallyTee earned 0 total points
ID: 37760886
It worked !!!   Terrific !!!     Now, can you tell how to APPEND new data to the table at a later time, e.g., one day later?  The source would be the same txt file but with overwritten data. In other words, the table would grow (more records) every day --- always retaining the earlier (historical) data.   Tnx again.   WallyTee
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37760936
You really cannot specify an "Append" in the vba code or in the import spec.

What is typically done is that the table is imported, then code is used to "append" the data
something like this:

CurrentDB.execute "INSERT INTO Table1 ( Fld1, fld2) SELECT Table2.fld1, Table2.fld2 FROM Table2;", dbfailonerror
...But this will depend on things like an autonumber PK, and the field datatypes, ...etc, ....so this is better dealt with in a separate, new question.

JeffCoachman
0
 

Author Comment

by:WallyTee
ID: 37761136
Hi Jeff ("boag2000??")---- I'm pretty sure I got it to work already.  Here's what I did, starting from scratch.

1.  I imported the text file to create the first set of data in the table.
2.  I over-wrote the data in the text file with new data for the update test.
3.  Using the Import Wizard, I did a regular 'import delimited text file' process; in doing so I chose the "append" rather than the "new table" option.
4. I clicked on 'advance' and created a new import specification.
5. I created a new button on my form and created an event using the VBA code 'template' you sent me; I used the new import specification in the code.
6. I then opened the table and saw the new data appended to the old data.

Do you see anything wrong with this logic?  Thanks again.

Wally
0
 

Author Comment

by:WallyTee
ID: 37776025
From WallyTee --- I finally found a solution on my own.  Instead of using the macro action titled "ImportExportText,' I did the following:

1.  Used the standard Import External Data wizard
2.  While still in the wizard, I saved and named the "Import Process."
3.  Created a macro using the "RunSavedImport" action
4.  Entered the name of my saved "Import Process."
5.  Saved and named the macro.
6.  Ran the macro and it worked.  

I then set up and tested a 'Scheduled Task' to run the macro at a certain time.  When the time came, the macro automatically imported the data correctly.  

As a 'low intermediate' user, I sure learned a lot.    Thanks to all the experts who contributed.  WallyTee
0
 

Author Closing Comment

by:WallyTee
ID: 37780897
boag2000 was a super help.  Very responsive, very patient (I'm just a novice-plus).  He walked me through a new (for me) process, and introduced me to VBA, which I will now pursue.  His solution worked perfectly.  Thanks to him and to the site.   WallyTee
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41529027
Glad I could help.
;-)

Jeff
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now