msaccess sql sha1()

[jayreis]

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

[mcconeghey]

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.

[jayreis]

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

[mcconeghey]

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.

[jayreis]

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