Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating System DSN on Windows 7 with code

Posted on 2013-06-19
3
Medium Priority
?
2,058 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
[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
  • 2
3 Comments
 
LVL 51

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

704 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