ACC 2002 - sql ADD USER TO error

  • Thread starter Thread starter Tony_VBACoder
  • Start date Start date
T

Tony_VBACoder

In my Access 2002 application, I am trying to issue the
following SQL statement to add a User to the built in
Access Security Group "Read-Only Users" and I am getting
the error "Run-time error '-2147467259 (80004005)'
Invalid SQL syntax - related tokens did not match. Jet
expected GRANT...TO, REVOKE...FROM, ADD...TO, or
DROP...FROM.":

**********************************************************
Public Sub AssignUserToGroup(sUserName As String, sGroup
As String)
Dim conDatabase As ADODB.Connection
Dim sSQL As String

' This will assign a User to a particular group
Set conDatabase = Application.CurrentProject.Connection

sSQL = "ADD USER " & sUserName & " TO " & sGroup
conDatabase.Execute sSQL
**********************************************************

I don't get the error if I try to add the user
to "Users", "Admins", "Operators", "SuperUser". It is only
when I try to add the user to "Read-Only Users". If I use
the Tools/Security/User and Group Accounts, I have no
problem adding this user to the "Read-Only Users" group.
 
Hi,

Have you tried this method:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;210418

Note: it uses DAO instead of ADOX. If working with security DAO is the
preferred method, there are numerous issues with ADOX and security.

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Content-Class: urn:content-classes:message
| From: "Tony_VBACoder" <[email protected]>
| Sender: "Tony_VBACoder" <[email protected]>
| Subject: ACC 2002 - sql ADD USER TO error
| Date: Thu, 24 Jun 2004 09:16:08 -0700
| Lines: 26
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcRaBo6C9vPbEJ9VSqSpdKEXnFClQQ==
| Newsgroups: microsoft.public.access.security
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.security:11681
| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| X-Tomcat-NG: microsoft.public.access.security
|
| In my Access 2002 application, I am trying to issue the
| following SQL statement to add a User to the built in
| Access Security Group "Read-Only Users" and I am getting
| the error "Run-time error '-2147467259 (80004005)'
| Invalid SQL syntax - related tokens did not match. Jet
| expected GRANT...TO, REVOKE...FROM, ADD...TO, or
| DROP...FROM.":
|
| **********************************************************
| Public Sub AssignUserToGroup(sUserName As String, sGroup
| As String)
| Dim conDatabase As ADODB.Connection
| Dim sSQL As String
|
| ' This will assign a User to a particular group
| Set conDatabase = Application.CurrentProject.Connection
|
| sSQL = "ADD USER " & sUserName & " TO " & sGroup
| conDatabase.Execute sSQL
| **********************************************************
|
| I don't get the error if I try to add the user
| to "Users", "Admins", "Operators", "SuperUser". It is only
| when I try to add the user to "Read-Only Users". If I use
| the Tools/Security/User and Group Accounts, I have no
| problem adding this user to the "Read-Only Users" group.
|
 
SQL Token

Hello Tony,

I had the same problem just today (2/1/2009)

There is nothing wrong with the SQL. The reason is that ADO is not as tolerant as Access. Unlike the Access tool, to ADO the SQL appears wrong because the group name 'READ-ONLY USERS' contains
1) spaces
2) An invalid character (-)
3) A reserved word (USER)

You can fix it by changing the group name to 'ReadOnlyUsers' but also you should add square brackets in your code to catch any problems in the future
ie
sSQL = "ADD USER " & sUserName & " TO [" & sGroup & "]"

The same could apply to the username if spaces are allowed there.

Tony_VBACoder said:
In my Access 2002 application, I am trying to issue the
following SQL statement to add a User to the built in
Access Security Group "Read-Only Users" and I am getting
the error "Run-time error '-2147467259 (80004005)'
Invalid SQL syntax - related tokens did not match. Jet
expected GRANT...TO, REVOKE...FROM, ADD...TO, or
DROP...FROM.":

**********************************************************
Public Sub AssignUserToGroup(sUserName As String, sGroup
As String)
Dim conDatabase As ADODB.Connection
Dim sSQL As String

' This will assign a User to a particular group
Set conDatabase = Application.CurrentProject.Connection

sSQL = "ADD USER " & sUserName & " TO " & sGroup
conDatabase.Execute sSQL
**********************************************************

I don't get the error if I try to add the user
to "Users", "Admins", "Operators", "SuperUser". It is only
when I try to add the user to "Read-Only Users". If I use
the Tools/Security/User and Group Accounts, I have no
problem adding this user to the "Read-Only Users" group.
Click to expand...
 
Back
Top