Links using XLODBC.XLA not working after upgrade to 2003

[Bryan Linton]

I wrote a workbook that uses SQL.REQUEST to pull information from an access
database. After upgrading from Office 2000 to Office 2003, the workbook no
longer works. I get the following error message:

"This workbook contains one or more links that cannot be updated."

When I use the "Edit Links" button to investigate, it shows that the
XLODBC.XLA add-in is the problem. It's status shows: "Error: Source not
found". I've searched my system for this file and it's no longer present,
as far as I can tell. The location where it used to reside in O2K
(C:\Program Files\Microsoft Office\Office\Library\Msquery\XLODBC.XLA) no
longer exists; the folder structure ends after "C:\Program Files\Microsoft
Office\Office".

I'm sure Excel 2003 is still capable of providing this functionality, but
I'm not sure where to look for it. Any help appreciated.

Thanks in advance.

Bryan

 

[Frank Kabel]

Hi
MS does no longer support this addin. But see:
http://www.bygsoftware.com/examples/sqlrequest.html

 

[Bryan Linton]

Thanks for the link; it was very clear and helpful. Still having problems,
however. I've extracted the add-in files to a new location (different than
where the old ones were, but it's the path Excel is apparently looking for)
and installed the add-in. Once the add-in was installed to the path Excel
wanted, the error went away. However, the workbook is not pulling the data
out of the Access Database as it should be. The field containing the
formula that references the database is simply displaying:

#REF!

Nothing has changed other than the path of the XLODBC.XLA add-in. The
formula in that field is:

='C:\Program Files\Microsoft
Office\OFFICE11\LIBRARY\Msquery\XLODBC.XLA'!SQL.REQUEST("DSN=MS Access
Database;DBQ=C:\Custom Cache\Experience Rating Tables.mdb",,,"SELECT ELR,
DRATIO FROM ClassCodes WHERE CLASSCODE="&$B7)

The file "Experience Rating Tables.mdb" is still in the same location it
always has been. I haven't opened or tampered with that database in months.
My existing Office 2000 installation included Access, although I think it
was added later after Office 2000 STD edition was installed. The version of
Office 2003 I installed was Professional; could that have caused issues with
Access now that I'm upgraded to 2003? But wait....no, it can't be...access
isn't even required for Excel to use the XLODBC add-in, iirc. My users who
have been successfully using the workbook in Excel 2000 are using Office
2000 STD, so they don't even have Access installed.

I can't figure out what the invalid reference is; any ideas? I didn't think
this workbook would break so thoroughly on upgrade to 2003.

Thanks again,

Bryan