Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Importing an Excel Spreadsheet into a Table

I have a table that has the primary key as a combination of the first name, last name and middle name. From time to time, I import records from a spreadsheet into the table by executing the following command: DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "File_Name", strflName, True, "".

The problem I am facing is that if there are any records in the spreadsheet that have exactly the same primary key values (first name, last name and middle name) as the record in the table, they will not get inserted since that will cause a duplicate primary key error. This does not happen very often, but it does occur from time to time. What I would like it to do in cases like these is to overwrite the existing record in the table with the new record from the spreadsheet, as I would prefer to have the latest version of a record in the table. How can I get it to do that?
0
geeta_m9
Asked:
geeta_m9
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you need to import the excel file into a temp Table.
after importing.. run two queries
1. append query, to append new records
2.update query, to update existing record with the new data from the temp Table
0
 
geeta_m9Author Commented:
I assume I should make the primary key of the Temp table be exactly the same as the main table?
0
 
Rey Obrero (Capricorn1)Commented:
that is correct..
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
geeta_m9Author Commented:
I tried what you suggested and it seems to work. However, it gives me a warning the Microsoft can't append all the records to the table (because of the duplicate records). How do I suppress this warning?
0
 
Rey Obrero (Capricorn1)Commented:
revised your append query with a where clause..

should be something like this

Insert into tablex.*
select tableY.*
from tableY
where tableY.id not in(select [id] from tableX)
0
 
geeta_m9Author Commented:
Ok. This command will also turn off the warnings: DoCmd.SetWarnings False
0
 
geeta_m9Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now