Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Best way to repeatedly Import data from Excel and disburse to Access tables

Best way to repeatedly Import data from Excel and disburse to Access tables

Best way to repeatedly Import data from Excel and disburse to Access tables

We have people in the field collecting data about buildings into multi-tab spreadsheets. (between 10 and 20 tabs total). We then want to copy this data into Access. I might try to do this a bit differently if given a choice, but I have no option but to have data collected in Excel and for Access to the the ultimate repository for it all.

Thus far I have put the worksheets into a layout that Access can recognize and created the tables in Access that will match the worksheets in Excel. The data is reasonably well normalized among the Excel Worksheets. (This is a too long story that i will wpare you reading).

My plan is to use append queries to copy the data from the worksheets into the Access tables. To do this, I believe I need to either import the worksheets into Access as tables or to link the worksheets.

With the option comes the quandary. There seems to be goods and bads of both options and I would love to hear from people who have done both or either of these methods. I have used both in the past for more limited scope tasks.

At first I liked linking. Once I have all of the Excel tabs linked, I can run the append queries. Then replace that spreadsheet with the next one, update the links and run the queries again. But then come the details.
1. I don't want people to have to manually update over a dozen links with every new spreadsheet coming in from the field.
2. Using the Import Data process in Access 2007, when setting up links, does not allow you to save the process to re-run it if you want to. Thus apparently requiring updating existing links. (That is, if I run the first spreadsheet with links, run my append queries, and then want to go to the next workbook, I can rename the first workbook (to break the links), then give my second workbook the same file name as when I set up the links and update the links so they now show the data from the second workbook. Then run the append queries again to get this second set of data into Access. And continue with however many workbooks we receive. The total number of these will be in the dozens.
3. So my problems with linking seem to involve the repeated renaming of spreadsheets and updating of many links to update the data in the linked tables in Access.

So I looked at importing worksheets as tables. Importing, not linking. Again, details.
1. In this case I can set a name for each import and presumably set up a macro that would run each of the imports, one after another. Is this true? I have not used macros in Access. Will they run these imports?
2. It would seem that after I run the append queries, I can set up a macro, or series of macros that will then delete out these imported tables, setting the stage to import them again from the next workbook to be processed.
3. So my problems here are whether I can use a macro to automate these processes (importing and later deleting the tables that are imported from Excel) and, again, the repeated naming and renaming of workbooks before each new running of the process.

And I just bet that there are issues that will arise that I have not thought about. I would surely appreciate all helpful comments about the two processes envisioned above and any other issues I may be missing.

Thank you in advance.

RE: Best way to repeatedly Import data from Excel and disburse to Access tables

MS Infopath is the tool for you. I don't think your users have to have it, only you.
User fills in form they get via e-mail, or on network(can look like the s/sheet tab they were using)
on sending/saving, any NEW data added populates a table in your Access/other DB
- you need to read notes & see demos, but that's a very simple view.

Or not using Infopath program directly(same back-end I expect):
If you are using Office2007 Access, it will mail a form to users to update, again form can show existing data if required.
It will update your d/b with NEW data only when they reply.

There will be various demos/help on this on the net now you know what to look for.

RE: Best way to repeatedly Import data from Excel and disburse to Access tables

I have always advocated importing all nedw data into a temp table, validating thet imported data, se[eratomg a;; invalid records, transfering the valid records to the normal (real / normalized table[s]), and returning the invalid records to the orininator / sender for correction.
used to
Of course this is a bit more immediat work / effort on your part, but ultimatly a much better database to work with.

I believe aearch through these fora would produce some specoific examples of techniques used to accomplish thses objectives.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close