Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating System DSN on Windows 7 with code

Posted on 2013-06-19
3
Medium Priority
?
2,075 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 52

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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 how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

916 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