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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roland - 27 Jan 2005 11:37 GMT

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

Reply to this Message

Brendan Reynolds - 27 Jan 2005 12:29 GMT

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.

Signature

Brendan Reynolds (MVP)

> 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
[quoted text clipped - 29 lines]
>
> Roland

Reply to this Message

Roland - 27 Jan 2005 13:57 GMT

Thank you thank you thank you.!!!

Roland

> The CurrentUser function returns a string, so you'll need quotes around it
> ....
[quoted text clipped - 37 lines]
> >
> > Roland

Reply to this Message

Holly - 23 May 2006 16:47 GMT

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!

Reply to this Message