Automate installation of ODBC system dsn link

Posted on 2008-10-14
Last Modified: 2013-12-17
Setting up deployment package for access front-end. it requires an installation of odbc link is there a way to automate this so i can included it in the installation package
Question by:david_88
1 Comment
LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 22711749

I wrote a small VB 6.0 program to handle creating shortcuts and setting up the ODBC DSN's. The code was originally in VBA in Access, but I moved it to VB (and hence got a true standalone EXE) it was difficult to determine the Access version installed, which affected the code.

I don't remember what the exact problem was, but it was problematic at best.

Attribute VB_Name = "OCS_DSNs"

Option Explicit           ' Require variables to be declared before being used.


Private Const ODBC_ADD_SYS_DSN = 4       'Add data source

Private Const ODBC_CONFIG_SYS_DSN = 5    'Configure (edit) data source

Private Const ODBC_REMOVE_SYS_DSN = 6    'Remove data source


Private Const vbAPINull = 0&


Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _

   hwndParent As Long, ByVal fRequest As Long, ByVal _

   lpszDriver As String, ByVal lpszAttributes As String) As Long


Function CreateDSNs(strDatabaseName As String) As Integer


   ' Using tblDSNs, Create/refresh required DSN entires for a database.


   Dim ws As DAO.Workspace

   Dim db As DAO.Database

   Dim rs As DAO.Recordset


   Dim strDriver As String

   Dim strAttributes As String

   Dim strDatabase As String

   Dim strDSN As String

   Dim intEntryCount As Integer

   Dim intNumberofDSNs As Integer

   Dim varRet As Variant


   'Dim pb As New Form_frm_ProgBar


    Set ws = DBEngine.CreateWorkspace("", "Admin", "")

    Set db = ws.OpenDatabase("P:\xxxxxx\SetClientEnv\SetClientEnv.MDB")

   Set rs = db.OpenRecordset("tblDSNs")

   intNumberofDSNs = rs.RecordCount

   intEntryCount = 0

   'pb.SetMessage "Creating/refreshing DSN Entries"


   With rs

      While Not .EOF

        ' Check if this entry applies to this database.

        If UCase(rs("DatabaseName")) = UCase(strDatabaseName) Then

             ' Register method can't create system DSNs

             ' only user ones.

             'DBEngine.RegisterDatabase rs("DSN"), _

             '         "SQL Server", _

             '         True, _

             '         "Description= xxxxxxx - " & strDatabase & _

             '         Chr(13) & "Server=" & rs("Server") & _

             '         Chr(13) & "Database=" & strDatabase & _

             '         Chr(13) & "Network=DBMSSOCN" & _

             '         Chr(13) & "Trusted_Connection=Yes"


            strDriver = "SQL Server" & Chr(0)

            strAttributes = "DSN=" & rs("DSN") & Chr(0)

            strAttributes = strAttributes & "Description= xxxxxxx - " & rs("Database") & Chr(0)

            strAttributes = strAttributes & "Server=" & rs("Server") & Chr(0)

            strAttributes = strAttributes & "Database=" & rs("Database") & Chr(0)

            strAttributes = strAttributes & "Network=DBMSSOCN" & Chr(0)

            strAttributes = strAttributes & "Trusted_Connection=Yes" & Chr(0)


            varRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, strDriver, strAttributes)


            If varRet <> 1 Then

              MsgBox "DSN Creation Failed"

              GoTo CreateDSNs_Err

            End If

        End If


        intEntryCount = intEntryCount + 1

        'pb.SetBarPercent (intEntryCount / intNumberofDSNs) * 100




   End With


   CreateDSNs = True



   On Error Resume Next


   If Not rs Is Nothing Then


    Set rs = Nothing

   End If


    If Not db Is Nothing Then


        Set db = Nothing

    End If


    If Not ws Is Nothing Then


        Set ws = Nothing

    End If


   Exit Function




   CreateDSNs = False

   GoTo CreateDSNs_End


End Function

Open in new window


Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

759 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

22 Experts available now in Live!

Get 1:1 Help Now