Exporting Table as a link (bass-akwardly....)

[michaelplogue]

OK. Here's a tricky one.

I need to find a way to export an existing table in the current DB, into a newly created DB - as a linked table (back to the current DB/Table) via VBA.

I'm familiar with the DoCmd.TransferDatabse method, but the acLink option links an existing table in an external DB into the current one. I need to do the opposite. I need to put a link into an external DB.

Is this possible?

Here's the scenario: This needs to be as transparent an as idiot-proof as possible. I will send out a master database to various offices. This DB will be placed in some folder on one of their share drives (I cannot control exactly where they will put it). When the MasterDB is first run by the local 'administrator' it will export a 'front-end' version of itself that will be e-mailed to various users to save and run from their personal directories. Some of the tables in the front-end will be for the user only, and can be exported using the standard TransferDatabase acExport option. However, the front-end will still need to connect to some tables located on the back-end/MasterDB.

The only option I can think of would be for the MasterDB to create a new table within the Front-end, that includes the full path for the MasterDB. When the Front-end opens for the first time, it will first check for the existance of this path table, then run the code to create links back to the appropiate tables in the MasterDB, then delete the path table once it's been completed.

This would certainly work, however, I was hoping I could eliminate all these extra steps if there was simpler way of doing this.

Any suggestions would be appreciated.



Thanks!

[rpeare]

That's not the only option and I don't know if you can create a link in a foreign database, but you can, create a method to refresh the link in a database with updated information. And, if the administrator at the foreign site has the location of the back end database mapped to the same drive on everyone's computer (it might even work with an absolute path \\servername\folderlist\ though I haven't tried that.

Here's an example I used on another thread of how to do it. Your administrator at the foreign site would just have to open up the front end FIRST edit the variables then close the database THEN send a copy to each user.