Manage SQL Server Connections in ADPs
June 18, 2004
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