Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    So I have spreadsheets linked via a "new database query" back to an Access database I have that combines different sources of data. One of those sources is an ODBC data link. I accidently deleted the ODBC link and have since reestablished it. I verified that all of my queries do work properly. When I return to the final report output in Excel and attempt to refresh, I receive the following error: [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'CCCRemedy2' failed."

    I checked the ODBC links and they test successfully. Somewhere between access and excel via microsoft query, there is a break that i cannot identify. Do you where i can check for there error?
    thanks
    christine

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Are you sure the re-created link is exactly the same as the original?
    Can you copy a couple of the cells to a new sheet to test?
    Also create a new one manually rather than copy.

    FWIW, I always use registry poking to copy/backup/restore ODBC settings. Much faster and no mistakes.
    You need to copy 2 entries from either machine or user settings, but I prefer machine settings because it works for all users:
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources contains a REG_SZ entry for each data source.
    HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI contains a key with the values for the data source.

    I find exporting the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI key to a REG file and then editing the file to remove everything except the data source you want is best - see the example below.

    cheers, Paul


    This is the ODBC entry for dsnHFNetChkPro4
    Filename: dsnHFNetChkPro4.reg
    Double clicking on this file will offer to import the data into your registry.

    Code:
    Windows Registry Editor Version 5.00
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\dsnHFNetChkPro4]
    "Driver"="C:\\WINDOWS\\system32\\odbcjt32.dll"
    "DBQ"="C:\\Program Files\\Shavlik Technologies\\NetChk\\ShavlikScans.mdb"
    "DriverId"=dword:00000019
    "FIL"="MS Access"
    "SafeTransactions"=dword:00000000
    "UID"=""
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\dsnHFNetChkPro4\Engines]
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\dsnHFNetChkPro4\Engines\Jet]
    "ImplicitCommitSync"=""
    "Threads"=dword:00000003
    "UserCommitSync"="Yes"
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
    "dsnHFNetChkPro4"="Microsoft Access Driver (*.mdb)"

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wonderful. You're absolutely correct. It wasn't EXACTLY the same. I went back through it and found that I didn't save the password to the data source. So everytime I attempted to refresh, it failed because the password wasn't stored. Duh! Thanks for helping me level-set!
    thanks
    christine

Posting Permissions

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