4

I have been searching for several hours regarding how to create a user using SQL for a database I am building in Access. I found several sources on Microsoft's website that say I can use the CREATE USER command to do this. However, whenever I attempt to run the query, an error saying Syntax error in CREATE TABLE statement pops up. What am I doing wrong? Thank you in advance for your help! If you're interested, the code format I am attempting to use is as follows: CREATE USER username, password, pid.

Anakela
  • 65
  • 7
  • 1
    Note that "Access users" are a legacy feature for `mdb` databases. You won't be able to use them with the current (since 2007!) `accdb` format. If you are writing new code, I strongly recommend you not to rely on a technology that has been deprecated 7 years ago. – Heinzi Aug 23 '14 at 16:30
  • @Heinzi, thank you for your comments. I see why `CREATE USER` isn't working! This was my first time creating a database at all, let alone choosing an RDBMS, so I had no idea that Access wasn't going to work the way Microsoft said it would. Haha... Next time, I may end up using MySQL instead. – Anakela Aug 23 '14 at 21:23

1 Answers1

2

Access does support CREATE USER as a DDL statement, but unfortunately it won't work in all contexts. Specifically, it won't work if we try to run it from

  1. the Query Designer within Access itself,
  2. a DAO connection to the database, or
  3. an ODBC connection to the database.

It will only work when run from an OLEDB connection to the database. That can be accomplished from VBA code within the Access database itself by using the CurrentProject.Connection object, like so:

CurrentProject.Connection.Execute _
        "CREATE USER newuser newpassword newpid"

(Note that there are no commas between the three arguments to the CREATE USER statement.)

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you, @Gord Thompson. I'll give this a try, though I am nervous about using VBA since I have no idea what it is or how it works, or where to type it for that matter. I'll try to find some tutorials online. Thanks again. – Anakela Aug 23 '14 at 21:41