Best way to Import Data

[vnfoster]

Good Morning All,

I have a functional question that I hope you all can help with, I am not sure what is the best way to set up information. Here is a little bit of background:
There is a National Database which stores information that I work with, but I can not manipulate it or use it in any functional way, there is a way to export said information into an excel report.

What I am trying to figure out is this information contains Name, Address, Phone Number, etc which I have currently stored in a Members Table, with 6 other tables connected to the Members Information. The Members Table has information stored in it that would not be in the excel roster that is pulled from the National Database. In addition some information would be new members and some information would be updated addresses and such and others would just be a copy of the information already contained within my database.

Is there a way to run an append or update Query based on the Excel Information or would a better solution be just to hand jam info from the Excel sheet into the created forms in Access?

Thank you in advance for any imput !

 

[Rx_]

B.T.W. there is an Excel forum at http://www.access-programmers.co.uk/forums/forumdisplay.php?f=55

Questions somewhat like yours have been discussed before.
Yes there is a way. Is it fully automated? No

In general, it might help if you break down the major steps and then take it on one at a time.
1. The Database exports to Excel.
2. Import the Excel into a Access Table (call it a Staging Table).
3. Data clean up (or data scrubbing) - run routines that look at the new Staging Table against the existing Access data. What is new, what is updated, (are there any deletions?). Set New data into a new Update Staging table. Set the Changes into a new Update Staging table.
4. Run routines to Add or Update Staging tables into the final data table. Typically, there is some additional routine to add a field about what date or what kind of update was applied to each affected record.

Stage 2 can be tricky. There may be data type conversions. There could be field name checking in case the Publisher (national db) changes the format. Most of all, there needs to be error checking and error traps / reports.

When you do this process manually, take careful notes of all the task and steps for the tasks. Document it in sequential order including any quality assurance reviews (like looking over the data). That should provide a good starting place.
If you are only doing this once a year, it might not be worth automation (coding).

I did something like this where the FERC prices for trading Natural Gas at the various hubs and terminals were published on the Web. They wanted a $4,000 mo subscription to get it in a DB table format. It took about a week, to automate it to consume the data and put it in SQL Server. The history of prices was also important.
Two days of that time was spent on Quality Assurance code. The FERC would randomlly change the format in several places to prevent people doing what I was doing. But, it was all public information. They just chose to mix it up to prevent making automation easy. I mention this because it is things like this that will change your project scope.