Access97 frontend with a few linked tables to sql issue with windows7 64bit

[WSLmbr]

I have an Access 97 database with a few Linked Tables to SQL tables. This works fine from Windows XP with Access 2002 and 2003. However, new Windows 7, 64-bit PCs, running either Access 2007 or 2010, cannot run a daily report that initially runs a macro and then loads the daily sales report data. Once the macro is run (by someone else) and the data is loaded, the Win7 PCs can then display the report and carry on. I have seen suggestions referring to the need to use the 32bit ODBC driver specifically for the ODBC connection and have tried that, but it still doesn't work from the 64bit Win7 PCs. I could convert the database to Access 2002 or 2003, or even 2007, if necessary I guess, if I save it as an .mdb format so the prior versions could still open it. I'm not convinced converting the database would resolve the issue, however. Any ideas or recommendations?

This is the error I get:

Macro Single Step error: 3151
Action: OpenQuery
MacroName: mcrRefresh
Arguments: queryMakeTbl, Datasheet, Edit

[caveman144]

Check the user rights to the directory in which the database and tables are stored. Win 7 can be fussy about this.

[HiTechCoach]

With Access 2007/2010 you need to make sure the front end database ( .accdb's or .mdb's) is in a trusted location.

See: Trust Center in Access 2007 and 2010

Boyd Trimmell aka HiTechCoach
Microsoft MVP - Access Expert

[WSLmbr]

I don't see my reply so I'll post this again. I did make sure the Access front end database was in a trusted location. I found that the version of Access is irrelevant, the same error message is displayed no matter if it's Access 97, 2003, 2007, or 2010. The problem apparently is a product of the 64-bit OS. Any other suggestions?

Thx!

[HiTechCoach]

Which SQL Server driver are you using?

Boyd Trimmell aka HiTechCoach
Microsoft MVP - Access Expert

[WSLmbr]

Under SysWOW64, odbcad32.dll (because this is a 64 bit OS) I'm configuring a user dsn with sqlsrv32.dll. Actually, when I create the ODBC connection, whether it's using the 64bit or 32bit ODBC connection tool the connection tests fine during the connection configuration.

[WendellB]

Are you creating your ODBC connection as a System or User DSN? I have seen issues trying to use a System one on Win7, especially on the 64-bit version. You might try doing it as a User DSN. (If you are using roaming domain users that can be a pain, as each user will need to have their own DSN.)

[WSLmbr]

I created it as a User DSN.