Direct SQL against MySQL.

  • Thread starter Thread starter Harald Mæland
  • Start date Start date
H

Harald Mæland

Hi,

I have a MS Access 2000 database, with linked tables from a MySQL server.
I'm trying to write a code to encrypt some passwords in a table on the MySQL
machine, using the 'PASSWORD()' function i MySQL..

Code:

SQL = "Update tblUser set passord =PASSWORD(""" & pwd & """) where ID = " &
rid & ""
currentdb.execute SQL

Since the 'PASSWORD()' function is special defined for MySQL, Access are not
able to run this command.

I therefore made a Direct SQL query with the SQL statement.

Question:

How, if possible, can I change the SQL statement within the query upon
executing?
Someone have mentioned using "currentdb.querydefs", but I can't get it to
work..

Anyone?

Greateful for answers,
Harold
 
1. Make sure Microsoft Data Access Object (DAO) in included in the
References.

2. Create a Pass-Through Query with some very simple SQL String the
appropriate Connection String to access you MySQL Server. The SQL String is
only for testing that the Pass-Through Query can communicate with your MySQL
and will be modified by code later. Save it as, says, "qpstUpdatePassword".

3. Code something like: (***untested***)

****
Dim db As Database
Dim qdf As QueryDef

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("qpstUpdatePassword")
qdf.SQL = "Update tblUser set [password] =PASSWORD(""" & _
pwd & """)"
'(assuming you assigned the value to pwd previously).
qdf.Close
DoEvents
db.Execute "qpstUpdatePassword", dbFailOnError
db.close
Set qdf = Nothing
Set db = Nothing
****
 
Van T. Dinh said:
Set qdf = db.QueryDefs("qpstUpdatePassword")
qdf.SQL = "Update tblUser set [password] =PASSWORD(""" & _
pwd & """)"
'(assuming you assigned the value to pwd previously).
qdf.Close
DoEvents
db.Execute "qpstUpdatePassword", dbFailOnError
Click to expand...
HTH
Van T. Dinh
MVP (Access)
Click to expand...

Hi,

Thanks very much for your answer!

I¨ve tried it, and it works perfectly.......

Regards,
Harold
 
Back
Top