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

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Is using Macros a *Must*?

Why not just continue to use code and you say you have done before?
Jeffrey CoachmanMIS LiasonCommented:
In any event, the macro actions have been the same for a while:

and also:  OutputTo
WallyTeeAuthor Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
Jeffrey CoachmanMIS LiasonCommented:

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


WallyTeeAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
<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"
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,
WallyTeeAuthor Commented:
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.
WallyTeeAuthor Commented:
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:  

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
Jeffrey CoachmanMIS LiasonCommented:

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)

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"
Jeffrey CoachmanMIS LiasonCommented:
Try this:

Unzip the text file and create a folder:
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...
WallyTeeAuthor Commented:
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.  

Jeffrey CoachmanMIS LiasonCommented:
<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, 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.
Jeffrey CoachmanMIS LiasonCommented:
<Also, I'm embarrassed to say, I'm unable to understand your zip/unzip suggestion.  >
So where did you get stuck...?
Jeffrey CoachmanMIS LiasonCommented:
<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...
WallyTeeAuthor Commented:
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:



Then put the text file in it.  SEE ABOVE.


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...
Jeffrey CoachmanMIS LiasonCommented:
<Unzip the text file and create a folder:>
Meaning unzip the file I attached to the post:
Insde this file there are two files, the text file and the Database file.
WallyTeeAuthor Commented:
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
WallyTeeAuthor Commented:
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
WallyTeeAuthor Commented:
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

Jeffrey CoachmanMIS LiasonCommented:
<double-clicked and single-clicked on it in the Navigation Pane>
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
WallyTeeAuthor Commented:
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
WallyTeeAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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, this is better dealt with in a separate, new question.

WallyTeeAuthor Commented:
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.

WallyTeeAuthor Commented:
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
WallyTeeAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.