Manage SQL Server Connections in ADPs

Late last year I picked up a new client whose SQL Server data is accessed
through an ADP file (Access Data Project).  The Access part of the
application was familiar enough and I am very comfortable with SQL Server, but
the Data Project piece was new to me.  The most frustrating
aspect, oddly enough, was the database connection issue, that reared its
ugly head every time I had to move the project file between production and
development servers.

The problem is that the connection parameters (server name, database and
user login) are stored in the ADP, and must be edited to point at the
available server.  This gets really annoying when you have to move the
file from production to development and back again every time you want
to implement a new client request or enhancement.  The only way I
could find to accomplish this task was to open the ADP, let the now invalid
connection time out and then choose Connection from the File menu to relink. Not
a great plan.

I could not help but think there must be a better way.  It seemed to me
that the Connection dialog box should allow you to select an ODBC DSN or even a
UDL file.  That way, the file could guide the ADP seamlessly between
different server environments.  That would have made sense, but it is not
possible.  Therefore, for months I suffered until I ran into a developer
at our user group who is an expert with ADPs.  He passed on a suggestion
that greatly simplified this process and I feel compelled to pass it on, along
with an alternate and more elegant solution.

Knowledge base to the rescue … sort of.

In response to my question about how to switch connections for an ADP, my
friend from the user group sent me an email with this simple reference:  KB – 202615.  
Seemed kind of cryptic to me, but I recognized it as a Knowledge Base article
reference, so I looked it up, and here’s what I found, along with a link to the
actual article:

ACC2000: Access Project Prompts You to Log On Even Though
"Blank Password" Check Box Is Selected

        http://support.microsoft.com/default.aspx?scid=kb;en-us;202615
 

At first glance, this does not seem to solve the problem but the
code, shown below, can be modified to meet our needs.  These methods
implement the main idea of the KB article, exposing a public sub routine
named ConnectDatabase that may be called from the code behind the opening form
or from the AutoExec macro.  It is simple and extensible, but
unfortunately, it is strongly bound to the names of my production and
development SQL Servers. 

‘ This sub looks for specific SQL Servers on the network and tries to
‘ execute a valid connection. First it looks for the production server
‘ and then for various development machines.

Public SubConnectDatabase()
On Error Resume Next

Dim SQLServersAvailable As String
Dim fProduction As Boolean
Dim fDevelopment1 As Boolean
Dim fDevelopment2 As Boolean
Dim fDevelopment3 As Boolean
Dim CurrentServer As String

click here to view code to enumerate domain servers.
SQLServersAvailable = EnumerateServers(“SQL”)

‘ The results from the EnumerateServers call returns a semicolon
‘ delimited list of available servers. Search the string for the
‘ specific server. If found, the flag returns True.

fProduction = InStr(1, SQLServersAvailable, “ProductionSQL”) > 0
fDevelopment1 = InStr(1, SQLServersAvailable, “MyDevLaptop”) > 0
fDevelopment2 = InStr(1, SQLServersAvailable, “MyDevDesktop”) > 0
fDevelopment3 = InStr(1, SQLServersAvailable, “MyDevServer”) > 0

‘ This could be handled differently, depending on your situation.
‘ I look for the production server first, and again in the ELSE
‘ clause, if none are found. This could be the case if the server
‘ is part of a Workgroup instead of a Domain. It’s a simple failsafe
‘ to ensure the code works in production.

If fProduction Then
CurrentServer = “ProductionSQL”
ElseIf fDevelopment1 Then
CurrentServer = “MyDevLaptop”
ElseIf fDevelopment2 Then
CurrentServer = “MyDevDesktop”
ElseIf fDevelopment3 Then
CurrentServer = “MyDevServer”
Else
CurrentServer = “ProductionSQL”
End If

‘ From here, call the private sub that executes the connection.
fncConnect CurrentServer

End Sub Private

Sub fncConnect(ByVal strServer As String)
On Error GoTo ConnectionErr

Dim strConn As String ‘Connection settings.
Dim strPrompt As String ‘Message to display if error occurs.
Dim intBtns As Integer ‘Buttons/icon to display on error message.
Dim strTitle As String ‘Title bar of error message.
Dim intRetVal As Integer ‘Button clicked on error message.

‘ This connection string assumes each server has the same RBIUser on each.
strConn = “Provider=SQLOLEDB.1;Persist
Security Info= True;Data Source=
“_&strServer&”;UserID=RBIUser;Password=xxxx;Initial Catalog=ProdData”

Application.CurrentProject.CloseConnection
Application.CurrentProject.OpenConnection strConn

ConnectExit:
Exit Sub

ConnectionErr:
strPrompt = Err.Description
intBtns = vbApplicationModal + vbExclamation + vbOKOnly
strTitle = “ERROR #” & Err.Number
intRetVal = MsgBox(strPrompt, intBtns, strTitle)
Resume ConnectExit

End Sub

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles