I have multiple data sources in multiple databases that I have been tasked with correlating for business analytics. I currently have ODBC connections to two (one Pervasive, one Firebird) that I can use successfully in Excel. A DBA friend suggested I use Microsoft Access to create linked tables and perhaps make creating lookup tables between the data sets easier.
I've run into a problem. Although Excel uses the ODBC connection to Firebird fine, Access can't create the linked table through the Create Linked Table dialogue. I get the following error message:
ODBC--call failed
[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 30, column 13
when (#-104)
I take this to mean that whatever query Access is using to attempt to create the linked table is failing to be interpreted correctly by the Firebird database, but I don't get to see any of the code because Access is handling it. I seem to recall reading that Access tend to fall back to SQL query structure when it can't figure out how to talk to certain databases, but I can't find the source.
I did see a post by Max Vernon with regards to linked tables in SQL server 2008. He suggested using Microsoft OLE DB Provider for ODBC Drivers, but I have been unable to locate the driver.
I guess my question is this: Is it possible to manually configure linked tables in Access, do I need to get the OLE DB interface working (I have yet to figure out where it is to install it), or is there another method I should be exploring?
1 Answer 1
It is very easy to connect, just configure ODBC. However, when you install Firebird Server, you must select the option to copy files to the Windows directory.