Work with MS Access for the first time

  • Thread starter Thread starter Carlo Razzeto
  • Start date Start date
C

Carlo Razzeto

I'm sorry, I was posting this before and ctrl + entered too soon:

Hello, I am under taking my first project that will use MS Access as it's
back end data store. The project was originally written against SQL Server
and worked happily but the client suddenly wanted to switch to Access (don't
ask me why because I don't know). Anyway, here is my problem... I am writing
an object that I will use as my authentication token on my web page. The way
it works is when you instantiated it, it connects to the database, queries
the tables for the data it needs, then closes its database connection and
cleans up after its self. That part of the project ported just fine and
works great, the problem I'm having is with a method of my user object that
updates user information. Even though my update query appears to be correct,
it's throwing a OleDbException loaded with the error "Syntax error in UPDATE
statement.". Here is the query that is being sent to the datase:

UPDATE users SET first_name = 'Carlo', last_name = 'Razzeto', password =
'gateway1', email_address = '(e-mail address removed)', access_level
='ADMINISTRATOR' WHERE user_idnum = 1

Here is how I am currently generating the query, however when I first
started having the problem I was declaring variables in the query and using
OleDbCommand.Paramaters.Add to load in the data:

this.Command.CommandText = "UPDATE users SET first_name = '" + first + "',
last_name = '" + last + "', password = '" + pass + "', email_address = '" +
email + "', access_level = '" + access + "' WHERE user_idnum = " +
this.user_idnum;
this.Command.ExecuteNonQuery();

General over view of how object works:

Instantiate object
Instatiation method calls initialize method that collects all object data
from the DB then disconnects
update method updates database then calls initialize method

Carlo Razzeto
(e-mail address removed)
 
I'm not sure if Password is a reserved word in Access but I'm pretty sure it
is in SQL Server. Anyway, do you have a Last Name with an Apostrophe in it?
Using concatenated sql like that (unless this is just for illustrative
purposes) is NOTHING but headaches and in a web app - it's begging for
disaster. Before I went much further, I'd run a proof of concept for them,
and have 12 people try to use it at the same time. When it breaks, show
them that it took very little stress to break it and go back to Sql Server.
(IF they still remain pig headed, keep a copy of the SQL Server code so that
the day everythign goes live and they realize the error of their ways, you
won't have to do too much rewriting). Also, since it's a syntax error, you
may need '' around user_idnum - not sure though.
 
W.G. Ryan eMVP said:
I'm not sure if Password is a reserved word in Access but I'm pretty sure
it
is in SQL Server. Anyway, do you have a Last Name with an Apostrophe in
it?
Using concatenated sql like that (unless this is just for illustrative
purposes) is NOTHING but headaches and in a web app - it's begging for
disaster. Before I went much further, I'd run a proof of concept for
them,
and have 12 people try to use it at the same time. When it breaks, show
them that it took very little stress to break it and go back to Sql
Server.
(IF they still remain pig headed, keep a copy of the SQL Server code so
that
the day everythign goes live and they realize the error of their ways, you
won't have to do too much rewriting). Also, since it's a syntax error,
you
may need '' around user_idnum - not sure though.
Click to expand...
<SNIP SIG AND OLD MSG>
Yeah, I agree with your position that concatinated SQL is bad, as mentioned
I was using OleDbCommand.Paramaters.Add. I had not consided that possibly
password is a reserve word, I really didn't have any issues running this
query on Sql Server however perhaps that is the issue. How would one get
around such problems? I belive in sql server all you would have to do is

.....username = @username, (password) = @Password.......

Would the same syntax work in Access? I suppose I'll give that a try in
access it's self. What's really driving me insane here is the fact that I
ran this query several times in Access it's self, no issues there! About
enough to make some one go insane.

Carlo Razzeto
(e-mail address removed)
 
Thanks to W.G. Ryan for making think about password possibly being a reserve
word, turns out that was the problem and I was able to solve it. Life is
once again happy :-)

Carlo0
 
Try it in access just to make sure. usually [] this do it Select
[ReservedWord]
FROM Whatever

I'm not sure this is it, i can't seem to find the updated list of reserved
words but I think that may be it.
 
¤ ¤ > I'm not sure if Password is a reserved word in Access but I'm pretty sure
¤ > it
¤ > is in SQL Server. Anyway, do you have a Last Name with an Apostrophe in
¤ > it?
¤ > Using concatenated sql like that (unless this is just for illustrative
¤ > purposes) is NOTHING but headaches and in a web app - it's begging for
¤ > disaster. Before I went much further, I'd run a proof of concept for
¤ > them,
¤ > and have 12 people try to use it at the same time. When it breaks, show
¤ > them that it took very little stress to break it and go back to Sql
¤ > Server.
¤ > (IF they still remain pig headed, keep a copy of the SQL Server code so
¤ > that
¤ > the day everythign goes live and they realize the error of their ways, you
¤ > won't have to do too much rewriting). Also, since it's a syntax error,
¤ > you
¤ > may need '' around user_idnum - not sure though.
¤ >
¤ <SNIP SIG AND OLD MSG>
¤ Yeah, I agree with your position that concatinated SQL is bad, as mentioned
¤ I was using OleDbCommand.Paramaters.Add. I had not consided that possibly
¤ password is a reserve word, I really didn't have any issues running this
¤ query on Sql Server however perhaps that is the issue. How would one get
¤ around such problems? I belive in sql server all you would have to do is
¤
¤ ....username = @username, (password) = @Password.......
¤
¤ Would the same syntax work in Access? I suppose I'll give that a try in
¤ access it's self. What's really driving me insane here is the fact that I
¤ ran this query several times in Access it's self, no issues there! About
¤ enough to make some one go insane.

Access QueryDefs are executed differently from within the Access application, which supports
extended features. If you run the QueryDef from outside of Access which requires the Jet database
engine the rules are somewhat different.

William is correct. Password is a reserved word in Jet.

http://support.microsoft.com/default.aspx?scid=kb;en-us;321266


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top