Linking Access f/e to Access b/e via ODBC

[NJS]

Does anyone know if it is possible (and how) to configure an Access f/e as
above to enable running of b/e queries as a database server (client/server)
instead of file server? I get the following error when attempting to 'Link'
to Access ODBC connection:

You cannot use ODBC to import from, export to, or link an external Microsoft
Jet or ISAM database table to your database. (Error 3423)
You are attempting to link, import data from, or export data to either an
external Microsoft Jet table or an external ISAM database table (for example,
dBASE, Microsoft® FoxPro®, Paradox, or Btrieve), but you have selected <SQL
Database> as the data source. You must select the appropriate data source for
the data you are attempting to link.

thank you

 

['69 Camaro]

Hi.

Does anyone know if it is possible (and how) to configure an Access f/e as
above to enable running of b/e queries as a database server (client/server)
instead of file server? I get the following error when attempting to 'Link'
to Access ODBC connection:

You cannot use an ODBC (Open DataBase Connectivity) driver for Microsoft
Access tables. You must use the built-in ISAM (Indexed Sequential Access
Method) driver for Jet in order to connect to the tables in the external
Access database. For more information on MDAC (Microsoft Data Access
Components), please see the following Web page:

http://safariexamples.informit.com/0789722534/3rdparty/Microsoft/PROJ2000/SYSTEM/MDACRDME.HTM#odbc

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

 

[NJS]

Thanks for the lead.
My objective was to use an ODBC connection to connect a local f/e to a
remote b/e via an IP address of a server, and execute queries on the server.
Some articles gave me the impression that a client-server Access to Access
solution is possible?

 

['69 Camaro]

Hi.

My objective was to use an ODBC connection to connect a local f/e to a
remote b/e via an IP address of a server

You don't want to use an IP address because no one using Access 2002 will be
able to use your database. They'll get the "located outside of a trusted
zone" error message. Use the UNC path instead. All versions of Access
support it.

and execute queries on the server.

One may execute a query that is located in another Access database, even if
that database file is on a Windows networked server, as long as security
settings don't prevent this.

Some articles gave me the impression that a client-server Access to Access
solution is possible?

Access is not a client/server database. It is a file based database.
There's a distinct difference. However, the Access database can be split
with the back end (tables and relationships) database file located on a
shared network server, and a copy of the front end (forms, queries, modules,
et cetera) database file located on each user's workstation to get a two-tier
database architecture.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

 

[John Nurick]

Access is not a client/server database. It is a file based database.
There's a distinct difference.

Hi Gunny,

I think it's worth distinguishing here between Access and the Jet
database engine that is usually used for Access databases.

As you know but others may not, Jet is file-based, so an Access/Jet
database is file-based too. But Access can also be used in databases
with a true client/server setup: all it takes is a server-based database
engine such as MSDE (supplied with Access), SQL Server or MySQL.

 

[Nicola Jones]

I am trying to do the same thing because I don't want to have to relink all
the tables everytime I change the databases location on the network.

Is it possible then to create an ODBC connection to use the ISAM driver
rather than the Access one offered to me when I create the DSN?

Unless anybody has any better ideas for allowing the database to be
portable? I will always have the link ed access database in the same
directory as the master one, but when I create a "normal" link to the other
access database it seems to hard code under the scenes either the drive
letter and folder or the unc path, if that is how I obtain the link.

 

['69 Camaro]

Hi, John.

I think it's worth distinguishing here between Access and the Jet
database engine that is usually used for Access databases.
Agreed.

But Access can also be used in databases
with a true client/server setup: all it takes is a server-based database
engine such as MSDE (supplied with Access), SQL Server or MySQL.

Absolutely true. Access makes an excellent front end in a client/server
setup. The OP was focusing on ODBC with Access as the back end, not some
other database, so within that context I offered some advice.

Gunny

 

['69 Camaro]

Hi, Nicola.

Is it possible then to create an ODBC connection to use the ISAM driver
rather than the Access one offered to me when I create the DSN?

If I understand your question correctly, the ISAM driver for Jet _is_ what
you're getting when you create the DSN for an Access database file. To
clarify, the ODBC Data Source Administrator can create the DSN to the Access
database file for you, but it will always use (or, as you put it, offer) the
built-in ISAM driver for Jet, because that's the best driver for the
connection to Jet's tables.

Unless anybody has any better ideas for allowing the database to be
portable?

There are many ways to connect to the back end, such as with linked tables,
DSN's or DSN-less connections. I'd suggest posting a new question yourself
so that you get a wider audience suggesting better alternatives than just
the people interested in reading about ODBC links to Access tables, which
isn't possible.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)