External MDB access via ODBC

[ca268339]

I'm trying to link an external MDB with a local database. After I've defined the ODBC data source and try to link or access the database I get the following message: "Error "You cannot use ODBC to import from, link to an External Jet or ISAM database table to your database." Is it possible to link to a remote machine's MDB or will I need to upsize to an actual DB server product, (e.g., SQLServer or Oracle?

Thanks in advance for any responses...

 

[hsitz]

You don't use ODBC to link to tables in an external .mdb. Just choose "F"ile, "Get External Data", and "Link tables" from the Access menu when the .mdb you want to link _from_ is open, and drill down to the external .mdb in the resulting file dialog. Then highlight any tables you want to link and press 'okay'. It's that easy. -- Herb

 

[hsitz]

From your post I'm not sure whether or not you were already aware of the automatic linking to .mdb's. If you already were, then I'm wondering why on earth you would want to link to an .mdb via ODBC? There's no advantage, as far as I can tell. -- Herb

 

[ca268339]

Herb,
Thanks for the response. Actually, I am aware of the auto linking along with the ability to link via the external data access, but I need the ODBC to link the MDB to a FrontPage application that doesn't use external data access. I was simply trying to use Access to test ODBC access to the remote MDB.

Thanks.

 

[hsitz]

Oh.

Interesting that Access can't link to ODBC data sources on network drives. Here's a link that has some info on this, with most of the relevant info quoted below. You should be able to connect from another app, but not from Access itself. -- Herb

http://odbcrouter.com/OverDRIVER3.html

"Presently Microsoft's Access application has facilities for connecting to local Access-format database-files (.MDB files) as well as external ODBC data sources. Access-format database-files may also be used by other applications through Microsoft's Access ODBC Driver. However, the Microsoft Access application itself will not allow a user to open an Access-format database-file through ODBC."

"Using an ODBC ROUTER in combination with the Microsoft Access ODBC Driver, many applications on MacOS and Windows may gain multi-user client/server access to Access-format database-files; however, due to the stated restriction, Access itself must continue to map the Access-format database-files using standard Windows file-sharing techniques."

 

[ca268339]

Thanks for the tip. I wish there was a way to do this with the native ODBC/Access environemnt instead of intorducing some other "shim" into the mix.

 

[hsitz]

I guess I still don't quite understand what you're doing. And I don't know what you mean by another method of connection from Front Page introducing a "shim".

You can still connect to any .mdb from Front Page with ASP over an ODBC connection. That's as direct a connection as you're going to get. Somehow going through Access would involve Access itself acting as a shim.

If you wanted to do some development using Access itself (of designing and refining queries, for example), you could just link the tables into Access using the usual Access linking. Then switch to ODBC when you're connecting from ASP/Front Page.

-- Herb