Using SQL Statements in VBA connected to Access Database

[Guest]

I'm having problems(SYNTAX error messages or wrong text added to database
table) when I use the following UPDATE and INSERT INTO SQL statements in my
VBA code, why is this?

"INSERT INTO LogIn (UserID,PW) VALUES=(txtNewUserName.text,CurrentUser)"
(or)
"UPDATE LogIn SET PW = '<txtNewPassword.text>' WHERE UserID =
'<CurrentUser>' "

All I want is my code to write the values from the textbox control
(txtNewUserName) and/or global variable (CurrentUser) into the specified
Access database table fields.

My connection to Access Database is done using ADO Object and everything
work fine, I just can't get it to Execute properly:

nwConStr2 = "Provider=microsoft.jet.OLEDB.4.0;" & "Data
Source=C:\Passwords.mdb"
nwCon2.ConnectionString = nwConStr2
nwCon2.Mode = adModeReadWrite
nwCon2.CursorLocation = adUseClient
nwCon2.Open
nwSql2 = "INSERT INTO LogIn (UserID,PW)
VALUES=(txtNewUserName.text,CurrentUser)"
nwCon2.Execute nwSql2, , adExecuteNoRecords

 

[Guest]

I'm having problems(SYNTAX error messages or wrong text added to database
table) when I use the following UPDATE and INSERT INTO SQL statements in my
VBA code, why is this?

"INSERT INTO LogIn (UserID,PW) VALUES=(txtNewUserName.text,CurrentUser)"
(or)
"UPDATE LogIn SET PW = '<txtNewPassword.text>' WHERE UserID =
'<CurrentUser>' "

All I want is my code to write the values from the textbox control
(txtNewUserName) and/or global variable (CurrentUser) into the specified
Access database table fields.

My connection to Access Database is done using ADO Object and everything
work fine, I just can't get it to Execute properly:

nwConStr2 = "Provider=microsoft.jet.OLEDB.4.0;" & "Data
Source=C:\Passwords.mdb"
nwCon2.ConnectionString = nwConStr2
nwCon2.Mode = adModeReadWrite
nwCon2.CursorLocation = adUseClient
nwCon2.Open
nwSql2 = "INSERT INTO LogIn (UserID,PW)
VALUES=(txtNewUserName.text,CurrentUser)"
nwCon2.Execute nwSql2, , adExecuteNoRecords

WannaBeVBQueen,

Comment out the EXECUTE line for a while and add a line (for debugging)
above the Execute line:

Msgbox nwSql2

I'd be willing to bet that the msgbox shows *exactly* what is in the code,
not the values for the password and user name.

Now change the line to: (watch for line wrap)

nwSql2 = "INSERT INTO LogIn (UserID,PW) VALUES=(" & txtNewUserName.text & ",
" & CurrentUser & ");"

and look at the msgbox. It should have the values for the variables instead
of the text strings "txtNewUserName.text" and "CurrentUser".


The UPDATE string would be:

"UPDATE LogIn SET PW = '"& txtNewPassword.text & "' WHERE UserID = '" &
CurrentUser & "' "

Look closely - there are
/single quote/double quote/
and
/double quote/single quote/
and
/double quote/single quote//double quote/

in the UPDATE string.

 

[Tim Ferguson]

nwSql2 = "INSERT INTO LogIn (UserID,PW) VALUES=(" &
txtNewUserName.text & ", " & CurrentUser & ");"

No equals after the VALUES keyword and you need quotes around the strings
too:

INSERT INTO Login(UserID, PW)
VALUES('dayna', 'starbuck33')


HTH


Tim F