Manage SQL Server Connections in ADPs

Friday Jun 18th 2004 by Danny Lesandrini

Danny Lesandrini shares a unique solution for switching connections for an ADP using SQL Server Client Network Utility.

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"
        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.OpenConnection strConn

    Exit Sub

    strPrompt = Err.Description
    intBtns = vbApplicationModal + vbExclamation + vbOKOnly
    strTitle = "ERROR #" & Err.Number
    intRetVal = MsgBox(strPrompt, intBtns, strTitle)
    Resume ConnectExit
End Sub

Code post mortem

This code is not pretty, but it works.  First, we get a list of available servers using the EnumerateServers() function.  Next, we check the string for specific SQL Servers in the order I want them to be used.  Production if available; my laptop next; then my desktop and finally my server.  If none are found, it defaults to production.  Strangely enough, this is possible since the function to enumerate network servers does not work for Workgroups and returns an empty string unless there is a SQL Server running on the local machine.

Yes, it solved my problem, but I do not really like it that well.  It requires that the code in the application know about potential servers and, in this case, at least, the connection string information is exposed in the code, posing a security risk.  It worked, yes, and I used it, yes, but I kept my eyes open for a better solution ... and I found one, here in the web pages of Database Journal.

Late one evening I was reading over my DBJ newsletter and I saw a reference to a SQL Server article that mentioned the alias function of the SQL Server Client Network Utility. Hmmm!  What if I could create an Alias to my SQL Server with the name of my client's SQL Server?  That would sure make things simple.  The login password could be obfuscated by the ADP, not being exposed in my code and as far as the application is concerned, so long as it finds a server with the correct name and user login, it's a GO for opening up.

Could it really be that simple?  Yes it is.  The two screen shots below demonstrate how easy it is to create an alias server on a machine that is already running SQL Server.  Simply launch the Client Network Utility from the SQL Server program item menu, select the Alias tab and click Add.

You will then be prompted with the following screen.  Enter any alias name and then provide the actual server name (or IP address as shown below).  Select TCP/IP as the protocol and click OK.  That is all there is to it.  Now, my aliased server, RoofSQL, shows up in Query Analyzer as an available SQL Server and when I open my ADP, it finds what it thinks is the correct production server.  Now THAT'S a solution.



This has not been the most elegant article I have ever written, though I have mulled over its contents for weeks.  The topic appealed to me because, after searching the newsgroups for an answer, I came up empty and I figure if I am asking this question, someone else may be also.  Add to that the even cleaner solution of creating an alias server and I figure I have something worth writing about. 

» See All Articles by Columnist Danny J. Lesandrini

Mobile Site | Full Site