Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

Part 2 Access ADP Tip : Get rid of the login box on startup

Note: This is part two of a series of articles on Access Data Projects,
Click to see Part 1
Click to see Part 3 

Access ADPs can provide you with the flexibility and power only a direct connection to SQL Server can provide, but a major drawback to a developer is the login box that will always show up when you distribute your application, Access will stubbornly hold on to the last account used to connect with the server, unless you store the password with the file, and that may not be ideal.

Use a low level SQL Server account and reset the project connection
I came across a partial solution to the problem using this support article. It describes how you can reset the connection to the user and password of your choosing, but it does not address how to get rid of the login box on startup.

The solution I developed is twofold: Use a low level account on startup, show a custom login form and then reset the project connection.

Start out by creating a new SQL Server login, (or a new Windows login if your ADP is on a Windows network and your using Windows authentication on your SQL Server database), that only has rights to the login stored procedure you’ve created for your ADP. (If you don’t have a login stored procedure you can find one soon in the same section where all are articles are located). This will allow you to save the username and password with your ADP project, but not provide rights to any table, view or stored procedure if the account info is compromised. Whatever you do, never use a high level account and password as your startup account with the ADP.

Now that you’ve saved the low level login and password with the ADP, the dreaded login form will no longer display on startup, allowing you to instead display your own login form for the users of your application.  Have them enter their login name and password, have them click on a button labeled Login and then proceed to break the connection and restore it with their connections to SQL Server.

Here is the sample code to validate their login, use it on your login form:
    If Not ChangeADPConnection(Me.txtUserID, Me.txtPassword) Then
Exit Sub
End If

txtUserID and txtPassword are two fields on the form where users enter their user name and password, substitute them with your own control names if you wish.

The ChangeADPConnection procedure will use the user name and password from the login form to validate the user and establish a connection with the server using their rights and not the rights of the low level login:
Public Function ChangeADPConnection(strUN As String, strPW As String) As Boolean
Dim strConnect As String
Dim strServerName As String
Dim strDBName As String

strServerName = “YourServerIPAddress”
strDBName = “YourDatabase”

On Error GoTo EH:
Application.CurrentProject.CloseConnection
‘The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = “Provider=SQLOLEDB.1” & _
“;Data Source=” & strServerName & _
“;Initial Catalog=” & strDBName
If strUN <> “” Then
strConnect = strConnect & “;user id=” & strUN
If strPW <> “” Then
strConnect = strConnect & “;password=” & strPW
End If
Else  ‘Try to use integrated security if no username is supplied.
strConnect = strConnect & “;integrated security=SSPI”
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
EH:
MsgBox Err.Number & “: ” & Err.Description, vbCritical, “Connection Error”
ChangeADPConnection = False
End Function

Conclusion
Access ADPs have a unique advantage over regular MDB’s when using SQL Server, I encourage you to use them if you can, along with SQL Server stored procedures, and create great solutions for your clients.

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help, SQL Server

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores