ODBC/DSN Connection in VBA

[jmason1209]

Hey group,

Hopefully someone can help me out. I developed an application in Access for my company with XP OS and Office 2007 (32-bit). Part of this application used a function (below) to create the ODBC connection on startup.

Option Compare Database  
Option Explicit  
 
'Const ODBC_ADD_SYS_DSN = 1      'Add a user data source  
Const ODBC_CONFIG_SYS_DSN = 2    'Configure (edit) datasource 
Const ODBC_REMOVE_SYS_DSN = 3    'Remove data source  
Const ODBC_ADD_SYS_DSN = 4       'Add a system data source  
 
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 
 
Function Build_SystemDSN()  
Dim Driver As String  
Dim Ret As Long  
Dim Attributes As String  
 
Driver = "SQL Server"  
'attributes are the connection information  
Attributes = "server=ANSRLTR" & Chr(0)  
Attributes = Attributes & "DSN=DSN_Temp" & Chr(0)  
Attributes = Attributes & "Database=ANSRLTR_Prod" & Chr(0)  
'use this line if you want to use the users name and password 
'Attributes = Attributes & "Trusted_Connection=Yes" & Chr(0)  
'use this line if you are adding a username and password  
Attributes = Attributes & "Uid=Temp;" & Chr(0) & "Pwd=TempID" & Ch(0)  
Ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)  
'ret is equal to 1 on success and 0 if there is an error  
If Ret <> 1 Then  
MsgBox "DSN Creation Failed"  
End If  
 
 
End Function

Since then, my company has upgraded our devices to Win7 and Office 2010 (64-bit), and the function no longer works. I've went through some changes/suggestions based on the bit version (adding Ptrsafe, changing 'long' types to 'integer') but nothing helps. Does anyone have an idea of how I can fix this?

Thank you in advance.

 

[Guus2005]

The PrtSafe keyword must be used when declaring DLL functions in a 64bit environment. It must be placed before the Function keyword.

Private Declare [COLOR="Red"]PtrSafe[/COLOR] Function SQLConfigDataSource Lib "ODBCCP32.DLL" ....

and must be removed when using 32bit versions.

HTH

 

[jmason1209]

The PrtSafe keyword must be used when declaring DLL functions in a 64bit environment. It must be placed before the Function keyword.

Private Declare [COLOR="Red"]PtrSafe[/COLOR] Function SQLConfigDataSource Lib "ODBCCP32.DLL" ....

and must be removed when using 32bit versions.

HTH

Thank you, Guus, for your reply. I have done that already. I also changed all all 'Long' types to 'Integer'. Maybe I should have posted my code instead of the original code I found.

'Const ODBC_ADD_SYS_DSN = 1 'Add a user data source
Const ODBC_CONFIG_SYS_DSN = 2 'Configure (edit) datasource
Const ODBC_REMOVE_SYS_DSN = 3 'Remove data source
Const ODBC_ADD_SYS_DSN = 4 'Add a system data source

Private Declare PtrSafe Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Integer

Function Build_SystemDSN()
Dim Driver As String
Dim Ret As Integer
Dim Attributes As String

Driver = "PostgreSQL ANSI(x64)"
'attributes are the connection information
Attributes = "server=server_add;port=3115;DSN=ABC;Database=dbName;Uid=Me.UID;Pwd=Me.PWD;"
Ret = SQLConfigDataSource(0, 4, Driver, Attributes)
'ret is equal to 1 on success and 0 if there is an error
If Ret <> 1 Then
MsgBox "DSN Creation Failed"
End If

End Function

 

[Rx_]

Kind of an old post, but perhaps the LENB described in it can help determine if the MSOffice is 32/64 bit?
http://gpgonaccess.blogspot.com/2010/03/work-in-progress-and-64-bit-vba.html
e.g. The LenB function is used with byte data contained in a string. Instead of returning the number of characters in a string, LenB returns the number of bytes used to represent that string.

http://www.utteraccess.com/forum/office-2010-x64-bit-qu-t1914261.html
There is a lengthy discussion on it here too.
Was looking at it in the day. But, we now post everything to a Citrix Server. So every one is running from one single OS / Office version.
Glad to avoid this issue myself (for now).
But, feel encouraged to share your own lessons learned.