Can access be used as a gateway of heterogenous databases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have developed an application on MS-Access and I want to expand its
functionality the following ways:

1. As number of transactions increases, I am afraid that MS-Access may not
be able to handle it in the future. So I want to split out the data to some
other "real" database server while retaining the Access front-end so that
existing program codes can be used. Is it a good idea to do so for this
purpose?

2. I want also to retrieve and modify data maintained by other external
applications from my application. Such external applications use different
database technologies: mySQL, MS-Access. I intend to use the Access front-end
to embrace all the required tables from heterogenous database as one single
database by the "Get external data --> link tables" command. That means, I
want to integrate all tables from different databases by Access. Is it a good
way to do so for this purpose?

Please advise.

Many thanks.
 
Access makes a fine front end to other DBMS. It shouldn't matter whether
your Access front-end is going against a single DBMS or multiple ones.
 
Thank you for your answer.

But if my front-end database contains tables of more than one database, is
it possible to define a transaction across several databases? That means, I
can use "begin transaction" and "commit" as if all tables are coming from the
same database.

Many thanks.
 
AFAIK, it's not possible to have a transaction across multiple databases.
This is a limitation of ODBC and the DBMS themself, though, not of Access.
 
Thank you for your help.

Could you name one or two technologies that can solve the multiple databases
problem so that I can dig further into it?

Also, if I want to upsize the Access database to MSDE, do I need a SQL
server running? If not, how I can do so because the upsizing wizard prompt me
a SQL server.

Many thanks.
 
Back
Top