OpenRecordset fail with error 3141

[GMan]

I have a database running on SQL Server8. There are several users that connect to the database through ODBC using laptops that are running Windows XP. Everything works well for them.

New laptops with Windows 7 have a connection issue. When connecting with a Widnows 7 PC the following code is receiving the ERROR message 3151 on the OpenRecordset command. The ODBC is setup to have a user entered name and password. When testing the ODBC connection it connects successfully.

[VBA]Private Sub Form_Open(Cancel As Integer)
Dim Connection_flag As String
Dim rstdb As xRecordset

On Error GoTo Form_Open_Err
Set db = CurrentDb()
Connection_flag = "N"
DoCmd.Echo True, "Connecting to SQL Server...."
Set rstdb = db.OpenRecordset("Login", dbOpenDynaset)
Connection_flag = "Y"
' Maximize form
DoCmd.Maximize
DoCmd.ShowToolbar "CSRData toolbar"

Form_Open_Exit:
Exit Sub

Form_Open_Err:
If Err.Number = 3059 And Connection_flag = "N" Or Err.Number = 3151 And Connection_flag = "N" Or Err.Number = 3146 And Connection_flag = "N" Then
MsgBox "Login incorrect!!!" _
, vbOKOnly, "SQL Server Login Error"
DoCmd.Echo True, "Shutting down application...."
DoCmd.Quit
Else
MsgBox Error$
Resume Form_Open_Exit
End If

End Sub[/VBA]

[GMan]

Not often I post on here and then figure out my own issue. But I managed it this time!!!

This issue is with the setup of the ODBC. The bit of the driver used with the ODBC has to match the same bit for the connecting application. So, being that I am connecting to a 32-bit SQL Database, I will need to setup the ODBC with a 32-bit driver in Windows 7.

The catch is there is no great way to tell if you have the 32-bit driver or not. If you go through the Control Panel to get to the Administration tools you execute the file at C:\Windows\System32\odbcad32.exe. This would be the 64-bit driver. To run the 32-bit driver you will need to go to C:\Windows\SysWOW64\odbcad32.exe. From here setup the ODBC just as you normally would and all works great!

Only way I was able to tell the difference between the 32-bit and 64-bit driver is when I clicked on the "DRIVER" tab. The 64-bit driver only has an SQL Server driver (all the other drivers have been removed in Windows 7), the 32-bit driver has a very long list of drivers.

Hope this saves someone the frustrations I just went through!

[HiTechCoach]

Thanks for sharing your solution. I am sure many will benefit.