Link to Access db via ODBC

[dougG]

Hi

I have an Access application (not developed by me) that currently uses a SQL Server backend. This application needs to be split into several geographical areas that use different domains. Because of connectivity problems across domains, I was hoping to use Access backends in each of the areas, each area having it's own database.

I need to modify the existing Access frontend to connect to the new Access backend. However, when I attempt to link a table using ODBC, I get this error:

"You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database"

Using NT4, Access 97.

Any help greatly appreciated. Thanks

 

[FoFa]

Are trying to use ODBC or just link the tables directly?

 

[dougG]

I am using ODBC i.e from the Tables tab, click 'New', then select 'Link Table', then select 'ODBC Databases' from the 'Files of type:' drop down. I have an Access ODBC connection set up which, when selected, gives the error.

I need to link to a 'query' in the Access backend, much the same as you would link to a 'view' in SQL Server, and I don't actually know if this is even possible!

 

[FoFa]

As I understand it (big IF there) Access doesn't support that because the Query would have to run on the remote (other, backend, whatever) DB, and since Access is not "running" you can not do it. You have to link the raw tables and create a query on the front end where Access is running. ODBC won't let you link, import, export to Access either (must be security related). Remeber the differance between Access and SqlServer is you have an actual DB Engine running in the background to service your requests. In the case of MsAccess, the forground reads the MDB file directly with no engine running your request for you.

 

[dougG]

Thanks for that. Seems to make sense. I'm going to have to re-think my strategy.

Thanks for your help.