Okay, so I just got a big project dumped into my lap to rebuild a database that was lost. This DB would import data from a couple excel spreadsheets and a text file, compare them to find any records that are missing from one or the other then spit that information back out in two reports. To make things harder, the person who uses this DB isn't the most computer literate and needs to be able to do everything with the push of a button. For this project I'm using Access 2003 with a possible need for 2000 backwards compatibility



First Issue: Importing. Is there ANY way to save an import so a macro could run it without the user doing anything? The other option is linked tables, but is there any way to update the data in those links because some of it needs to be changed. Also, can linking combine two excel spreadsheets into one linked table?

Second Issue: Information updating. As I mentioned before there is some information that needs to be updated before we can do comparisons. There is a field in both record sets that could be used for comparisons except for the fact it is formated all wrong.

In the excel spreadsheet it is set up as just a string of numbers like "3125" or "55879", but in the text file they added some preceding digits to this number code. The text file adds "HM" and a number of 0s to each number so that the field is a uniform 10 digits long. Like "3125" would become "HM00003125" and "55879" would be "HM00055879". So each record needs to have this data either added to it or trimmed off. The problem here is the variable number of 0s. I created a macro that would use the Len function to test how many zeros need to be added and update the field, but it can only run on one record at a time.

What I need to do is have either a trimming or adding updater for that field that runs in the background (either through the macro button or when loading a query) to make those fields compatible. It also would be preferable to work through linked tables.

Thankfully, there isn't any sort of rush on this paticular project. If you have any advice on any part of this project, please post

Thanks in advance