Unlinking and relinking a back end table

[Petrol]

In a multi-user environment I want a user to be able to unlink a table from the back end so he can make temporary changes to the table without interfering with other users. But most of the time the table should stay linked so he can get the latest data.

I figured I can unlink the table with

If Len(CurrentDB.TableDefs("Walks").Connect)>0 Then
   DoCmd.DeleteObject acTable,"Walks"
End If
 

After fiddling with the table the user will log out and any changes he makes can be discarded. But I suspect I'll have to re-link the table before closing the DB so that he'll get the latest version next time he opens it. This is a bit of a challenge - most of the "Relink" stuff I've seen on the net assumes the table is currently linked and just needs to be shifted to a different BE table. Will the Relink code work if the table is not currently linked at all? I've also seen code which loops through all the tables in the application and links them, but I'm not quite sure how to apply it to just the "Walks" table.
Any suggestions will be much appreciated.

[PhilOfWalton]

Peter.

That is a really bad idea. Users should NEVER have access to tables in design view especially in a multi-user environment.

Think what happens if they delete a field. I certainly wouldn't know how to recover the data.

Why on earth do you want to do this?

Phil

[Petrol]

No, I wasn't thinking of giving him access to a table in design view. I want to be able to programmatically disassociate his copy from the master copy that others access.

There are several forms and reports which can update or display information about a particular Walk. The Registrar will need to work on one forthcoming Walk over a period of weeks, and doesn't want to have to keep re-specifying which Walk every time. So the Walks table contains a field indicating which of the 100 or so Walks is currently selected for subsequent forms or reports to act on.

But other people can have access to just one of those reports, which displays information about the selected Walk. Unfortunately I can't give him a read-only access to the database - Access doesn't work that way - so I give him access to only that report. However he also needs to be able to select the Walk he wants to report on. That involves setting the "Selected" field for that Walk in the Walks table. But I don't want that to muck up what the Registrar or others may be doing with other Walks.

Apart from the "Selected" field, the Walks table only rarely changes, so the restricted user doesn't need it to be updated in real time. Since each user has their own copy of the FE on their own computer, which accesses a common copy of the BE, the simplest solution seems to be for the restricted user to drop the link to the Walks table so he can select the Walk on his own machine. I just want the "Select Walk" facility to quietly unlink his table - he won't even know about it.

Actually, now that I come to think about it, I probably don't need to relink the table when he closes the DB. The batch file on each user's computer which invokes the FE actually copies it afresh from the cloud each time, so that when I deploy a new version everybody will get it. So they'll all get a fresh set of linked tables anyway. It just seemed aesthetically wrong to leave the table unlinked. What do you think?

I can easily pick up the Connect string from one of the other tables that never gets unlinked:

CurrentDB.TableDefs("Walks").Connect = CurrentDB.TableDefs("Talks").Connect

All I needed was a command to reverse the effect of the DeleteObject command. But perhaps I don't need it after all?

[PhilOfWalton]

A much better solution would be to set up a user table with an indicator as to whether that user has edit or read only permissions.
Then set up a JoinUserWalk table with UserID, WalkID as combined primary key and Selected as a yes/no field. This allows any user a limited no of walks he has access to, and by default will bring up only the Walk that user is interesed in.

If you don't want a user to log in, you can use the computer name instead.

Option Compare Database
Option Explicit
 
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSMachineName() As String
    'Returns the computername
 
    Dim lngLen As Long, lngX As Long
    Dim strCompName As String
 
    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        fOSMachineName = Left$(strCompName, lngLen)
    Else
        fOSMachineName = ""
    End If
 
End Function
 
Function ComputerName() as String
    Dim ComputerName As String
 
    ComputerName = fOSMachineName()         ' This computer name
 
Exit Function
 

So the main form for the walks also has the 2 new tables in the RecordSource, and if the user does not have edit privileges, then lock all the controls (text box, combo & list box using a loóp) then unlock your Combo Box to select a different Walk.

A bit of code to set the Selected indicator for that user in the JoinUserWalk table and unset the indicator for all the other walks for that User only.

Phil