What may be the reason for Error(VB & MS Access)

[prileep]

Hi,

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
 
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "\\Computer1\Project\SampleDB.mdb"
End With
strSQL = "Update Users set Password='" & txtNewPwd.Text & "' where UserID=" & UserID
cn.Execute strSQL

----------------------------------------------------------------------------------------------------------
"cn.Execute strSQL" statement gives run-time error Message: "ErrorSyntax in Update".

But when i copy the query from VB(on debugging) and execute in MS Access it s geting executed.
What is the reason for this error ?

[mabubakarpk]

Dear Try this one

With cn
.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\db1.mdb[Path of db]"

.Open

End With

[prileep]

Dear Try this one

With cn
.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\db1.mdb[Path of db]"

.Open

End With

I have establed connection with DB using current code itself. Even istarted accessing Database and executing querries. But the error is coming when i execute this particular Update statement only generates run time error.

[Killer42]

I can't remember the syntax for the SQL Update command, so I'll have to trust you on that.

However, a couple of posibilities come to mind. You might need to put square brackets around your names (Password, for instance, might be a reserved word). You may also need quotes around the User Id (unless UserId field type is numeric). See whether this works...

strSQL = "Update [Users] set [Password]='" & txtNewPwd.Text & "' where [UserID]='" & UserID &"'"

Also, have you checked that you are putting the correct values in the string, from the UserID variable and the txtNewPwd control? I would recommend you put a breakpoint on the Execute line, and examine the string before executing it. Or do a Debug.Print.

By the way, I think that using the same name for a database field and a variable (UserID in this case) causes unnecessary confusion, and would recommend you avoid it. That's just my opinion, of course. Also, make sure you have VB set to require explicit variable declaration, to ensure you have not accidentally used the wrong name and created a variable instead of referencing an existing one.

[prileep]

I can't remember the syntax for the SQL Update command, so I'll have to trust you on that.

However, a couple of posibilities come to mind. You might need to put square brackets around your names (Password, for instance, might be a reserved word). You may also need quotes around the User Id (unless UserId field type is numeric). See whether this works...

strSQL = "Update [Users] set [Password]='" & txtNewPwd.Text & "' where [UserID]='" & UserID &"'"

Also, have you checked that you are putting the correct values in the string, from the UserID variable and the txtNewPwd control? I would recommend you put a breakpoint on the Execute line, and examine the string before executing it. Or do a Debug.Print.

By the way, I think that using the same name for a database field and a variable (UserID in this case) causes unnecessary confusion, and would recommend you avoid it. That's just my opinion, of course. Also, make sure you have VB set to require explicit variable declaration, to ensure you have not accidentally used the wrong name and created a variable instead of referencing an existing one.

I have used "Option explicit" in my form and it is not giving me any compile time error.
And I have put a break point on assigning query to the variable strSQL.And i am getting the string "Update Users set Password='trustme' where UserID=1". I copied the value from immediate window and executed in MS Access and i found it working fine. But when executing from VB,through ADODB.connection object it is giving me error as i mentioned first.
What is the reason for this ??

[vijaydiwakar]

I have used "Option explicit" in my form and it is not giving me any compile time error.
And I have put a break point on assigning query to the variable strSQL.And i am getting the string "Update Users set Password='trustme' where UserID=1". I copied the value from immediate window and executed in MS Access and i found it working fine. But when executing from VB,through ADODB.connection object it is giving me error as i mentioned first.
What is the reason for this ??

just alter the column name from password to pwd and try it
ur problem will be get solved

[getmeidea]

just alter the column name from password to pwd and try it
ur problem will be get solved

Thank you very much for giving solution for this topic.....
Now the table value is updating from my front-end without any error.
But please let me know what is the reason, i am getting error in giving field name as PassWord.

[vijaydiwakar]

Thank you very much for giving solution for this topic.....
Now the table value is updating from my front-end without any error.
But please let me know what is the reason, i am getting error in giving field name as PassWord.

Dear there are som ereserved words for every backend whenevr u use that word say in oracle then oracle give the error at design time but unfortunatly access not so try not to use them
some word are date,password,name,desc in oracle etc
Good Luck

[Killer42]

Dear there are som ereserved words for every backend whenevr u use that word say in oracle then oracle give the error at design time but unfortunatly access not so try not to use them
some word are date,password,name,desc in oracle etc

Does Oracle allow them if you put square brackets around them (as I suggested earlier)? For that matter, does Oracle use the square brackets at all?