?
Solved

Write an access record to mysql

Posted on 2011-10-03
10
Medium Priority
?
243 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Needy11
  • 7
  • 3
10 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 36905416
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
 

Author Comment

by:Needy11
ID: 36910351
Yes I have josenrolusers linked. I shall try your command and revert. Tx
0
 

Author Comment

by:Needy11
ID: 36910862
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 36911868
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
 

Author Comment

by:Needy11
ID: 36918133
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 36923392
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
 

Author Comment

by:Needy11
ID: 36923812
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
 

Author Comment

by:Needy11
ID: 36923860
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
 

Author Comment

by:Needy11
ID: 36923870
Instead of typeo 'firle' above please read 'field'
0
 

Author Comment

by:Needy11
ID: 36924388
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question