How to programmatically re-link all tables from BE Database

[lmarconi]

Good morning all.
I would like to know if someone can kindly suggest me how programmatically re-link tables in a front-end database from the back-end database.


The idea is to create a function to be used in the Autoexec Macro (at start-up) with the following steps:
1. When the database is open, the system should check if any link table is present. If tables are link, open the database, if not, go to the step 2.
2. The system re-link all tables from a given path (will never change)
The reason why I ask this is because i's going to manage the database remotely so, any new revision will be without any linked tables (because i don't have the way to do it remotely) and the link will be done from the user at the first log on.
Thank you in advance for kindly and usual support, any other suggestion is more then welcome.
Cheers.
L.

[CJ_London]

you need to clarify - you said 're-link tables in a front-end database from the back-end database.' and When the database is open, the system should check if any link table is present. If tables are link, open the database, if not, go to the step 2.
which database? FE or BE?

If I understand correctly, you do not have access to your clients network, so you a sending out via email/whatever a revised FE? or a revised FE and BE? and the client needs to relink?

[lmarconi]

you need to clarify - you said 're-link tables in a front-end database from the back-end database.' and When the database is open, the system should check if any link table is present. If tables are link, open the database, if not, go to the step 2.which database? FE or BE?

If I understand correctly, you do not have access to your clients network, so you a sending out via email/whatever a revised FE? or a revised FE and BE? and the client needs to relink?

You understand correct, and thanks for answer. When (and if) any modification is required, I will work with the interface only (FE). Then I will send it them back, without linked tables because, as you said, i don't have access to their network and to be sure it use the tables in the BE database (the BE has only tables and is locate in their network server) , i need this process, so to link the BE tables (from their server network) to the FE database i sent with the modification. Hope it is more clear.
Cheers.

[Micron]

Does your back end have a table that contains the path(s) for all tables that would need to be linked? That's where I'd get the paths from then run through all linked tables and relink them. That can also ensure that no one copies a db to their desktop since the db path won't match what's in the table - assuming they can't get at them and modify the path values. One issue could be that users don't have the same drive letter, so you probably should be using UNC paths.

[CJ_London]

Suggest tell the client to put the FE in the same folder as the BE. The path to the BE will be the same as the FE (currentproject.path) - you can then have code to relink the tables. As Micron suggests, recommend convert the path to UNC before relinking.

After that, all depends on how you (or your client) are planning to update individual users, but a good idea to include code in the FE that closes the app if the path is in shared folder to avoid the risk of users using the copy on the server - obviously to run once tables have been relinked

[Gicu]

Here is how I used to do that, no need to remove the tables when you send back the updated FE; have a settings table in your FE where you save the connection string for the live BE on the client site and the one for your development BE. Same table has a yes\no field to initiate the change and a "target" field (LIVE or TEST).
You just need to call the custom VBA function from the very first line of the AutoExec macro (if you have a form set as opening form remove that and do it in the AutoExec after you re-link because if the form is bound it will interfere with the re-linking).

See the attached sample for all objects required.

Cheers,

[Gicu]

Sorry, forgot to mention that if you want to see how the connection strings look like open a linked table in design view and open the Properties window.
This link should also help:
https://www.connectionstrings.com/access/

Cheers,

[lmarconi]

Here is how I used to do that, no need to remove the tables when you send back the updated FE; have a settings table in your FE where you save the connection string for the live BE on the client site and the one for your development BE. Same table has a yes\no field to initiate the change and a "target" field (LIVE or TEST).
You just need to call the custom VBA function from the very first line of the AutoExec macro (if you have a form set as opening form remove that and do it in the AutoExec after you re-link because if the form is bound it will interfere with the re-linking).

See the attached sample for all objects required.

Cheers,

Thank you all for kindly suggestion.
First i would like to clarify that the User FE database don't use any Drive but the network path and i did exactly to avoid problem of drive letter as mentioned by Micron.

I used the Gicu's script and looks like the source doesn't change so i would like to understand if i did some mistake. In the setting table i use:
ConnetcionStringLive = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\musnas04\cpd_projects\CTO_OR\05- M&I\05-M&I Support\QAQC Supportive Documents\_QAQC Tool\Database\BE\ARDC-BE-R1.mdb;" (which is the user BE database in their network and which is the one i need to reconnect)
ConnectionStringTest = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Scratch\C Data\Desktop\QA -QC tools\Main DB\R5.8\be\ARDC-BE-R1.mdb;Persist Security Info=False;" (which is my BE database in my local disk)
ConnectTo = "Live"
ChangeSource = "True"

Then i use the Macro Autoexec with the function "vcChangeSource()"
When the autoexec start, it seams change the source (system also give message that source is changed) but the tables are linked always to my BE "ConnectionStringTest".

Where i did mistake?
Cheers,

[Gicu]

Let me have a look and I'll get back to you, need to check the connection string formats.

Cheers,

[Gicu]

Please try these connection strings:
Live:
;DATABASE=\\musnas04\cpd_projects\CTO_OR\05- M&I\05-M&I Support\QAQC Supportive Documents\_QAQC Tool\Database\BE\ARDC-BE-R1.mdb;TABLE=

Test:
;DATABASE= C:\Scratch\C Data\Desktop\QA -QC tools\Main DB\R5.8\be\ARDC-BE-R1.mdb;TABLE=

And replace the vcLinkTableDefs() function with this updated one (it concatenates the source table name to the strings in the settings table):

Public Function vcLinkTableDefs()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strNewConnectionString As String, sSourceTable As String


On Error Resume Next


 strNewConnectionString = IIf(DLookup("ConnectTo", "tblSettings") = "Test", DLookup("[ConnectionStringTest]", "[tblSettings]"), DLookup("[ConnectionStringLive]", "[tblSettings]"))
  Set dbs = CurrentDb()


  ' Loop through TableDefs collection, only processing
  ' the table if it already has a Connection property.
  ' (all other tables are local ... not linked)


  For Each tdf In dbs.TableDefs


    If tdf.Connect <> "" Then
        sSourceTable = tdf.SourceTableName
        If tdf.Connect <> strNewConnectionString Then
            tdf.Connect = strNewConnectionString & sSourceTable
            tdf.RefreshLink
        End If
    End If
  Next
 
 Set tdf = Nothing
 Set dbs = Nothing
End Function

Cheers,

[lmarconi]

Good morning and thanks you first of all.
It works now for the "Live" string, so it change if i have Test BE to Live BE but not work to the contrary, so if i have "Live" BE and want to change to "Test", string not change.
I used the string you suggested and the new function.
Any further suggestion? This could be not a big issue because the user need to use the "Live" so i could link the "Test" manually in my PC but, as you did so good function, it is interesting to know why it not work for "Test".
Cheers,

[Gicu]

Can you try an alternate path in your C:\ drive (maybe with no spaces in the path) ?

Cheers,

[lmarconi]

I tried: ;DATABASE= C:\Scratch\be\ARDC-BE-R1.mdb;TABLE=
But is the same, don't change.
Cheers,