Importing from Excel to an existing table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to import data from an excel spreadsheet. Some of the data is revised
data for existing records and other data creates new records. The import
wizard won't allow me to over write the existing records. Do I need to
create a macro to handle this or is there a simpler way?
 
When you say "Some of the data is revised data for existing records", do you
mean that the new record from the Excel sheet should replace the old records?
If so, then all you need to do is delete all the records in the table before
you do your import. If you mean replace fields in the table with fields from
the Excel sheet, then that is a more complex operation. Let me know if you
need that much detail.
 
Unfortunately I am trying to selectively update scores in a table from a
spreadsheet. There is a member ID key field and the a series of nnumeric
scores. What I want to do is over write the scores if the member ID exists
and append the record if the member ID does not exist in the table.
Thanks
 
There are two ways you can do this. In either case, I would suggest you link
the Excel spreadsheet rather than import it.
The first way would be to create two queries that use the spreadsheet as the
source. One would be an Update query that will update the scores where the
member exists and an Append query that will append records to the destination
table where the member does not exist. If you are not moderately experienced
in VBA, this would be the way to do it.
The other way would be to use VBA to read through the spreadsheet as a
recordset and determine whether an append or an update is required for each
row in the spreadsheet.
 
Back
Top