“Error 3151 ODBC connection failed” - After upgrading to Win 7

[udigold1]

I have a network Excel file that connects to an Access db using vba code. Several workstations were upgraded from Win XP to Win 7 32bit and Office 2010. After that upgrade those workstations are now not able to connect to the and they keep popping up run time error 3151.


What's going on? How can I fix this?

Public Const dest_db_file = "\\na02.ds-invest.local\data$\Gemel\Dash\pizulim\pizulim.mdb"

Sub load_hafkadot()

Dim the_db As Database
Dim the_rec As Recordset

If Range("c3").Value = "" Then

    Set the_db = OpenDatabase(dest_db_file)

    the_str = "select * from Viewhafkadot_mavar_masik where hesbon='" & Format(Range("c2").Value, "000000") & "'"
    Set the_rec = the_db.OpenRecordset(the_str) 'this is the line that causes the prblem
End sub

[rpeare]

by upgraded do you mean 'replaced the computer' or 'installed win 7 over the win xp system'.

If you actually replaced the computers you will likely have to set up all the ODBC drivers again (if they are relevant)

Have you attempted to debug.print the SQL statement and see if it actually produces an expected result in your Access database?

do this:

the_str = "select * from Viewhafkadot_mavar_masik where hesbon='" & Format(Range("c2").Value, "000000") & "'"
debug.print the_str

copy the statement to a query designer SQL window in ms access and see if you get the result you expect.

Lastly, check to make sure the path is still valid (especially again, if it's a new computer and not a software upgrade) and make sure the computer(s) in question can see the location you're pointing to.

[udigold1]

Hi,
That was actually the case. The System DSN weren't installed on the Win 7 computers (even though they were the existing machines, so I guess they were formatted prior to the installation).

Anyway, it took me 2 hours of googling to figure it out and im not even an IT Person. I later found out that this problem have occurred for the last 4 months and the IT team couldn't figure it and I was told about it today because they thought it was an Excel problem... good for me, bad for them!

And I have to thank you, too.