append/import
append/import
(OP)
Greetings,
Currently running access 2016 (be/fe) split db. We have a table that holds old records that is not up to data and try to updated using excel file that is current and up to date.
My goal is remove old records from the table and to include the latest records to this table.
What is the procedure to accomplish this?
TIA
Regards,
Currently running access 2016 (be/fe) split db. We have a table that holds old records that is not up to data and try to updated using excel file that is current and up to date.
My goal is remove old records from the table and to include the latest records to this table.
What is the procedure to accomplish this?
TIA
Regards,
OCM
RE: append/import
I will assume the records have some kind of primary key. So link to the Excel table using the import function. Link to the data do not import. Then first do an update query. If the PK already exits update the record with the information in the linked table. Next do an insert query If the PK does not exist insert the record from the Excel table. I assume you want to keep records in the db that are not in the Excel.
RE: append/import
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: append/import
Dhookam, I imported the excel with a primary key. I highlighted the existing customers table and save object as tblCustomers_old. Now, how do I create update/append query?
Regards,
OCM
RE: append/import
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: append/import
Thanks,
Yes, to answer your question
Simply replacing all of the current records with the imported Excel records, as the excel record is up to date.
TIA
Regards,
OCM
RE: append/import
- link to the Excel file
- Delete all records from the current table
- Append records from the linked Excel file to the now empty Access table
If there are lots of records, you may need/want to compact the database following the process.Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: append/import
Thanks.
Yes, I propose the idea of linking. But, I was instructed to import instead.
Here is where I'm in my test environment:
Imported the excel sheet (61270 records)
Selected my table, add and close
Also, in query design, I selected my table from Table drop down (before running query, I noted record count = 75K).
After the update, I noted 87K records (this tells me that customers that weren’t in the present tables were imported)
1. For some reasons, when I ran the query I got the following error where I clicked Ok
Microsoft Access can’t append all the records in the append query.
Microsoft Access set 0 field(s) to null due to a type conversion failure, and it didn’t add 61270 record(s) to the table due to key violations
2. Once everything is working in test, is it a matter of renaming tblCustomers in production old and copy the updated (tblCustomers) from test and paste it in production?
TIA
Regards,
OCM
RE: append/import
Please, if you are working with queries, you need to provide the SQL statements inside TGML tags.
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: append/import
Sorry, I was referring to
in design view > show table > selected the desired table > add > close
TIA,
Regards,
OCM
RE: append/import
RE: append/import
As for primary key, I selected Cust# and Loc manually (both number data type)
TIA
Regards,
OCM