Testing for the presence of linked tables

[paulhorn]

I hope somebody out there can point me in the right direction.
Background:
I have implemented a split database for a group of users who are non Access literate. The back end contains the data tables, and the front end all the queries, forms, and macros. My problem is that their requirements are evolving, necessitating fairly frequent additions/modifications to the front end, reloading it and re-linking to the back end tables. Because the users are remote from my location, and with no hope of gaining on line access to do this across a network for them, I have to physically take the upgraded front end to them, and re-link the back end.

I envisage a command button on the start up form which becomes visible only if (after suitable testing) the linked tables are not present. THe user is then invited to re-establish the links by pressing the button (which houses a macro to complete the task.

Does this make sense?

My question is;
Is this possible and what sort of code would I need if it is ? (Or is there a more effective wayof achieving this?)

Thanks for any input ray:

 

[Corticus]

Hi paulhorn,

Here's the code I use:

Public Sub relink()

    On Error Resume Next
    
    slashPos = InStrRev(CurrentDb.Name, "\")

    dbPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - slashPos)
    dbName = "YOUR DATA FILE NAME HERE"
        
    Set db = CurrentDb()

    For Each td In db.TableDefs
        If td.Connect <> "" Then
            [REFERENCE RELINK BUTTON HERE].Visible = True
            td.Connect = ";DATABASE=" & dbPath & dbName
            td.RefreshLink
        End If
    Next td
    
End Sub

Hope this helps,

 

[paulhorn]

Wow,
Thanks Corticus. I'll give it a try tonite!!

 

[paulhorn]

Hi Corticus,
I had a go at using your code last night - but failed!
I get a compile error where the 'InStrRev' statement is highlighted?

I have put the code into the global module - is this correct? I have also only changed the dbName to the required backend database, and not added any path info (the backend sits on a network drive).

I am using Access 97 if this is relevant

Any pointers gratefully appreciated - as you will have realised I do not possess a great deal of coding knowledge/epertise!

Thanks

 

[Corticus]

I don't believe Access 97 has the InStr() function. Just define it like so:

Function InStrRev(str$, chr) As Integer

    ct = 1

    For I = 1 To Len(str)
        If Left(Right(str, I), 1) = chr Then
            InStrRev = ct - 1
            Exit Function
        End If
        ct = ct + 1
    Next I

End Function

Remember, the only point of that code is to locate where your data file is, you can adjust to suit. The real heart of the code is the loop, once your db is located and defined, that will work.

 

[paulhorn]

Corticus,
Thanks again for your input - it is really appreciated. I'll have another go tonite ray:

 

[Corticus]

I just noticed something on this.

The following code should be run when the startup form is opened:

    On Error Resume Next 
    
    slashPos = InStrRev(CurrentDb.Name, "\") 

    dbPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - slashPos) 
    dbName = "YOUR DATA FILE NAME HERE" 
        
    Set db = CurrentDb() 

    For Each td In db.TableDefs 
        If td.Connect <> "" Then 
            [REFERENCE RELINK BUTTON HERE].Visible = True 
        End If
    Next td

The button should execute a sub, 'Relink' defined as follows:

Public Sub relink() 

    On Error Resume Next 
    
    slashPos = InStrRev(CurrentDb.Name, "\") 

    dbPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - slashPos) 
    dbName = "YOUR DATA FILE NAME HERE" 
        
    Set db = CurrentDb() 

    For Each td In db.TableDefs 
        If td.Connect <> "" Then 
            td.Connect = ";DATABASE=" & dbPath & dbName 
            td.RefreshLink 
        End If 
    Next td 
    
End Sub

So first the tables links are tested to make the button visible or not, then if clicked, the relink sub is run.

 

[paulhorn]

Thanks for the revision - I was just logging on to ask if I was being stupid because I could not get it to execute (found I had to declare several bits as variables just to clear the unknown variable errors).

THanks again - I'll give it a go tonite

 

[paulhorn]

Hi Corticus - Well I modified the code as per your last post and..........now I know I must be stupid. I still have a compile error (slashpos not defined as a variable). I tried declaring it as a text variable, but to no avail. Any ideas? - (Is it related to my use of Access 97)
Sorry to be a pain - tho' the upside for me at least is that I am still learning!

Thanks for your perseverence!

Paul

 

[Corticus]

You must have 'option explicit' set in your declarations, which means all your variables must be dimensioned.

SlashPos is an integer type variable, not text, so try changing that.