[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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