?
Solved

Automate installation of ODBC system dsn link

Posted on 2008-10-14
1
Medium Priority
?
894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
I originally wrote this article to compare SARDU and YUMI, but have now added Easy2Boot, since that is the one I currently use and find the easiest to create and alter.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

718 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