Do Table Names and Field Names make such a difference? (1 Viewer)

[vivian123]

Hi

I'm using MS Access 2000 on a Win 98 machine. I have a table called 'Users' in an Access DB. The table has 3 fields LoginID, Password and Disabled [datatype of all fields being Text]

The following UPDATE statement works just fine [from an ASP page].


sqlQuery = "UPDATE Users SET Disabled='Y' WHERE LoginID='" & someParameter & "'"

con.Execute sqlQuery 'con = initialized and open Connection object


However, the foll query throws an error:

sqlQuery = "UPDATE Users SET Password='" & NewPassword & "' WHERE LoginID='" & someParameter & "'"

This is the error I get...


Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement

Renaming the field to "Passwords" or "XPassword" fixes the error.

Also, prior to this the very same table was named USER [instead of USERs] That factor produced some real queer errors of its own that were fixed by renaming the table to USERs [the plural]

I don't understand why the table name or field name must have such a profound effect on the execution of a program [ASP script in this case] if these names are only logical entities.


The only theory I have come up with is that the terms "USER" and "PASSWORD" are of special significance to MS Access.

Hoping you could help me and thanking you in advance...

Sincerely

Vivian Jason

 

[The_Doc_Man]

Names make a difference if they happen to match the names of keywords used in Access. The PASSWORD, USER, USERS names are all keywords.

PASSWORD is part of SQL for CREATE USER (in DDL).

USER is a member of the USERS collection and is also part of DDL.

Try looking up Reserved Words (or is it Keywords? I forget...) under Help. You will see a long list of words you should not use for datanames.