Solved

Creating System DSN on Windows 7 with code

Posted on 2013-06-19
3
1,990 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
ID: 39261775
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
ID: 39521610
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
ID: 39532426
there was no solution - just a work around
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

815 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