(Re)link tables for multiple front end databases at same time

[Mihail]

Hi !

Scenario:
3 front end databases based on the same back end database.

All front end parts are in the same folder (=> I know their paths) and have the same structure of linked tables.
One OR two OR all can be unlinked (at a tme) to the back end part.

What I know:
To relink a front end part to a back end part via VBA (no matter what path has the back end part).

What I have:
A very simple wizard to (re)link tables.
At this time this "wizard" is implemented in all three front end parts (I think that is not the best idea while I can design only once and use it from where I need, but I'll see later about)

What I wish to do:
When I run my wizard (from one of the front end part), after I (re)link tables (so I can save the path for the back end part), automatically do the same job for the other two front end parts.
The best will be WITHOUT open the other front end parts (to avoid run any code in this parts). If can't be avoid to open it I think I can develop code to skip the code which normally running when open it). Of course I need other code to close it after the wizard do the job.

Hope you understand what I mean.

Using other words, the title of this thread can be "How to run code from one database to another one (without open the second one)"

Thank you !

[NeoPa]

There is a DAO method called OpenDatabase() which allows you to open extra databases. Unlike OpenCurrentData base(), it doesn't require that the current database is closed to do it. I imagine you can work the rest out from there.

[Mihail]

Thank you !

[NeoPa]

Always a pleasure to help Mihail :-)

[ADezii]

This is how I interpret your Request:

1. Open the External Database (FE) as defined by the Constant conEXTERNAL_DB within the context of the Active Database.
2. The Table to be Linked may already exist in the External Database (FE), so DELETE it.
3. From the context of the External Database conEXTERNAL_DB, Import the Table as defined by the Constant conTABLE_TO_LIN K from the Back-end Database defined by conDB_TO_LINK.
4. The following Code will Open the 'C:\Dezii\DB1.m db' Database (simulated Front End), then import the Employees Table from the "C:\Dezii\North wind.mdb" Database (simulated Back End) into it.
5. This Code has been tested, and is fully operational.
   

   Dim appAccess As Access.Application
   Const conEXTERNAL_DB As String = "C:\Dezii\DB1.mdb"
   Const conDB_TO_LINK As String = "C:\Dezii\Northwind.mdb"
   Const conTABLE_TO_LINK As String = "Employees"
   
   Set appAccess = CreateObject("Access.Application")
   
   With appAccess    'Represents Access Front End Application
     .OpenCurrentDatabase conEXTERNAL_DB
     .DBEngine.Workspaces(0).Databases(0).TableDefs.Delete conTABLE_TO_LINK
     .DoCmd.TransferDatabase acLink, "Microsoft Access", conDB_TO_LINK, acTable, _
                                    conTABLE_TO_LINK, conTABLE_TO_LINK, False
   End With
   
   appAccess.Quit
   Set appAccess = Nothing

[Mihail]

Thank you very much ADezii.

I don't try yet but, after first view, I think that it is exactly what I am looking for.

I am almost sure that I return here only to choose the best answer :).

[Mihail]

Hi again.

ADezii, your code work as I expect: very well.

But I wish to add more generality by looping through all FE tables or, for a full generality, through all FE linked objects.
I don't know how to "see" all this objects (I suspect that must be a collection somewhere. But where is it and how to extract it's items ?).
Can you point me to the right direction ?

Thank you !

[Mihail]

Oh. Forget me, please. Sorry for that.

I see the solution in your code ADezii: TableDef. I think is the same for QueryDef.

However I can't find something similar for Reports.
Any advice ?

[NeoPa]

Mihail:
Using other words, the title of this thread can be "How to run code from one database to another one (without open the second one)"

I'm confused. I thought this was the question, which has already been answered.

@ADezii
I'm also confused that you would open a new version of Access to handle this when OpenDatabase() is available for use within the same session.
Also, the approach to delete and then recreate is not necessary (and can involve the loss of meta-data information) as there is already an article which deals with repointing and refreshing existing links. I only didn't include that because Mihail indicated he already knew how to handle that aspect of the question. For interest though, it's Relinking ODBC Tables using VBA and it includes relinking Access tables too.

I have no idea at this stage, why accessing reports comes into the scope of this question, but they can be found as the CurrentProject. AllReports() collection.

This all seems very strange to me, but I'm sure you two can sort things out between you. Please be careful of changing topics though, within the thread.

[Mihail]

Sorry, NeoPa.

The title of the thread indeed is how to (re)link tables. I ask only that because I can't see far away at that moment. I know that you are able to change a little bit the title: "How to (Re)link front end databases to the back end databases" to give as more generality to this thread as ADezii (not me) can offer. Doing that you will eliminate the risk to become "out of subject".

Thank you !

[NeoPa]

No Mihail. I see you seem to be much confused by this. We are not looking to change the question to fit the answers. That would be somewhat ridiculous and of no help to those looking for a clear answer to a clear and similar question. The question is the question, and if a new question is required then you post a new one.

Rules are not invented purely to make your life less simple. They are there to ensure work done in these threads are not worthless to everyone else looking for similar solutions. Answers should match the question and be clearly posted in the thread. Attachments can be of value to enable users to experiment and learn to work with the ideas, but they are not a substitute for the answer - purely an addendum to one if posted.

There is always some leeway allowed for issues which are related, but when you get to a stage where a moderator (myself in this case) cannot even see the relation between the question and new posts, then it's gone too far off-topic.

[ADezii]

I'm also confused that you would open a new version of Access to handle this when OpenDatabase() is available for use within the same session.

The OpenDatabase() Method returns a Reference to the Database object that represents it. This, unfortunately, this is not enough since we need a Reference to an Access Application Object in order to dynamically Link from an External Database (FE) to a Table in yet another External Database (BE). This is all being done within the context of the Current Database, which is not either the FE or BE DBs. The mechanism that is needed, as I see it, is:

<Access Application Object>.DoCmd.TransferDatabase()

P.S. - I can't see how this can be accomplished with OpenDatabase(), but if you can show me how this can be done, I would be very interested in seeing it.

[ADezii]

Also, the approach to delete and then recreate is not necessary (and can involve the loss of meta-data information) as there is already an article which deals with repointing and refreshing existing links.

We are not talking about Linking from the Current Database, but from an External Database, referenced from the Current Database. Unless I am incorrect, these are 2 vastly different scenarios.

[NeoPa]

Well then, Sir ADezii of Philadelphia, As you've thrown down the gauntlet I must take up the challenge. It's not something I've done before, so it may take some time, but when I looked (to check it out for this thread) the pieces all seemed to fit together sensibly.

I'll see if I can knock up a scenario with four databases in the same foldere where :

A.MDB contains the code.
B.MDB has a table that initially starts as connected to C.MDB, but is changed, by the code in A.MDB such that the table links instead to D.MDB.

That is the challenge as I see it and I'll post back when I have an answer for you.

[ADezii]

@NeoPa:
C.MDB, as I see it, is irrelevant, since we are not at all concerned with the Original Link, being it will be Deleted anyway based solely on the Object Name (Table). Good luck on the gauntlet. BTW, when was the last time you gauntleted (is that a word!)?