Import/Update from one file to another

[ts_hunter]

I have a table that I want to update and insert into based on new data each week that is extracted out of another system and given to me in .xls format. I have already figured out the commands to pull the .xls information into a table (tmpCustomer) in my Access application.

What I want to do now is loop through each record in tmpCustomer and update that customer in the Customer table (customer name, address, etc), and if I hit a record in tmpCustomer that does not exist in Customer, I want to insert it.

[JoeM]

Both of those can be done fairly easily without any need of VBA using Action Queries.

You can join your temp table and permanent table, and do the updates using an Update Query.
You can set up and unmatched query between your temp and permanent table to find the new records and use an Append Query to add those records.

[rpeare]

got a sample of your original excel file? how are you uniquely identifying customers other than the spelling of their customer name?

What if you have something like

smith brothers farm
smith brothers farms

in other words is your source information going to give you unique customer names reliably?

how far do you want your uniqueness to go

let's say smith brothers has 4 different locations, do you want all four locations in your customer table or, do you have a more normalized structure where you have a customer table and a locations table for each of their shipping destinations? If you don't have a normalized structure how close do the addresses have to be, again what if you had

1212 mockingbird lane
1212 mockingbird ln

how would you handle addresses like this that are the same but appear as different text strings?

[ts_hunter]

The excel file will have one unique record for each customer, defined buy Customer_ID, which is the key of the Customer table and tmpCustomer. I am going to treat the values in the Excel file as the new good record, overwriting what is in my current Customer table. I won't be comparing name or address strings at all.

[rpeare]

So if you have this in your database:

Customer_ID  Customer_Name  Customer_Address ----> etc
1            Customer A     111 Wherever Lane

but in your excel file you have

Customer_ID  Customer_Name  Customer_Address  ---- etc
1            Customer AA    112 Whatsit Road

You are going to replace everything in your existing record with the incoming value?

and if it's a new customer ID you're going to insert that record.

so let's say this is your existing recordset (tblCustomers):

Customer_ID	Customer_Name	Customer_Address
1	Customer A	Address 1A
2	Customer B	Address 1B

let's say this is your incoming recordset (tblIncoming):

Incoming_ID	Incoming_Name	Incoming_Address
1	Customer AA	Address 1AA
2	Customer B	Address 1B
3	Customer C	Address 1C

Your request is actually 2 different queries.
1. Update any existing customers with the 'most recent' information
2. Add any new customers to your record set

So the first part would be a query like:

UPDATE tblCustomers INNER JOIN tblIncoming ON tblCustomers.Customer_ID = tblIncoming.Incoming_ID SET tblCustomers.Customer_Name = [incoming_name], tblCustomers.Customer_Address = [incoming_address];

This will update your tblCustomers with the data in your tblIncoming wherever the customerID's match.

The second query would be:

INSERT INTO tblCustomers ( Customer_ID, Customer_Name, Customer_Address )
SELECT tblIncoming.Incoming_ID, tblIncoming.Incoming_Name, tblIncoming.Incoming_Address
FROM tblIncoming;

This assumes that you have some method in place to prevent duplicate customer_ID's in the table tblCustomers.

[ts_hunter]

Thanks for the Query info. While that will work, if I find a way to eliminate duplicates, I would like to do this with VBA code. That way I can do it all in one swipe and add other logic later on.

like this:

read tmpCustomers until eof
if tmpcust_id found in Customer table
update Customer record with new info
else
insert tmpcust_id information into Customer table
endif
loop


I just have no idea what VBA to use to start programming this approach.

[rpeare]

cycling through each record of your incoming file and looking for a matching record in your existing data then deciding what to do is much more inefficient than the two queries I gave you. You would just have to be very clear on what your protocol was in replacing data and design your query around that protocol. If you're actually doing something more complex than what you stated (for instance if the street address and City change you want a prompt to appear to confirm the change) you would definitely want vba code cycling through it and coming up with a list of records that need confirmation to continue. Your initial problem though can be handled by the two queries I gave you.

[JoeM]

Thanks for the Query info. While that will work, if I find a way to eliminate duplicates, I would like to do this with VBA code. That way I can do it all in one swipe and add other logic later on.

If you create two queries, an Update Query and an Append Query like rpeare and I suggested, you can create a Macro or VBA code that runs both queries, back-to-back, in one step. So it will do everything you want "in one swipe".

[ts_hunter]

Thanks everyone. I created an unmatched query and then added an append to that to pull in my new records. That was the one I didn't know if I could pull off. I will create and update query to refresh my other records. I am so used to programming everything - - the idea of just using queries is kind of foreign.