By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,659 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,659 IT Pros & Developers. It's quick & easy.

Unlinking and relinking a back end table

P: 91
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
Expand|Select|Wrap|Line Numbers
  1. If Len(CurrentDB.TableDefs("Walks").Connect)>0 Then
  2.    DoCmd.DeleteObject acTable,"Walks"
  3. 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.
Jul 25 '17 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,430

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?

Jul 25 '17 #2

P: 91
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:
Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 25 '17 #3

Expert 100+
P: 1,430
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Declare Function apiGetComputerName Lib "kernel32" Alias _
  5.     "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  7. Function fOSMachineName() As String
  8.     'Returns the computername
  10.     Dim lngLen As Long, lngX As Long
  11.     Dim strCompName As String
  13.     lngLen = 16
  14.     strCompName = String$(lngLen, 0)
  15.     lngX = apiGetComputerName(strCompName, lngLen)
  16.     If lngX <> 0 Then
  17.         fOSMachineName = Left$(strCompName, lngLen)
  18.     Else
  19.         fOSMachineName = ""
  20.     End If
  22. End Function
  24. Function ComputerName() as String
  25.     Dim ComputerName As String
  27.     ComputerName = fOSMachineName()         ' This computer name
  29. 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 lop) 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.

Jul 26 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.