Alter ADP Connection String using VBA. 2

[edwilli]

Hello,

I am having a problem with the users of my ADP application saving their UserName and Password in the Connections tab, and then giving the application to unauthorized users. I would like to force users to type in their UserName and Password each time they open the ADP. I have built a custom login form, however I cannot find a way to link to the ADP connection string. Does anyone know how to alter the ADP connection information using VBA? Is this even possible?

Thanks in advance for your help.

Eric

 

[birgitd]

Hi Eric,

I have 2 suggestions. First, if you go to File -> Connection in your Project file (logged in as "sa&quot, there is a check-box called "allow password saving" .. make sure that is NOT clicked. Next, make a custom menu bar that does NOT include the Connection item. That way your users can't get to the Connection item to change it. That may solve the problem.

My other suggestion would be to use Windows authentication in your SQL Server database.

Hope this helps.

Birgit

 

[Di0genes]

Hello,

you can alter your Connectionstring with vba.

Public Function fct_Anmelden(User As String, PW As String, Optional str_Server As String, Optional str_db As String)
    
    Dim str_Connection As String
    Dim str_internServer As String
    Dim str_internDB As String
    
On Error GoTo HandleErr
    str_internServer = IIf(IsNull(str_Server) Or str_Server = "", Standardserver, str_Server)
    str_internDB = IIf(IsNull(str_db) Or str_db = "", Standarddatenbank, str_db)
 
'Standardserver and Standarddatenbank are global variables 'containig standard values for server and database                             
    If User = "" Or PW = "" Then
    str_Connection = ""
    Else
    str_Connection = "PROVIDER=SQLOLEDB.1;" & _
    "PERSIST SECURITY INFO=FALSE;" & _
    "INITIAL CATALOG=" & str_internDB & ";" & _
    "DATA SOURCE=" & str_internServer
    End If

'If you call these function with emty strings vor DB or PW
'the connection string will be "" and the Frontend is left
'disconectet. This is importent because the BaseConnectionstring
'is saved and will call the standard SQL-Login form when the 
'Frontend is restarted. You should call the function in this
'way if you exit the frontend 

    CurrentProject.OpenConnection str_Connection, User, PW
    
    If CurrentProject.IsConnected Then
        Form_frm_Anmeldung.Detailbereich.BackColor = 65123
        Form_frm_Anmeldung.cmd_Anmelden.Caption = "Abmelden"
       
        DoCmd.Minimize
        DoCmd.OpenForm "frmHaupt"
    Else
        Form_frm_Anmeldung.Detailbereich.BackColor = 255
        Form_frm_Anmeldung.cmd_Anmelden.Caption = "Anmelden"
    End If
        
      
ExitHere:
    Exit Function

HandleErr:
    Select Case Err.Number
        Case -2147467259
            MsgBox "Sie haben einen falschen Server- oder " & _ 
            "Datenbanknamen eingegeben! ", vbCritical, _ 
            "Falscher Server- oder Benutzername"

'False DB or Server 

        Case -2147217843
            MsgBox "Benutzername oder Passwort falsch!", _
            vbCritical, "Anmeldungsfehler"
            Form_frm_Anmeldung.txt_Passwort.SetFocus
            
'False PW or Username
            
        Case Else
            MsgBox "---Fehler " & Err.Number & ": " & Err.Description, vbCritical, "mod_formModule.fct_Anmelden"   'ErrorHandler:$$N=mod_formModule.fct_Anmelden
    End Select
' Ende des Fehlerbehandlungsblocks.
End Function

You have to start your frontend whit a form that has no recordsource! The connection will be done after calling the function fct_Anmelden("User","PW",["Server"],["PW"]).

In my frontend the form "login" will start per auto and is only minimiced. the login-form has the following close event:

Private Sub Form_Close()
Call fct_Anmelden("", "&quot
End Sub


Regards
Daniel

 

[edwilli]

This is what I was looking for.

Vielen Dank Daniel!

 

[chrissie1]

this was a good tip so it deserves a star

please dont forget

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong