modify links

[idxyz]

hi,
I have a sub-form which is bound to a query. I like before the query runs, be able to modify the table links?. Tried different things to accomplish this in expression builder, did not work. Used sub-form's open handler, did not work. Is this possible? if yes, how?.

 

[DHookom]

What exactly do you mean by “be able to modify the table links”?

 

[CJ_London]

Perhaps just a case of changing the subform linkchild and link master properties?

 

[sonic8]

Used sub-form's open handler, did not work.

What exactly did you try and why exactly did it fail? - It should work, if done right.

 

[nokidding]

hi,
I have a sub-form which is bound to a query. I like before the query runs, be able to modify the table links?. Tried different things to accomplish this in expression builder, did not work. Used sub-form's open handler, did not work. Is this possible? if yes, how?.













Living in the United Kingdom I started to think about setting up my own company in Switzerland and I found a very useful article about opening a on this site. This article gave me a lot of new and useful information about this topic and so I was happy to recommend this service to you as well.

Yep, it’s possible—just a bit tricky in Access. You can’t really change table links directly in the expression builder or the subform’s Open event because by then Access is already trying to run the query.


Instead, try modifying the table links before the subform loads—ideally in the main form’s Load or Open event. That way, you can adjust the links (using TableDefs) before Access hits the query.


Example:


CurrentDb.TableDefs("YourLinkedTable").Connect = "YourNewConnectionString"<br>CurrentDb.TableDefs("YourLinkedTable").RefreshLink<br>

Make sure you reassign and refresh before the subform is loaded. Let me know if you want help with the actual connection string.

 

[CJ_London]

Instead, try modifying the table links before the subform loads—ideally in the main form’s Load or Open event.

You do know that subforms open before the main form?

 

[sonic8]

You can’t really change table links directly in the expression builder or the subform’s Open event because by then Access is already trying to run the query.

In a (sub)form's Open event the Recordsource was not touched in any way yet.

Instead, try modifying the table links before the subform loads—ideally in the main form’s Load or Open event.

I suggest you verify your statement about the sequence of events. - You might be in for a counter-intuitive surprise.

 

[CJ_London]

Until the OP answers the question in post #2 we are all just wasting our time guessing

 

[idxyz]

hi,
my main form's subform is bound to a query. Query retrieves data from some remote tables. I like to be able to modify the links so that back end tables come from a different location. I have remote1 and remote2. I like to be able to relink the tables before the query that subform is bound to runs.

 

[sonic8]

Unless the query or tables in question are also used by another open form at the same time, it should work to set the Connect property of the linked tables to the other remote database.

You could also (re)write the query SQL to contain a direct reference to the remote table using the IN syntax. E.g.:

SELECT *
FROM YourTable IN 'C:\path\to\database.accdb';

 

[LarryE]

If you want to select a specific file to connect to, you can make sure your references include the Office 16.0 Object Library as below:

And then call the Public Function as shown below to select and connect to a new backend file:

Public Function ConnectFileDialog()
On Error GoTo ConnectFileDialog_Error
Dim ConnectDataFileDialog As FileDialog
Set ConnectDataFileDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim SelectedFile As Variant
Dim db As DAO.Database
Dim TDef As DAO.TableDef
Dim BackEnd As String
Set db = CurrentDb
With ConnectDataFileDialog
    .AllowMultiSelect = False
    .Title = "Select A Backend File"
    .ButtonName = "Connect"
    .Filters.Clear
    .Filters.Add "Access Files", "*.accdb", 1
    .FilterIndex = 1
    If .Show = -1 Then 'If user selected a file
        For Each SelectedFile In .SelectedItems
            'MsgBox SelectedFile
            BackEnd = ";Database=" & SelectedFile & ""
            If Len(BackEnd) > 1 Then
                For Each TDef In db.TableDefs
                    If Len(Left$(TDef.Connect, 1)) > 0 Then
                        Set TDef = db.TableDefs(TDef.Name)
                            TDef.Connect = BackEnd
                            TDef.RefreshLink
                    End If
                Next TDef
            End If
        Next 'Selected File
    End If
End With
Exit Function
ConnectFileDialog_Error:
DoCmd.CancelEvent
MsgBox Err.Description
Resume Next
Exit Function
End Function

You can call the Public Function whenever you wish to change the connection to a normal ACCESS file.