Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 18, 2004

Manage SQL Server Connections in ADPs

By Danny Lesandrini

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM