Open Database syntax issues

[GBalcom]

Can anyone tell me what is wrong with this?

 Set dbs = OpenDatabase("", Options:=dbDriverNoPrompt, ReadOnly:=True,"Description=acsSQL;DRIVER=SQL Server Native Client 11.0;SERVER=REMOVED;UID=gb;PWD=REMOVED;APP=Microsoft Office 2010;WSID=GARY-PC;DATABASE=CabProgram;")

When I try in VBA IDE, it turns red and says "Compile Error, expected named parameter"

 

[CJ_London]

Probably need to put something like currentdb. in front of the opendatabase

 

[jdraw]

Did you DIM dbs somewhere?

 

[smig]

You must declare dbs as DAO.database

 

[spikepl]

This post is a perfect illustration for how NOT to ask a question.

OP provided an orphaned snippet, without any other code and without any context, so now a number of people have been passing time guessing this and that.

 

[GBalcom]

Ok guys, my apologizes, I thought this was going to be a quick "duh" kind of answer.

Here's the code:

Private Function ValidateLogin() As Integer
    '====================================================================
    ' Comments: Compares both the Hard drive code, and the username and password, to ensure they are valid
    ' State of Code: Under Development
    ' Params  :
    ' Returns : Integer
    ' Created : 11/18/2014 11:51 AM GB
    ' Modified:
    '====================================================================
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    'define variables
        Dim strHDSN As String
        Dim strUser As String
        Dim strPw As String
        
        
    'confirm form is populated
    Dim ctl As Control
        For Each ctl In Me.Controls
            Select Case ctl.ControlType
                Case acTextBox
                Debug.Print ctl.Value
                    If IsNull(ctl.Value) Then
                        MsgBox "Please ensure both the username and password are filled out" & vbCrLf & " then try again", vbInformation, ProgramTitle
                        
                        ctl.SetFocus
                        GoTo PROC_EXIT
                    End If
            End Select
        Next
    
    'populate variables
        strHDSN = GetHDSN()
        strUser = Me.txtUserName
        strPw = Me.txtPassword
        

        
            
        
    'open recordset to confirm variables
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    
    Set dbs = OpenDatabase("CabProgram", dbDriverNoPrompt, True, ResizeMemo("~=FCR#SlNs2fcD^<zynh"))
    
    strSQL = "SELECT * " & _
             "FROM dbo_tblLogin " & _
             "WHERE UserName = '" & strUser & "'"
             
    Debug.Print strSQL
    
             
             
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    With rst
        
        
        'ensure username is valid
        '.MoveLast
        If .RecordCount < 1 Then
            MsgBox "Username is not Valid" & vbCrLf & "Please try again", vbInformation
            Me.txtUserName.SetFocus
            GoTo PROC_EXIT
        End If
        
        'ensure password is valid
        .MoveFirst
        If rst("Password").Value = strPw Then
            'continue
            Else
            MsgBox "Password is not Valid" & vbCrLf & "Please try again", vbInformation
            Me.txtPassword.SetFocus
            GoTo PROC_EXIT
        End If
        
        'check to ensure computer is the one registered
        
        If Left(rst("HDSN").Value, Len(strHDSN)) <> strHDSN Then
            MsgBox "This computer is not registered for this software" & vbCrLf & "Please Contact Atlanta Cabinet shop to setup", vbInformation
            GoTo PROC_EXIT
        End If
        
    
        
        'close recordset
            .Close
        
    End With

    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    
        
        
        
        



'flag that everything was validated
    ValidateLogin = 1

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, Me.Name & ".ValidateLogin"
    Resume PROC_EXIT
    Resume
    'TVCodeTools ErrorHandlerEnd

End Function

the resizeMemo() function is to throw off hackers....the argument passed is used as a password to make the function return the connection string

Public Function ResizeMemo(strIn As String) As String
    '====================================================================
    ' Comments:  Returns a connection string for our SQL Cloud db
    '           strIn works as a password to shelter against hacker attacks
    ' State of Code: Under Development
    ' Params  :
    ' Returns : String
    ' Created : 11/18/2014 02:30 PM GB
    ' Modified:
    '====================================================================
    'ErrorEnablerStart
    On Error GoTo PROC_ERR
    'ErrorEnablerEnd


    
If strIn = "~=FCR#SlNs2fcD^<zynh" Then
        '  "ODBC;"
        ResizeMemo = "" _
            & "DRIVER= {SQL Server Native Client 11.0};" _
            & "SERVER= 195.215.45.ETC;" _
            & "UID= user;" _
            & "PWD= Password;" _
            & "DATABASE= CabProgram;" _
            & "Encrypt= No"
    Else
        ResizeMemo = vbNullString
    End If
    
    
    'ErrorHandlerStart
EXIT_PROCEDURE:
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "ModCore.ResizeMemo"
    Resume EXIT_PROCEDURE
    'ErrorHandlerEnd

End Function

 

[GBalcom]

Oh yeah, and it will work with currentdb....but I don't want to do that. You see, I'd like to make this a unique connection, before the linked tables are refreshed. If the login fails, then the db closes before anything can be written or changed.

I'm open to ideas

 

[MarkK]

Once you supply a named parameter calling a VBA subroutine, all following parameters must also be named. In the example below, the parameters in red are named parameters, and they are followed in blue by an unnamed parameter.

Set dbs = OpenDatabase("", [COLOR="Red"]Options:=dbDriverNoPrompt, ReadOnly:=True[/COLOR], [COLOR="Blue"]"Description=acsSQL;"[/COLOR])

That causes, as you've seen, a compile error in VBA. Named parameters are not required to be in order, but then the order of following unnamed parameters is ambiguous.