Excel VBA/MySQL Update Stmt

gdspeare used Ask the Experts™
I am using Excel vba form to connect to a mysql table. My form has several textboxes that can be updated.  I am using the following UPDATE statment, but am getting a runtime error message about the syntax.  In the error message box, however, my statement is reading as I would expect it to.

usqlstring = "(UPDATE company set company = REPLACE('" & ucompany & "' WHERE prospect_id='" & updateid & "'))"
With rs
    ' Assign the Connection object.
    .ActiveConnection = cnnt
    .Open usqlstring
        ' Tidy up
End With
Set rs = Nothing
Set cnnt = Nothing
MsgBox "Updated"
End Sub

Thanks for the help -
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2004

I see a couple things to be corrected.  The final syntax of your SQL should be:

UPDATE company SET company.<fieldname> = <replace function> WHERE prospect_id='value'

1) Do you have a field in the company table called 'company'?  I mean, does company.company exist?
2) At the very least, your replace statement lacks a couple parameters and the closing parenthesis.  See the replace function in the MySQL docs to learn how to construct this part of the statement:


3) What's up with the parenthesis?  Get rid of them.
4) "with rs" implies you are using a recordset object to run the query...?  I know I've been out of VBA for a long time, but I'm about 99% sure this is way wrong.


Thanks for the reply.  

1. The table is called marketing, the field name is company.   I am also using a select statement with similar syntax and it is working just fine: "(SELECT * FROM marketing WHERE prospect_id ='" & prospectid & "')"

2. What kind of parameters would it be lacking? I'm indicating replace the existing "company" record with the variable "ucompany" where the existing prospect_id is equal to the variable "prospect_id".  I'll also look at the link.

3. Ok

4. How would you suggest that I run it?
Top Expert 2004
1.  The syntax for an update is:

UPDATE <table name> SET <fieldname>=<value>

Given the additional information, your query should look similar to below, but keep reading.

UPDATE marketing SET company=<replace function> WHERE prospect_id='value'

2.  REPLACE is used to substitute parts of string.  For example, REPLACE('foobar', 'bar', 'foo') would yield a result of 'foofoo'.  It sounds like you want to replace the entire company field with a new value.  In that case, a straight update is in order.  Your query should look like this:

UPDATE marketing SET company='newvalue' WHERE prospect_id='value'

4.  Again, it's been awhile since I've done VBA.  Perhaps another expert can chime in to fill in the missing details.  You'll want to run the query from either the connection object (the cnnt variable in your example code) or a command object which would be spawned from the connection object.  Your best bet is to post this point as a new question in the Access zone...my knowledge in the area only goes as far as Office 2002, and I've not worked with even that for about a year and a half.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Top Expert 2004

One more note on terminology.  In order to reduce confusion, we tend to be picky about the terms we use to describe objects.  You can pick up more of this by looking through a relational database tutorial.

table - a collection of records
record - a collection of fields
field - a single data point

Think of a database as a filing cabinet.  Within that cabinet, you have different drawers for different filing needs.  One can hold accounts receivable paperwork, another may hold customer correspondence.  The drawer would be a table.  Within each drawer, you'll have several file folders.  Each file folder holds information regarding exactly ONE subject of the drawer's topic.  If the drawer holds customer correspondence, then each file will be for one particular customer.  That file is a record.  The papers within that file detail specific attributes of that subject, such as name, address, the date the account was opened, etc.  Each paper is a field.
Top Expert 2004

A 'C'?  I may not have answered all of your questions, but a 'C'?


I apologize I must have overlooked this step.  I am new to this forum.  

I would grade this a B.  The answer was not exactly what I needed, but it certainly put me on the right track to figure our what I was doing incorrectly.

Routinet - Thank you very much.  
Top Expert 2004

To be fair, the 'C' was technically appropriate (I didn't answer the entire question), but I consider it appropriate only when an expert has been "less than helpful".  My own personal bias..  No problems, friend, and thank you for the grade change.  Good luck!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial