Unless ADO allows you to create a linked table in one of the MDBs,
you're hosed if you don't want to do it all yourself. Air-code:
It doesn't. You can't modify the contents of the MSys~ tables. If only you
could, you could dynamically change linked tables, which would be a
beautiful feature of Access. But they don't *want* you to stick with Access
do they!
The only other alternative is to use an SQL Server DTS (before you start
jumping up and down that you haven't got SQL server, read on...):
If you DO have SQL Server - then just use Enterprise manager to design a
package with two Access connections. It will be faster than Joe's code
(which without DTS is the only method of doing it) and it doesn't matter one
bit that no SQL server databases are involved - some companies apparently
even buy SQL server even if they're not going to use it, just in order to
gain DTS capability.
If you haven't got SQL Server or they're 'too far away' on the network -
then you can create or get someone to send you (I could for one) the module
which SQL server outputs when you save a DTS as a Visual Basic module, then
you can look at the structure of it and modify it to fit your requirements.
You might have to dig around a bit for data type codes, although a good
sample would include a datatype of each field, like my_long, my_varchar,
etc.
You'd then just need to plonk it into your app and set a reference to
Microsoft DTSPackage object library (although you might need MSDE in order
to gain that DLL, but you can download MSDE for free).
