×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

append/import
2

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,

OCM

RE: append/import

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.

RE: append/import

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

RE: append/import

(OP)
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

RE: append/import

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

RE: append/import

(OP)

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

I would:
  1. link to the Excel file
  2. Delete all records from the current table
  3. 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

(OP)
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

RE: append/import

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

RE: append/import

(OP)

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

TIA,

Regards,

OCM

RE: append/import

Quote:

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.

RE: append/import

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close