append/import 2

[OCM]

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,

OCM

 

[MajP]

I am guessing because there is not much information here.
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.

 

[dhookom]

I haven't tried it but I expect any query involving a linked Excel file won't be updateable. I could be wrong. If the query doesn't work you might want to import the Excel into a temporary table with a primary key and then create the update/append queries. When I create temp tables like this, I typically create a temp ACCDB file and link to the table(s).

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Thank you both.

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

 

[dhookom]

You only mentioned removing some records and adding some records. Is this the case or do you also want to edit/update some existing records? Or, do can you live with simply replacing all of the current records with the imported Excel records?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

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

 

[dhookom]

I would:
[ol 1]
[li]link to the Excel file[/li]
[li]Delete all records from the current table[/li]
[li]Append records from the linked Excel file to the now empty Access table[/li]
[/ol]

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

 

[OCM]

Duane,

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

 

[dhookom]

I have no idea what you mean by "Selected my table, add and close".

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

 

[OCM]

Sorry, I was referring to
in design view > show table > selected the desired table > add > close

TIA,

Regards,

OCM

 

[MajP]

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

Are you saying it worked or did not work? From that message above it looks like no records are added. And my guess would be that you are trying to append your PK to an autonumber field which it cannot do and thus a key violation for each record.

 

[OCM]

Given the old table had 72K and the new table 87K I would say it is updating. The only thing I wasn't sure about was the error. May be it means that not all 61270 records were imported.
As for primary key, I selected Cust# and Loc manually (both number data type)

TIA

Regards,


OCM