Db multiple access internal connection

Question

I am redesigning an ASP.NET CMS application that I really dislike. I made some performance improvements just to find that this CMS not only uses MS SQL, but some users "just" use the MS Access database.

The problem is that I have several tables that I use for internal joins that are in two different files with the MS Access version. I am not allowed to just move tables to another mdb file.

Now, I'm trying to find a good way to “internally connect” through multiple db access files?

It would be a pity if I got all the data and did it programmatically!

thank

Answer 1

If you have access to MDB and you can change them, you can use Linked Tables. Access provides the ability to bind to external data (in other MDBs, in Excel files, even in SQL Server or Oracle), and then you can perform your joins by reference.

I would highly recommend such performance testing. If it is possible to transfer Access database users to another system (even SQL Express), this would also be preferable - the last time I checked, there are no more 64-bit JET drivers for ODBC, so if the application is ever hosted in 64 in a bit environment these users will be closed.

Answer 2

. MDB, . "IN" c:\MyDBs\Access.mdb " FROM SQL. :

SELECT MyTable.*
FROM MyTable IN 'c:\MyDBs\Access.mdb'

:

SELECT OtherTable.*
FROM OtherTable IN 'c:\MyDBs\Other.mdb'

, .

SQL, MDB FROM, :

SELECT MyTable.ID, OtherTable.OtherField
FROM [c:\MyDBs\Access.mdb].MyTable 
  INNER JOIN [c:\MyDBs\Other.mdb].OtherTable ON MyTable.ID = OtherTable.ID

:

Jet ( , , ), ( , , ). .

Answer 3

" ", . ( ) , .

, , ,

Answer 4

Inside Access, you can add remote tables through the "Linked Table Manager". You can add links to one access file or another, or create a new Access file that references tables in both files. After that, the inner join requests are no different from what they do in the same database.