Solved

Creating System DSN on Windows 7 with code

Posted on 2013-06-19
3
1,983 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

867 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