DSN-less connection to SQL server using a server-stored UDL file.

Posted on 2007-08-01
Last Modified: 2008-05-12
I would like to be able to push a command button on a form and establish a link to a SQL Server database and link a table.  I would like do that using a UDL file, stored on a shared drive and not using ODBC.  Can that be done?  This is a multi user database which is accessed by in-house personnel as well as by users that come in to our network via a remote desktop connection.

I am currently doing the above using ODBC.  The code behind the command button is:

DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=bkmcth2;Description=Data Access for Hedebrg bkmcth2 database;APP=Microsoft® Access;WSID=tmulloy;DATABASE=BKMCTH2;Trusted_Connection=Yes;TABLE=dbo.GL_JCTIMSHT", acTable, "dbo.GL_JCTIMSHT", "dbo_GL_JCTIMSHT"

I followed these steps and created a UDL:
1.Using notepad, create an empty text file and then change the extension to UDL.
2. Double click on the UDL file to bring up the Data Link Properties Dialog Box.
3. Click on the Providers tab and select the driver you wish to use.
4. Click on the Connection tab and enter the connection properties, each driver will require different settings, so I will not go into much details. All drivers will require a user name and password.
5. Click on the Test Connection button to verify your connection is working correctly. Change accordingly if you get an error.

When I click Test Connection, it says everything is OK.  I have now stored this in a shared folder on the server (J:\Shared\MyFIle.udl)

If I right-click the created UDL file and open with notepad this is what it contains:

; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;User ID=tmulloy;Data Source=bkmcth2;Initial Catalog=BKMCTH2

Can anyone help me put all of these pieces together?  I would like to change the code I have behind the command button so that instead of using ODBC , it goes out and uses the UDL file on the network drive, makes the connection to the SQL database and then links SQL Server table  "dbo.GL_JCTIMSHT" to my access application.

Question by:snyperj
    LVL 38

    Accepted Solution

    "DSN=bkmcth2;" or "Data Source=bkmcth2;" is some sort of ODBC call on the client machine, whether it is a file, user or system DSN. I can't think of anyway to actually link the tables into a DB without having it there.

    You can do ADO recordsets to forms that skip the DSN. Google ActiveConnection and ADO and you'll find many examples.

    The code below is the one we use to create system DSN's on the fly.

    Option Compare Database
    Option Explicit
    Option Base 1

    Private Const REG_SZ = 1    'Constant for a string variable type.
    Private Const HKEY_LOCAL_MACHINE = &H80000002

    Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
       "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
       phkResult As Long) As Long

    Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
       "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
       ByVal reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
       cbData As Long) As Long

    Private Declare Function RegCloseKey Lib "advapi32.dll" _
       (ByVal hKey As Long) As Long

    Public Function Add_System_DSN()

    'If you need to add multiple ODBC calls just copy from the Public Function _
     to the bottom and rename the function. And make the changes as indicated _
     by the comments. You only need the declarations above in the module once.

    'This function is designed for creating system level ODBC calls to MS SQL _
     Server databases on the fly.

    'The form events are listed below in the order that they occur.  The call to _
     this function has to be done in the Open Event, because the Load Event is _
     where it will attempt to use the ODBC call to start pulling in data.
    'Open Þ Load Þ Resize Þ Activate Þ Current

    Dim DataSourceName As String
    Dim DatabaseName As String
    Dim Description As String
    Dim DriverPath As String
    Dim DriverName As String
    Dim LastUser As String
    Dim Regional As String
    Dim Server As String
    Dim AddSetup As String

    Dim lResult As Long
    Dim hKeyHandle As Long
       'Specify the DSN parameters.

       DataSourceName = "DSNName"           '<--- The name of the ODBC Call. (1) See below.
       DatabaseName = "DBName"              '<--- This is the database you are targeting.
       Description = "Description"          '<--- (2) See below.
       DriverPath = "C:\WINDOWS\system32"   '<--- Default value. Don't change it.
       LastUser = "sa"                      '<--- This can be any valid SQL Userid.
       Server = "ServerName or IP"          '<--- The server or DNS value you are targeting.
       DriverName = "SQL Server"            '<--- Default value. Don't change it.
       AddSetup = "No"                      '<--- This is for Additional Setup Options

        '(1) This is what you want the ODBC call to be named, such as "VDW" or _
             "ISIR". You do need to be aware that if you are putting a front-end _
             on a delivered application that you are not overwriting some setting _
             that the application comes with.  You can check this by looking at _
             at a workstation that the application is installed on.
        '(2) Description: This is optional, but if you don't use it delete it _
             in the set values section below, or change it to a single space.

       'Create the new DSN key.

       lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
            DataSourceName, hKeyHandle)

       'Set the values of the new DSN key.

       'The first three below (QuotedId, AnsiNPW, AutoTranslate) are optional depending _
        on the ODBC setup that you are creating.  If you were setting up an ODBC call _
        manually on the second to last page it has check boxes for _
            Use ANSI quoted identifiers. = QuotedId _
            Use ANSI nulls, paddings and warnings. = AnsiNPW _
        and on the last page it has a check box for _
            Perform translation for character data. = AutoTranslate _
        On our home grown databases and many of our delivered ones these are turned off _
        by default. If they would need to be turned on (checked), you just delete the _
        line that matches it.

       lResult = RegSetValueEx(hKeyHandle, "QuotedId", 0&, REG_SZ, _
          ByVal AddSetup, Len(AddSetup))
       lResult = RegSetValueEx(hKeyHandle, "AnsiNPW", 0&, REG_SZ, _
          ByVal AddSetup, Len(AddSetup))
       lResult = RegSetValueEx(hKeyHandle, "AutoTranslate", 0&, REG_SZ, _
          ByVal AddSetup, Len(AddSetup))
       lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
          ByVal DatabaseName, Len(DatabaseName))
       lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
          ByVal Description, Len(Description))
       lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
          ByVal DriverPath, Len(DriverPath))
       lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
          ByVal LastUser, Len(LastUser))
       lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
          ByVal Server, Len(Server))

       'Close the new DSN key.

       lResult = RegCloseKey(hKeyHandle)

       'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
       'Specify the new value.
       'Close the key.

       lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
          "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
       lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
          ByVal DriverName, Len(DriverName))
       lResult = RegCloseKey(hKeyHandle)

    End Function

    LVL 38

    Expert Comment

    by:Jim P.
    Delete -- no refund.

    Author Comment

    sorry- I have been out sick- just wading through about a thousand emails.   :(
    LVL 38

    Expert Comment

    by:Jim P.
    Glad to be of assistance. May all your days get brighter and brighter.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now