run-time error'-2147217900(8004e14)': syntax error in FROM clause

[Guest]

Anybody there? Please help. I using Access Versions 11. Below is the code
that I'm having the above error message with. I'm trying to create a
recordset from an access table. I plan on using the recordset in some codes
to follow which is not included in this pasted codes (trying to create
something similar to a cursor in plsql). Also, after I get my recordset, how
do I retrieve the current record?


Private Sub Form_Current()

Dim db As ADODB.Connection
Dim UserGroupRst As ADODB.Recordset
Dim SqlStmt As String


Set db = CurrentProject.Connection

Set UserGroupRst = New ADODB.Recordset


SqlStmt = "SELECT DISTINCTROW AdministerSecurity.GroupName " & _
"FROM AdministerSecurity " & _
"Where AdministerSecurity.UserId = " & CurrentUser



UserGroupRst.Open SqlStmt, db, adOpenStatic, adLockReadOnly, adCmdTable

UserGroupRst.Close
db.Close

End Sub


Thanks in Advance!

Roland

 

[Brendan Reynolds]

The CurrentUser function returns a string, so you'll need quotes around it
....

Where AdministerSecurity.UserId = '" & CurrentUser & "'"

.... that's a single quote followed by a double quote after the "=" sign, and
a single quote between two double quotes at the end.

 

[Guest]

Thank you thank you thank you.!!!

Roland

The CurrentUser function returns a string, so you'll need quotes around it
....

Where AdministerSecurity.UserId = '" & CurrentUser & "'"

.... that's a single quote followed by a double quote after the "=" sign, and
a single quote between two double quotes at the end.

 

[Guest]

I have just attempted to upsize an Access database (for the first time) to
connect to a sql backend. I followed the instructions from the white paper
"How to Migrate from Access to SQL Server 2000". I am in the process of
converting the code from DAO to ADO.

I keep getting the same error as mentioned in this post. All I am trying to
do is open this table to update a record. Here's my code:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "Currency", cnn, adOpenDynamic, adLockOptimistic

What am I doing wrong? Also, any resources you can recommend would be
greatly appreciated!