ACCESS FEED: Re: [MS_AccessPros] Importing Excel Data

Sabtu, 27 Oktober 2012

Re: [MS_AccessPros] Importing Excel Data

Hi Rob,

set up your data structure properly in Access.

import the Excel data to its own table.
Add key fields to the table (this can be done with code) -- or have a structure already set up with the new fields at the end

Populate key fields from the main table where records already exist
run update queries for those records to fill in the main id

append records that don't have a main id
then you can append/update your related records

Warm Regards,
Crystal

Learn Access on YouTube
http://www.youtube.com/LearnAccessByCrystal

*
(: have an awesome day :)
*

________________________________
From: rodbevill <desertscroller@cox.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Saturday, October 27, 2012 8:37 PM
Subject: [MS_AccessPros] Importing Excel Data

I have a question on best way to import data into the database I am designing. The database has several tables, i.e. taxpayer table with associated data, property table with its associated data. The taxpayer may have multiple properties. The Excel data received from governing body has the data listed per property; therefore, there may be multiple records in the Excel spreadsheet. I am wanting to import the data into the proper tables. My question is on an approach. I am considering two approaches:
1) Try using vba code to read the Excel sheet then process each
record one at a time.
2) Import the spreadsheet into a table using the same field names
then process the data in that table to populate the main data
tables, taxpayer (if needed such as new record) and the property
table with new records. After being process the spreadsheet
table would be cleared.
Any ideas which would be a better approach or another approach?

There may other imports to be made to multiple other tables.

Thanks for any suggestions. (Hopes the above makes sense.)
Rod

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]