Can access be used as a gateway of heterogenous databases

[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.

 

[Douglas J. Steele]

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.

 

[Guest]

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.

 

[Douglas J. Steele]

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.

 

[Guest]

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.