CountryGirlMD
asked on
Creating System DSN on Windows 7 with code
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\ODBCC P32.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
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\ODBCC
(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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
there was no solution - just a work around
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