Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1087
  • Last Modified:

how to open Back End exclusively from Front End database access 2007 database

Hi,

we have several fe_ databases, linked to tables in a shared be_ database.

How can just one of the fe_ database users have exclusive access to the be_ database tables, so that  user can make "bulk" changes to the table data. That is, so that no other user can access these tables whilst the bulk updates are happening.

This article goes close, but my attempt to open the be_ (shown below) made no difference, as other users could still open their fe_ database edit the tables...which is exactly what I don't want.

(I put the code in a module of the fe_  that is to have the exclusive access)

exchange.com/Microsoft/Development/MS_Access/Q_20092219.html?sfQueryTermInfo=1+10+30+back+end+exclus+open

Dim WS As Workspace
Dim DB As Database
Set WS = DBEngine(0)
Set DB = WS.OpenDatabase("G:\PropMan\RIdata\RI_be.accdb", True, True)

Any help greatly appreciated.
Thanks
0
GregCyril
Asked:
GregCyril
  • 3
  • 3
  • 2
  • +2
1 Solution
 
lee555J5Commented:
Do you have to use the frontend to make these bulk changes, or could you open the backend directly? If you can make your bulk changes directly in the backend, you can do this 3 ways:

1

Start Access with no db. Open the backend with the open dialog using the dropdown in the open dialog to open the backend exclusively.

2

Add /exclusive as a command line switch to a shortcut that opens the backend.

3

Open the backend when no one else is in it. Open one of the tables in Design view. This will lock the db until you get back out.

Lee
0
 
peter57rCommented:
The first user to open a database determines its mode - shared or exclusive - except that while there is only one user they can change between modes.  Once that is established by the first user  it can't be changed by a second or subsequent user.  Obviously(?) if a second user can get in then the open mode for both users must be Shared and neither user can get exclusive access.
0
 
peter57rCommented:
Sorry - misread the q.  This is about DATA changes yes?

The same principle applies here though - first user determines the access mode.  If you issue a code command requesting exclusive access to a table that is in use, the 'exclusive' part will just be ignored and you will get no warning.

What sort of changes are you trying to make?
Sorry to be patronising, but if you really believe you need exclusive use of a table or tables for these changes then shouldn't you be doing them when no-one else can be using the database.  Access apps are not normally 24/7 apps - is there no time when this can be scheduled?  
Otherwise you have to be first in and open the database or access the tables in exclusive mode.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<How can just one of the fe_ database users have exclusive access to the be_ database tables, so that  user can make "bulk" changes to the table data. That is, so that no other user can access these tables whilst the bulk updates are happening.>>

 I see three approaches:

1. Attempt to open Exclusive and then check if anyone else is in the DB.  If not, you can proceed with your operations (that includes the FE that your using yourself BTW which would count as a user - you can't have any open connections to the BE).

2. Do your updates in shared mode, but place them in a transaction.  Of course you'll have concurrency issues this way, but anything you touch (and get a lock on) others will not be able to use until the transaction is complete.

3. Use what I would call psuedo locking - place a flag in the app that gets checked at vairious points and if set, don't allow anyone to do anything.  For example, in a menu.  If set, anything they try to run gives them the message "Database maintenance in progress - please try again latter".

 I wrote an article on that:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_5328-Resource-locking-in-your-applications.html

  Combine that with #2 and that may give you what you want.

4. Last, note that while you cannot open a DB Exclusive once opened by other users, you can open tables exclusive with an openrecordset.  If the open suceeds, then you've got exclusive use of the table.

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Yes ... I would note that if you do bulk updates on a specific table ... and you run that in a Transaction, that will in fact lock the entire table until the transaction is complete.

mx
0
 
GregCyrilAuthor Commented:

Thanks Jim (& mx amd others). I had completely forgotten about Transactions, and never used this approach before for that matter, but it's the approach I'd like to use I think.. But, I'm missing something(s!) in my understanding of how Access conencts to data, and recordsets. To explain....

The recordset I want update is a clone of a form's data.
The form's data is based on a query. (both the form and the query def are in the fe_ database)
The query's data is based on a linked table.
The table is stored in the be_ database.

To use the TRANSACTION approach, I created a workspace object.....
--------------------------------------------------------------
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
Set rst = fRm.RecordsetClone ' (fRm object repsents an active form)
ws.BeginTrans

....looped through the rst and edit/update changes

ws.Rollback 'Rollback anyway to test
rst.Close
ws.Close
--------------------------------------------------------------
This code failed, as the table updates stuck, and didn't roll back.
So I changed the rst object to:
Set rst = CurrentDb.OpenRecordset("qryRIdata") '(qryRIdata is the record source for the form)
And rollback worked. But I want to use the cloned rst if possible.

So,
Q #1 is: How can I use the TRANSACTION approach for where the rst is a clone?

Q #2: I'm very unclear about the mode I should use to open record sets generally in fe_ be_ environment.
Given my limited skills, I want to keep things simple, so I've generally used database.openrecordset(), never used DAO, and assume ADO is the default method Acess 2007 uses
Any links to useful resources appreciated!
Q #3 can I have the link to the WhoAm1 function please Jim?

Q #4 generally, in a fe/be structure, what is the best practrice approach to meet my objective, which is:
doing deletions/additions to be_ tables (no structural changes), from a fe_ database (i.e. where the user does not open the be_ directly), whilst preventing any other users who may log in from other fe_ databases accessing those tables?
An approach I tried from my fe_ was to open a record set dbDenyWrite, then whilst that rst was opened, opened the table used by my rst on another user's fe_ ...and damn it, they could change the table data. We are in a Terminal Server environent.
And one last one without trying to push the issue! Can I have a link to the WhoAMI functin code please Jim!

Thanks so much for you help,
Greg
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<This code failed, as the table updates stuck, and didn't roll back.
So I changed the rst object to:

Set rst = CurrentDb.OpenRecordset("qryRIdata") '(qryRIdata is the record source for the form)
And rollback worked. But I want to use the cloned rst if possible.

So,
Q #1 is: How can I use the TRANSACTION approach for where the rst is a clone?
>>

   The only way to do that is for you to open a recordset within a workspace you create in the OnOpen event and then set the forms RecordSet property to that.  Here's some detail on that:

CC2000: How to Control Bound Form Transactions in Access Databases
http://support.microsoft.com/kb/248011

  But let's make sure were on the same page; that is not going to guarantee you exclusive access.  All it will do is that when you attempt the changes, they will succeed as a unit.  If another user is in there, you will get an error and need to roll back.

<<Q #2: I'm very unclear about the mode I should use to open record sets generally in fe_ be_ environment.

•Given my limited skills, I want to keep things simple, so I've generally used database.openrecordset(), never used DAO, and assume ADO is the default method Acess 2007 uses
Any links to useful resources appreciated!>>

  If you want to keep it simple, using DAO (which is recommended for JET DB's) and OpenRecordSet() is it.

<<Q #3 can I have the link to the WhoAm1 function please Jim?>>

  I'll go back and add it to the article, but here it is:

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function

  Declares go at the top of the module (standard), then paste the function in below.


<<Q #4 generally, in a fe/be structure, what is the best practrice approach to meet my objective, which is:

•doing deletions/additions to be_ tables (no structural changes), from a fe_ database (i.e. where the user does not open the be_ directly), whilst preventing any other users who may log in from other fe_ databases accessing those tables?

•An approach I tried from my fe_ was to open a record set dbDenyWrite, then whilst that rst was opened, opened the table used by my rst on another user's fe_ ...and damn it, they could change the table data. We are in a Terminal Server environent.
>>


  Hum...that last part doesn't sound right.  It you really opened a table with Denywrite, no one should have been able to change the data.  If you look at the article on resource locking, that's exactly what I do for the lock table:

Set rst = dbCur.OpenRecordset("tblResourceLocks", dbOpenDynaset, dbDenyWrite)

  But if one is doing a mass update while other users are in and you don't want to lock the table, you simply attempt the update.  If it is an all or nothing situation as far as the update or not a repeatable operation, then you use a transaction.

  Beyond that, your only option is to provide some means of kicking everyone out either with something you've built-in, using the passive shutdown feature in JET, a locking flag in the menu, or a combination of all of the above.

  Maybe if we stepped back and looked at why your attempting this bulk update while users are in, we may be able to offer a better approach.  Bit funny to that you want to do this through a form.  Normally "bulk" operations are carried out with SQL statements (queiries).
 
Jim.
0
 
GregCyrilAuthor Commented:
Thanks so much Jim...I think I understand all but the last point. (truly, thankyou for the trouble you've gone to).

dbDenyWrite:
Here's test code I've tried to help me figure out the "elephabt in the room"...
I tun this code from my fe_database.
....The msgbox causes the rst to stay open, so I leave that dialogue box unanswered whilst I log into a second (terminal server) session (as a different user), open that user's fe_databse and see if  I can edit tblRI.

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblRI", , dbReadOnly)
rst.MoveLast
MsgBox ("opened")
Set rst = Nothing

..which I always can do. That is, the tbl is not locked.

Saying all this in other words:
User A has a rst opened with dbDenyWrite set.
User B can still edit the table upon which the rst is based.

Where's the elephant!!
---------------------------------------------------------------------------------------

I'd love to be able to kick the other users out somehow whilst I do the bulk updates.
I've tried examing the laccdb file, but the users ares always "Admin", and then when all but me are logged out, the file seems to show more than one user logged in. (we're in a Terminal Server environment). So I can;ltI can't tell who the users are using this approach.

Any pointers appreciated!
---------------------------------------------------------------------------------------------
I'm using a form to do these bulk updates in the sense that there's cmd buttons on a form that runs code, and I'm trying to sync the form data with the code at various points. I guess I could use sql to do these file operations, but I've found it easier to loop through record sets to manipulate the data that way.

Thnaks for perservering Jim,

Greg
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

<<User B can still edit the table upon which the rst is based.>>

  Don't think so...you opened it read only<g>

Set rst = CurrentDb.OpenRecordset("tblRI", , dbReadOnly)

  I think if you try an actual edit, you'll get a lock error.   But no need for even opening a recordset, just open the linked table directly and try editing a record.  You should not be able to.

  One other thing; you need to be careful with optional arguments.  Often if you skip one, the rest will be skipped.  You should supply a value for every argument or use named arguments.  That may be part of your problem.

<<I'd love to be able to kick the other users out somehow whilst I do the bulk updates.
I've tried examing the laccdb file, but the users ares always "Admin", and then when all but me are logged out, the file seems to show more than one user logged in. (we're in a Terminal Server environment). So I can;ltI can't tell who the users are using this approach.>>

  First, to get an accurate user count, you need to use the JET ShowRoster function:

How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access
http://support.microsoft.com/kb/285822

  Looking at the LDB file directly doesn't help because you can't see the corresponding user lock that's being held, which indicates if the user is actually connected or not (the LDB can list users not currently connected).

  Second, using that method, you need to make sure your looking at the BE and not the FE.

  Finially, as you have seen, unless your using ULS (User Level Security), every user is listed as "ADMIN", which is the default JET login.

  So forget all that<g>.

  What many do is keep a "logged in user table".  Typically you'll see something like this at app startup:

CurrentDb.Execute "INSERT INTO tblUsers (UserName, ComputerName) VALUES ('" & strUserName & "','" & strMachineName & "')"

 and this at app shutdown:

CurrentDb.Execute "DELETE FROM tblUsers WHERE UserName = '" & strUserName & "'"

  along with the calls from the WhoAmI() procedure I gave you, then you really know who's in.  Then it's just a matter of doing a Dcount() on the table to see if your the only user in.

  But really that's what the resource locking article was all about and fact I even mentioned keeping a "user lock" at one point in the article.

  So now you know who is in, but how do you kick them out?  

  First, set a flag which is checked at startup which forces a quit (place an 'app wide' resource lock).  This prevents new users from coming in and if you kick someone out, from coming back in.

  Second, within the app open a hidden form at startup.  Set the forms timer for xx seconds (typically 60) and when the timer event fires, have it look for the lock flag.  If found, you can message the user, force a shutdown, etc; it's up to you what you want to have happen.

<<I'm using a form to do these bulk updates in the sense that there's cmd buttons on a form that runs code, and I'm trying to sync the form data with the code at various points. I guess I could use sql to do these file operations, but I've found it easier to loop through record sets to manipulate the data that way.>>

  So there's no real need to boot everyone out; you just need to make sure the update completes, which means a transaction.

Jim.





0
 
GregCyrilAuthor Commented:
Jim is terrific. Thankyou!
Greg
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now