Write an access record to mysql

Posted on 2011-10-03
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"

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

Question by:Needy11
    LVL 84

    Accepted Solution

    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='' 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.

    Author Comment

    Yes I have josenrolusers linked. I shall try your command and revert. Tx

    Author Comment

    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.

    LVL 84

    Assisted Solution

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

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


    Author Comment

    Tx that helped. I now get an error as follows...

    Syntax error (missing operator) error in query expression '' is the read in email adddress for the current access db record.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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] & "'")


    Author Comment

    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])

    Author Comment

    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='' WHERE access_partno=" & strPart_No)

    but I get Access can't find the firle"|1" in your expression (?)

    Author Comment

    Instead of typeo 'firle' above please read 'field'

    Author Comment

    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 & "'")

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now