Hello all,

I have msaccess that I use to insert data into a mysql database on a local in house server.
Now what I would like to do is I have the below query

INSERT INTO User_logins ( GroupWiseID, ConnexLogin, [Password] )
SELECT NewHireIDs.[GW ID], NewHireIDs.[LIC ID], NewHireIDs.[ACD ID]
FROM NewHireIDs;

Basically what it does is insert a staff members groupwise id, username and default password into a mysql table called User_logins the above query works good however now what I want to do is have the query sha1() encrypt the password that it is inserting into the User_Logins table.

I thought the below would work

INSERT INTO User_logins ( GroupWiseID, ConnexLogin, sha1([Password]) )
SELECT NewHireIDs.[GW ID], NewHireIDs.[LIC ID], NewHireIDs.[ACD ID]
FROM NewHireIDs;

but that gives me an unknown function error. So I am looking for suggestions. I was told this might be able to be done in VB but I do not know VB so anyway to do this right in the sql view in mysaccess?

Thanks

Try taking sha1() out of the INSERT INTO statement and put it in the
SELECT statement: sha1(NewHireIDs.[ACD ID]) . You don't want to encrypt the fieldname [Password] you want to encrypt the value you have selected.

Ahhh ok well I tried that and I still get a "Undefined function sha1 in expression error box popping up?

My expertise is MSAccess and I am a newcomer to MySQL. I don't know if you can use functions within a query. The next areas you might look at is stored procedures or triggers.

Well I am a mysql guy and new to msaccess was originally thinking all sql was the same but msaccess seems to write their queries a bit different then how I am use to seeing/writing them. Can you point me to any good online tutorials on using stored procedures within msaccess 2007

Thanks