ODBC Connection Failure

Topic

New Reply

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?

Reply | Quote

Replies

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_MACHINESOFTWAREODBCODBC.INIODBC Data Sources contains a REG_SZ entry for each data source.
HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INI contains a key with the values for the data source.

I find exporting the HKEY_LOCAL_MACHINESOFTWAREODBCODBC.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_MACHINESOFTWAREODBCODBC.INI]

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIdsnHFNetChkPro4]
"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_MACHINESOFTWAREODBCODBC.INIdsnHFNetChkPro4Engines]

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIdsnHFNetChkPro4EnginesJet]
"ImplicitCommitSync"=""
"Threads"=dword:00000003
"UserCommitSync"="Yes"

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIODBC Data Sources]
"dsnHFNetChkPro4"="Microsoft Access Driver (*.mdb)"

Reply | Quote

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!

Reply | Quote