Direct SQL against MySQL.

[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

 

[Van T. Dinh]

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
****

 

[Harald Mæland]

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

HTH
Van T. Dinh
MVP (Access)

Hi,

Thanks very much for your answer!

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

Regards,
Harold