Results 1 to 2 of 2
  1. #1
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40

    Not using CREATE TABLE, but still getting syntax error

    Code:
    NewLogin = "CREATE LOGIN " & Me.txtUserID & " WITH PASSWORD = '" & Me.txtPassword & "'; USE myDB;" _
                    & "CREATE USER " & Me.txtUserID & " FOR LOGIN " & Me.txtUserID & "; EXEC sp_addrolemember 'myDB_admin', '" & Me.txtUserID & "'"
    dbs.Execute NewLogin
    This code results in "Syntax error in CREATE TABLE statement", even though I'm not using a CREATE TABLE.

    A print statement generates this:



    Code:
    CREATE LOGIN admin2 WITH PASSWORD = 'password'; USE myDB;CREATE USER admin2 FOR LOGIN admin2; EXEC sp_addrolemember 'myDB_admin', 'admin2'
    Which, according to my SQL Server, is valid SQL code. When run as a query server side it pops up the login, user, and role just fine.

    According to this note here: http://msdn.microsoft.com/en-us/library/ff837200.aspx MS Access doesn't directly support this form of CREATE statements though? It says refer to the DAO Create, but that page isn't filled out on MSDN. Therefore I'm a little confused as to what I need to do.

    I'm trying to create SQL server logins through this Access DB. I know that is horribly, horribly insecure, and probably why it won't allow me to do it. Is there any way around this? Is there a different method of doing this that is preferred? Should I be smacked in the head for even trying this? Keep in mind I still don't know much about SQL Server, but definitely enough to get me in trouble.

  2. #2
    PRMiller is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Location
    St. Paul, MN
    Posts
    16
    I realize this is an old thread... have you resolved your issue? If not, have you tried creating a generic pass-through query, then populating the SQL programmatically?

    Code:
        Dim dbCurrent As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbCurrent = CurrentDb  
        Set qdf = dbCurrent.QueryDefs("qryPassThrough")
    
        qdf.SQL = "CREATE LOGIN " & Me.txtUserID & " WITH PASSWORD = '" & Me.txtPassword & "'; USE myDB;" _
                       & "CREATE USER " & Me.txtUserID & " FOR LOGIN " & Me.txtUserID & "; EXEC sp_addrolemember 'myDB_admin', '" & Me.txtUserID & "'"
        qdf.Connect = "Your connection string"
        qdf.Execute

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-30-2012, 12:32 PM
  2. Replies: 5
    Last Post: 03-27-2012, 01:40 PM
  3. Alter Table syntax error
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 02-06-2012, 05:43 PM
  4. Compile Error Syntax Error
    By Mohamed in forum Access
    Replies: 3
    Last Post: 10-06-2011, 10:12 AM
  5. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums