Solved

Creating System DSN on Windows 7 with code

Posted on 2013-06-19
3
1,972 Views
Last Modified: 2013-09-30
I have several Access 2007 front end applications that link to SQL and/or Oracle databases .
I have code that verifies the ODBC connections at startup  and creates the system DSN connections if they don’t exist.  

It works great on my XP boxes – but on Windows 7 it doesn’t create the DSN

If I manually create the DSN using the 32bit version of odbcad32.exe – my connection routine finds the connection correctly.  If I create the DSN using the 64 bit version the app can’t see the connections – which is what I would expect.

I added the path to the 32 bit version of the dll - C:\Windows\SysWOW64\
When I run the routine on Windows 7 I don’t get any error messages - but it doesn't create anything either

If I change the fRequest paremeter to 1 – user DSN – it will create the DSN on the user tab

So I’m looking for a way to create the System DSN with code on Windows 7 - with code

My XP systems are all Windows XP SP2 - Office 2007 SP2
The 7 boxes are all Windows 7 64bit - Office 2010 32 bit

SQL apps use the SQL Server Native Client 10.0 on both XP & 7
Oracle apps use Oracle in OraClient10g_home1 for XP
windows 7 have Oracle in OraClient111_home1 - both 32 bit & 64 bit

Here’s my code –

‘windows 7 version
Private Declare Function SQLConfigDataSource Lib "C:\Windows\SysWOW64\ODBCCP32.DLL" _
    (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

‘xp version
  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

Private Sub cmdLoadDriver_Click()
    Call fCreateODBCLinks2(Me.DSN, Me.DriverType, Me.Server, Me.Database, Me.UserName, Me.Password)
End Sub


Public Function fCreateODBCLinks2(sDSN As String, sDriverType As String, sServer As String, sDB As String, sUserID As String, sPW As String) As Boolean
    On Error Resume Next
    Dim db As DAO.Database
    Dim rsC As DAO.Recordset
    Dim sSQL As String
    Dim sOSVer As String
    Dim iSysUser As Integer
    Dim sAttributes As String
    Dim lResults As Long
       
    Call fSetStatusBar("Creating ODBC link to " & sDSN)
       
    Select Case sDriverType
        Case "ORA"
            sOSVer = fOSVersion
        Case Else
            sOSVer = "All"
    End Select
       
    Set db = CurrentDb
    sSQL = "SELECT * From ODBCDrivers WHERE [Drivertype] = '" & sDriverType & "' AND [OSVersion] = '" & sOSVer & "' ORDER BY InstallOrder"
    Set rsC = db.OpenRecordset(sSQL)
   
    While Not rsC.EOF
        Err.Number = 0
       
        sAttributes = "DSN=" & sDSN & Chr(0)
        sAttributes = sAttributes & "Server=" & sServer & Chr(0)
        sAttributes = sAttributes & "Database=" & sDB & Chr(0)
        sAttributes = sAttributes & "Description=" & sDB & " using " & rsC("Driver") & Chr(0)
   
        lResults = SQLConfigDataSource(0&,  4, rsC("Driver"), sAttributes)
               
        If Err.Number = 0 Then
            fCreateODBCLinks2 = True
            Call fSetStatusBar("ODBC connection established to " & sDSN)
            Exit Function
        End If

        rsC.MoveNext
    Wend
   
    fCreateODBCLinks2 = False
    MsgBox Err.Number & " = " & Err.Description

End Function
0
Comment
Question by:CountryGirlMD
  • 2
3 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Windows 7 is far more restrictive than WinXP, so I guess you will have to run that code with elevated rights (as an administrator) to get allowed to create a system DSN.

Another option is to create a file DSN which can be placed anywhere as the user's rights permit, or to use a DSN-less connection. You can Bing/Google on this for a lot of hits.

/gustav
0
 

Accepted Solution

by:
CountryGirlMD earned 0 total points
Comment Utility
Closing this question without a real solution
Our application installer is now doing the install of the system DSN separately and I opted to have the code create a user DSN on the Windows 7 machines if it’s missing when the app starts.
0
 

Author Closing Comment

by:CountryGirlMD
Comment Utility
there was no solution - just a work around
0

Featured Post

Free Trending Threat Insights Every Day

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 theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

771 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