Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    WA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    Check the user rights to the directory in which the database and tables are stored. Win 7 can be fussy about this.

  3. #3
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    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


    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    WA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  5. #5
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Which SQL Server driver are you using?

    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert


    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    WA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.)
    Wendell

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    WA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I created it as a User DSN.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •