Write an access record to mysql

I have (inherited) an access database which uploads NEW records to a MYSQL database on an event (button click). However I wish to update a part of an EXISTING record on a UPDATE button and need help with same. Here is what I have\where I am heading with code...

1. I want to save the key user details record within access when I make the record change. Therefore I have an event that executes DoCmd.RunCommand acCmdSaveRecord when the SAVE button is clicked

2. However I need to add code to the procedure that will write a part of the record that has been saved\updated e.g. an updated email address, to the correct record\field on the key user table in my mysql db(Joomla).

NOTE: The Joomla  db does not have matching tables so I am looking to query the Joomla table for the same user record (identified by a common participant ID value ) and change the email element\field to the new one.
The database connection opens up fine and I use the following code to execute a query that will isolate the MYSQL record in Joomla. Where to next though?
In pseudocode terms I want to know how to refer to the access email field of the current selected record and write its value over the email field of the MYSQL record whcih has the same participant number .


Many thx




strSQL2 = "jos_enrolusers"
strSQL3 = "Select distinct from jos_enrolusers where jos_enrolusers.email=?"

Set qdf = New ADODB.Command
qdf.CommandText = strSQL3
qdf.CommandType = adCmdText
qdf.Prepared = True
Set prmEmail = qdf.CreateParameter("EnterEmail", adChar, adParamInput, 50)

qdf.Parameters.Append prmEmail

Open in new window

Needy11Asked:
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:
The simplest way to update a table is directly through an Update statement. Are you using Linked tables? If so, you can do this:

Currentdb.Execute ("UPDATE jos_enrolusers SET email='someone@osmedomain.com' WHERE UserID=" & Me.SomeUserID )

If you're NOT using linked tables, then you'd have to build a connection to the database and then issue the same statement.

If you'll give use the table and field names you're trying to update we might be able to give you more precise SQL syntax.
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
Needy11Author Commented:
Yes I have josenrolusers linked. I shall try your command and revert. Tx
0
Needy11Author Commented:
OK LSM
Your UPdate statement above worked but I would like the statement to allow me to update the email address based on the value of the email field and access_partno field in the current access record. However when I execute the following...

CurrentDb.Execute ("UPDATE jos_enrolusers SET email=" & Me.Email &  "WHERE access_partno=" Me.[Courses_Part No])

I get a syntax error stating "missing operator in the query expression 'ciaran@icepe.euWHERE  access_partno=Me.[Courses_Part No]"

I'm not too good at VBA syntax yet. can you see the problem? Any help appreciated.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need a space in from of WHERE:

CurrentDb.Execute ("UPDATE jos_enrolusers SET email=" & Me.Email &  " WHERE access_partno=" Me.[Courses_Part No])

0
Needy11Author Commented:
Tx that helped. I now get an error as follows...

Syntax error (missing operator) error in query expression 'ciaran@icepe5.ie'

ciaran@icepe5.ie is the read in email adddress for the current access db record.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must offset Text field with single or double quotes:

CurrentDb.Execute ("UPDATE jos_enrolusers SET email='" & Me.Email &  "' WHERE access_partno=" Me.[Courses_Part No])

If Courses_PartNo is a Text field:

CurrentDb.Execute ("UPDATE jos_enrolusers SET email='" & Me.Email &  "' WHERE access_partno='" Me.[Courses_Part No] & "'")



0
Needy11Author Commented:
TX but..

Courses_part NO is not a text field access_partno is a textfield however. MIght be easier for me to change the field type but I'm loathe to do this for fear of repurcussions. WOuld this make a differnce.

Note: WHen I use the first example you have given I get this compile error:
Expected: List separator or )

I also tried the following on the chance that the signle quote only gets recognised when surrounded by doubel quotes but the same issue occurs. I appreciate the help again.Tx

CurrentDb.Execute ("UPDATE jos_enrolusers SET email=" & "'" & Me.Email & "'" " WHERE access_partno=" Me.[Courses_Part No])
0
Needy11Author Commented:
After above I ahve been thinking I need to convert Me.[Courses_Part No to a string value for the update to work. SO I tried this...

Dim strPart_No As String
   
    strPart_No = CStr(Me.[Courses_Part No])
    CurrentDb.Execute ("UPDATE jos_enrolusers SET email='someone@osmedomain.com' WHERE access_partno=" & strPart_No)

but I get Access can't find the firle"|1" in your expression (?)
   
0
Needy11Author Commented:
Instead of typeo 'firle' above please read 'field'
0
Needy11Author Commented:
It ok I got it. I switched ot a test deve dagtabase and needed to work off a differnt variable name for the particpant number. Her is what worked

pretty much an exact match for your second option.

CurrentDb.Execute ("UPDATE jos_enrolusers SET email='" & Me.Email & "' WHERE access_partno='" & strPart_No & "'")
0
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
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.