MsAccess 2010 to mySQL 5.5 "insert on duplicate key update" example

I searched for days with no luck. Could someone help me to find a way how to insert/update the contents of a table in Access 2010 to mySQL server 5.5 table ?
The table has thousands of records so it also needs to be efficient. I need examples of VBA code.

These are the specs:
Access :
Table name "ExportTable"
Fields:
"RouteOrder"
"StreetName"
"ZipStr"
"HouseNumber"
"Name1"
"Name2"
"Phone"
"PickupDate"
"Charity"
"MapCode"

Open in new window


MySQL table:
Table Name "Customers"
Fields:
"record_id" <-autonumber
"routeorder"
"streetname"
"zipstr"
"housenumber"
"name1"
"name2"
"phone1" <-Unique Key
"pickupdate"
"charity"
"mapcode"

Open in new window


The mySQL table is linked inside the Access DB via ODBC.
Thanks in advance !!
LVL 5
vguzmanIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you want to insert the records from the Access table into the MySQL table?

You can try this:

Currentproject.Connection.Execute "INSERT INTO YourMySQLTable(RouteOrder, StreetName, etc etc) (SELECT RouteOrder, StreetName, etc etc FROM YourAccessTable)"

If you still get the "duplicate key" error, then confirm that the record_id field is an actual increment field.
0
vguzmanIT ManagerAuthor Commented:
Yes from Access table to MySQL table but I want to INSERT or UPDATE depending if the phone1 in the MySQL table exist or not.
0
IrogSintaCommented:
How about using an "upsert" query which can update or insert simultaneously. You can find a sample posted by Fyed on this link and a step by step process in cactus_data's post here.
0
Nick67Commented:
Yes from Access table to MySQL table but I want to INSERT or UPDATE depending if the phone1 in the MySQL table exist or not.

Then you need to check for the existence of the record first
The mySQL table is linked inside the Access DB via ODBC.
so I assume that DAO code will be fine

Dim db as database
dim rs as recordset
dim rs1 as recordset

set db = currentdb
set rs = db.openrecordset( "Select * from ExportTable;",dbopendynaset, dbseechanges)
do until rs.eof
    set rs1 = db.openrecordset( "Select * from Customers where phone1 = " & rs!Phone dbopendynaset, dbseechanges)
    rs1.movelast
    rs1.movefirst
    Select Case rs1.recordcount
        Case 0
               'code like LSMConsulting's insert
         Case 1
               'Code for an update, which we can pencil in, in a bit
         case else
               'Oops, phone number duplicate!
      end select
      rs1.close
      set rs1 = nothing
      rs.movenext
loop

Open in new window


You'd knock it in with code like this.
A one walkthrough of ExportTable, inserting or updating depending on a match to phone1 = phone

It's good efficient code.
@LSMConsulting went with a SQL execute, I'd go with

with rs1
    Select Case rs1.recordcount
        Case 0
               .addnew
         Case 1
               .edit
         case else
               'Oops, phone number duplicate!
               'bail from here
               goto BailPoint
      end select
      !routeorder = rs!routeorder
      !streetname = rs!streetname
      !zipstr = rs!zipstr
      !housenumber = rs!housenumber
      !name1 = rs!name1
      !name2 = rs!name2 
      !phone1 = rs!phone1 
      !pickupdate = rs!pickupdate 
      !charity = rs!charity
      !mapcode = rs!mapcode 
      .Update
end with

BailPoint:

Open in new window


Although, WHY you'd want to do this in code is a mystery.
Create a new query.
Put in both Export Table and Customers
Join them on Phone = Phone1
Make it a left join, showing all records from export table, and only those from Customers where they match
Select all the fields from ExportTable for the query grid
Select Phone1 from customers
Put a Criteria of NULL for Phone1 and uncheck it from displaying in the results

Run it

Check that it has records, and the right ones.
Change it to a maketable query.
Run it

Now make a new query that will append all the records from the newly created table to Customers.
Delete the newly created table
Change the Phone1 criteria to IS NOT NULL
Check it.
Run it as a maketable query again
Now make a new query that will update Customers with the values for the newly created table.
Clean up
Done!

No code required.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.