mySQL Problem with join in VBA

[andytodd]

This also posted on Access VBA scripts as not sure who is best placed to answer.

Our database is mySQL, but has an MS Access (2000 at present) interface.

We have a script which imports a comma delimited file and updates various tables in our db. As the data is supplied from Personnel (Human Resources) it is frequently misspelt, or prone to format change without warning.

Our biggest headache is data on which department staff are paid by. We have to provide training information to various parties who want to know which departments etc have taken up training and which haven't.

To enable this to be administered, the script compares the Personnel spelling / wording for the department to a look up table, spitting out where it can't match things so we can teach the look up table. So far so good.

A recent change to a department title has me stumped. Both the csv and the lookup table contain "Mechanical & Manufacturing Eng", but Access fails to match the two either in script, or in a saved query. MySQLFront does match them up though. I thought it was the ampersand, but it can cope with "COMMUNICATIONS & PUBLICITY"

Any ideas?

If it is any help, SQL is below

    Dim myDb As Database
    Dim myQRY As QueryDef

      Set myDb = CurrentDb()
      Set myQRY = myDb.CreateQueryDef("")
      
      With myQRY
  
      .SQL = " UPDATE DATAFILE LEFT JOIN tbl_paypoint ON DATAFILE.Section = tbl_paypoint.Paypoint SET DATAFILE.sectionID = tbl_paypoint.Sectionid "
      .Execute