Backend File Swap/Rename Utility Required

[PaulO]

OK ... that sounds really good!

One more question/problem ...

Apart from the links to the backend database the program file also has links to a couple of text, picture, and/or spreadsheet files ... i.e. these files lie outside of the backend database.

Will these links be affected (I don't want them to be)? I think the answer is no?

 

[MSAccessRookie]

It looks like you have received great advice as to how to handle the issue, but I would like to add one thing that has not been discussed (at least I do not think that it has).

When the user is using the database, they might want/need to know which Members Club that you are currently supporting, so that they do not get confused. I realize that the user will log in each time they open the database, but what if they are working, and get interrupted by other things, and then receive a call related to one of the members clubs. They may or may not remember which is the current club. Some type of Club logo or perhaps a text box with the Club name or ID would make it easier to determine if logging out and starting over is required.

This is an interesting case, and I can imagine a number of different scenarios where this would be useful.

 

[PaulO]

That's another very good point ...

... there is an existing 'Club' page within the database that the user can quickly access in such instances and when the database first starts up the 'splash' screen has the club name on it to remind the user which database the user is connecting to.

 

[PaulO]

Hi David

Just copied your VB code into my program file, and amend the optional file paths to some of my own test backend databases and get the following error returned:-

Compile error : Sub or Function not defined

It highlights the Call RefreshLinks command?

Any ideas what this means ... is it anything to do with the fact that some of the links in the Linked Table Manager are to external TXT, JPG, and XLS files?

 

[namliam]

Compile error : Sub or Function not defined

It highlights the Call RefreshLinks command?

You didnt copy all the vb code, check the Modules tab of the database

 

[DCrake]

Another spoke in the wheel. Another thing not mentioned at the beginning. Are the other type of attachments club specific? if the files are hyperlinks then it should not matter as the reference to them will be in the tables them selves. However if they are totally different then further work needs doing.

 

[PaulO]

These were mentioned, belatedly, in post #21 above ...

If these are posing the problem I could perhaps link them directly to the backend file rather that the program file meaning that the program file's Linked Table Manager is ONLY looking at the Backend file?

 

[DCrake]

If there any shared files then you can link them directly to the common front end, however you may need to change the refresh links to accomodate any other issues.

 

[PaulO]

Hmmm ... I deleted the links in the frontend database to non-Access tables such that the only links are direct from the backend file.

However I get the same error message ...

Compile error : Sub or Function not defined

1) It highlights the Private Sub ComLoad_Click() command in Yellow
2) It highlights the Call RefreshLinks command in Blue

?

 

[DCrake]

If you click on the modules tab have you imported the module. To text this go to vba of the form and look for the Call RelinkTables line.

Right-click on RelinkTables and select Definition. What happens?

 

[PaulO]

I right-clicked on Call RefreshLinks, then selected Definition, it says:-

Identifier under cursor is not recognized

 

[PaulO]

Should the command be RelinkTables as per your last message or RefreshLinks as per the VB code?

 

[DCrake]

My mistake it should be RefreshLinks. Anyway by the sound of it you never copies in the module from the MDB sample Demo I gave you.

 

[PaulO]

"Anyway by the sound of it you never copies in the module from the MDB sample Demo I gave you."

Very True! ... is is contained in PaulO.mdb? Never copied a Module before ... will take a look see.

 

[PaulO]

I have copied the ModEnvironment Module across but a new error message appears:-

Compile error: User-defined type not defined

the following code is highlighted in Blue

dim dbs As DAO.Database

I sense we're almost there!?

Here's the full Module Code:-

Function RefreshLinks() As Boolean
' Refresh links to the supplied database. Return True if successful.
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim HostMDB As String

HostMDB = CurrentProject.Path & "\" & CurrentProject.Name


'Test to see if the chosen database actually resides in the specified location
If Dir(StrDatabaseToOpen) = "" Then
MsgBox "Cannot find the specified database to connect to.", vbCritical + vbOKOnly, "Connection Failed"
RefreshLinks = False
Exit Function
End If


' Loop through all tables in the database.
Set dbs = OpenDatabase(HostMDB)

For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & StrDatabaseToOpen
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True ' Relinking complete.

End Function

 

[DCrake]

You need to reference the DAO. Go to Tools > References and select
Microsoft DAO 3.6 Object Library

 

[PaulO]

David

Well what can I say ... ABSOLUTELY BRILLIANT ... to a point! I got very excited as my Club Names changed each time I ran the routine but then I couldn't find the correct members and program of events for each club.

The individual Table Links DO refresh individually, in alphanumeric sequence, until the first non-Access linked file is encountered (in this cae a TXT file). At which point no further backend table links appear to get refreshed.

Is there a workaround loop that could either

1) Ignore/skip non-backend _be files?
or
2) Restart the routine from each break point?

We're so close .... !

 

[DCrake]

You need to be looking at your naming conventions now. At the point in the code where it relinks the tables you need to use an If statement.

Here is an example of such a thing

'Define an array
Public MyLinkedTables(50) As String

Function RefreshLinks() As Boolean
' Refresh links to the supplied database. Return True if successful.
[COLOR="Red"]Call PopLinkedTablesArray[/COLOR]
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim HostMDB As String
    Dim LinkedMDB As String
    
    HostMDB = App.Path & "\DataBase\ICATSDATA.mdb"
    LinkedMDB = App.Path & "\DataBase\iCATS-" & szOrgCode & ".mdb"
    
    ' Loop through all tables in the database.
    Set dbs = OpenDatabase(HostMDB)
    
    For Each tdf In dbs.TableDefs
        ' If the table has a connect string, it's a linked table.
        If UCase(Left(tdf.Name, 3)) = "TBL" Then
            If Len(tdf.Connect) > 0 Then
                If [COLOR="Blue"]RelinkTable[/COLOR](tdf.Name) = True Then ' is it in the relinked table list
                    tdf.Connect = ";DATABASE=" & LinkedMDB
                    Err = 0
                    On Error Resume Next
                    tdf.RefreshLink         ' Relink the table.
                    If Err <> 0 Then
                        RefreshLinks = False
                        Exit Function
                    End If
                End If
            End If
        End If
    Next tdf

    RefreshLinks = True        ' Relinking complete.
    

End Function

Sub [COLOR="red"]PopLinkedTablesArray[/COLOR]()
'Populates the array with the table names I want to relink

MyLinkedTables(0) = "tblAppointments"
MyLinkedTables(1) = "TblChargeableTests"
MyLinkedTables(2) = "tblComplaints"
MyLinkedTables(3) = "tblConsultants"
MyLinkedTables(4) = "TblConversations"
MyLinkedTables(5) = "tblEventLog"
MyLinkedTables(6) = "tblFreeText"
MyLinkedTables(7) = "tblLocations"
MyLinkedTables(8) = "tblLoginSessions"
MyLinkedTables(9) = "tblMedications"
MyLinkedTables(10) = "TblOutpatientTarrifs"
MyLinkedTables(11) = "tblParameters"
MyLinkedTables(12) = "TblPatientDiagnoses"
MyLinkedTables(13) = "tblPatientExtraFields"
MyLinkedTables(14) = "TblPatientICATNotes"
MyLinkedTables(15) = "TblPatientMedications"
MyLinkedTables(16) = "TblPatientTests"
MyLinkedTables(17) = "tblPermissions"
MyLinkedTables(18) = "TblPrimeTimes"
MyLinkedTables(19) = "tblReferralLetters"
MyLinkedTables(20) = "TblReferralsMain"
MyLinkedTables(21) = "TblSystemSettings"
MyLinkedTables(22) = "TblTariffs"
MyLinkedTables(23) = "TblTaskList"
MyLinkedTables(24) = "TblTests"
MyLinkedTables(25) = "TblTriageNames"
MyLinkedTables(26) = "TblUsers"
MyLinkedTables(27) = "TblTreamtents"
MyLinkedTables(28) = "TblPatientTreatments"
MyLinkedTables(29) = "TblAssessments"

End Sub

Function [COLOR="blue"]RelinkTable[/COLOR](TName As String) As Boolean

Dim bFlag As Boolean
    bFlag = False
    For x = 0 To 26
        If MyLinkedTables(x) = TName Then
            bFlag = True
            Exit For
        End If
    Next
    RelinkTable = bFlag
    
End Function

 

[namliam]

I would suggest instead of
If Len(tdf.Connect) > 0 Then
to use
If tdf.Attributes = dbAttachedTable Then

And to only relink MDB files, perhaps something like
IF right(tdf.connect,3) = "MDB" then
??

Unless offcourse people prefer to keep ignoring me like below post

You didnt copy all the vb code, check the Modules tab of the database

Also SOME self work... The DOA problem could have been solved with a quick search of the error message on the forum or google.

 

[PaulO]

Hmmm ... here's where my poor knowledge of VB coding lets me down big time and I have to confess that I wouldn't know what to do with that code ...

I wonder whether I should consider the following options:-

1) Prefixing the names of the TXT and XLS files (there are only six in total) with "ZZZ" in that way the existing code will only fail when it gets to the links I don't want refreshed anyway?

2) Link the TXT and XLS files to the backend file and THEN through to the frontend file? In which instance, with all the linked files derived from the backend file, the routine would work as currently written?