Solved

Automate installation of ODBC system dsn link

Posted on 2008-10-14
1
886 Views
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
0
Comment
Question by:david_88
1 Comment
 
LVL 57

Accepted Solution

by:
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.

JimD.
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

        

        rs.MoveNext

      Wend

   End With

   

   CreateDSNs = True

   

CreateDSNs_End:

   On Error Resume Next

   

   If Not rs Is Nothing Then

    rs.Close

    Set rs = Nothing

   End If

   

    If Not db Is Nothing Then

        db.Close

        Set db = Nothing

    End If

    

    If Not ws Is Nothing Then

        ws.Close

        Set ws = Nothing

    End If

   

   Exit Function

 

   

CreateDSNs_Err:

   CreateDSNs = False

   GoTo CreateDSNs_End

 

End Function

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A list of useful business intelligence software.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

930 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

12 Experts available now in Live!

Get 1:1 Help Now