Using SQL Statements in VBA connected to Access Database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WannaBeVBQueen - 04 May 2005 20:56 GMT

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

Reply to this Message

SteveS - 04 May 2005 23:22 GMT

> 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
[quoted text clipped - 21 lines]
> 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.

---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message

Tim Ferguson - 05 May 2005 20:27 GMT

=?Utf-8?B?U3RldmVT?= <sanfu at techie dot com> wrote in
news:58D41FFC-4955-4F8A-B54B-2075BAD223E4@microsoft.com:

> 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

Reply to this Message