Modules & VBA :: Record Locking Multi-users Over Network Split Database / Access 2007
Aug 5, 2015
I have designed a database which I intend to split for multi-users to access from one front end icon on a shared folder at work. I have designed a form bound to cmr record table and on it a subform to another table where cmrs activities will be saved. The form has buttons to and blank controls.
User can create a new activity entry by typing into the blank controls and pressing the save button which saves to the activity table. the edit button extracts a selected records details (selected on the subform) from the activity table and populates the blank field for a user to edit and then hit save to save changes. The delete button deletes a selected record from the activity table. I intend to have multi-users either accessing, viewing and a possibly editing the same customer at the same time. The simultaneous viewing is essential but the simultaneous editing, though not desired is inevitably going to occur.
What I would like to know is:
1. Can you lock an individual record in a table or does the whole table have to be locked. E.g If Colleague 1 is editing Cmr A's record in Table1 can he lock it so Colleague 2 can view and edit Cmr B's record in Table1
2.Can Colleague 1 access/read Cmr A's record in Table1 to retrieve details toe the form controls if Colleague 2 is viewing or editing Cmr A's record in Table1
3. If record lock is possible, how can I initiate it in my example code below.Edit activity record Code
Code:
Private Sub Edit_A_Click()
'Get Data to text box control
With Me.R_P_Data_P_Subfrm.Form
Me.txtrID = !rID
Me.txtrID.Tag = !rID
Me.txtrefNo = !refNo
Me.cmbrpc = !rPC
I am the administrator for a 2010 Access Client Database that consists of many clients with their information and we have three users whom go in and edit-add information to this Database and it hasn't been a problem until two users have tried to edit the same client record at the same time and then we have had some issues...
So, not sure this is even possible, but can one prevent more than one user being on the same client record? Is there a way to have a message come and say this record is in use?
I've got a database in Access 2007 that keeps track of client data for work. It's been working fine for about a month, and suddenly today other users can't change any information because the records are suddenly locked when accessed from their accounts.
I've already checked that they can't update forms, tables, nothing.
I've checked the database properties, it defaults to shared with no locks.
I've checked the properties of my forms - no locks.
I've double checked the permissions to the folder that the database is in - no restrictions.
Bottom line I'm in bit of a pickle, work has moved forward the migration of Office97/NT4 to Office2003/XP by 2 months (clever lot) and was hoping not have to cram for this question until at least 3-5 weeks.
I'm looking for pointers/suggestions because I now have to test in Access2003 Runtime on MONDAY!!!!
What it is, is:
I have a query which is a list of items that need to be worked out of 65,000. Of that 65,000, 20,000 end up in the query (Actually do need looking at after an Automated process) (it's adapting the query/process I think I need)
Query:
EntID - UniqueID for a household Applicant1 - Number lookup value for Applicant 1 Applicant2 - Number lookup value for Applicant 2 Qualifies - 1 = Yes, 2 = No, Null = Not worked.
That's basic building block of the Query which is Drives the main Form.
What I would like is a scenario similar to this:
Person A opens record 1
Person B opens record 2
Person C opens record 3
Person C finishes record 3
Person B finishes record 2
Person C opens record 4
Person B opens record 5 (he/she took a little break)
Now that's the way I would like it to work, but there will be other factors and this is where I'm all ears for anyone who is used to this type of system.
The problem I can't figure out is that the Main Form has several subforms, but none of the data is to be edited, it is there for visual purposes only. The users will be creating records via the Main Form through code, but not directly into any tables/queries with which I can use conventional record locking, that I can see.
My thoughts are that Person A calls up a record based on Min EntID and somehow locks it so the Person B looks for Min EntID Where not locked.
Please, please can someone point me in the direction of how to do it and more importantly the correct order of events? I have tried doing the Min EntID and locking the record, but while Person A is running the Min Query, Person B is running it also so they end up with the same record as B has the record on screen while A is locking it.
Also, In the real world Person B might open record 2 and think, nah I can't be bothered. I would like a proffessional opnion on whether C opens record 2 or should B be made to deal with it.
I think not given the huge time constrants landed square on my lap, I would get there with smaller questions, but I've spent the last 10hrs writing a Function, to get it ready for the testing lab, where the owner has changed the requirements 7 times and my head isn't working.
All or some help given will be GREATLY received. Any further information needed, just ask.
We have a database that is split. Every user has their own front end (installed via a .bat file so that the users always have a local copy of the most recent version), linked to a shared back end on a server. This seems stable and functional for us so far.
However, because our users are geographically separated, I don't actually know how many folks are using the database. I know the folks who contact me, but everyone in our company could use it if they wanted to.
For requesting funding, guiding future development, etc., I need to get some metrics:
Total number of unique users Avg uses per day
Total number of simultaneous users (daily, weekly, and monthly maxes and mins)
What is the best way to get this info? Write user info and a timestamp to a table each time a front end is launched? Are there tools for monitoring this sort of information?
I have an Access DB shared through a network folder. It is currently frozen and showing a record lock, however, none of the users have it open. Is there a way to kill the lock so I can restore it?
I work with an Access 2007 database that I split into a front end and back end. The back end holds all of the tables and the front end holds the queries, forms and reports. It is also a multi-user database, in that we have 14 people having access to the same back end. Each of the 14 people have their own front end. I have one main table in the back end listing clients and their individual ID numbers. That table has a One to Many relationship with several other tables, such as Case Notes and Authorizations, to name a couple.
Here's one of my problems: Sometimes data that is entered by a user will disappear.
Example 1: User creates a new record in the Authorizations form. Sometimes it is created by duplicating an existing record, sometimes it is completely new. User then prints a report from that new record, which looks like it has been saved. A couple minutes later, or a couple hours later, or maybe even the next day, user goes back into the database and cannot find the record that was previously created. At that time I look in the back end and cannot find the record either.
Example 2: User creates a new record in the Case Notes form on Day 1. These are always completely new records. User returns to the database on Day 2 and the new record sometimes does not show up in the form or in the back end table.
I cannot MAKE these things happen and they are sporadic, which makes it hard to diagnose, but why this was happening or if there was a way to retrieve the lost data.
I have a multiuser data entry form which on using somtimes gets inactive, means the button stop working and we have to close the form and open again to avoid.
Background Info: I developed our main department's Access 2003 split database which is on a server for 15+ Users. I've now been given Access 2007 for development -- Users still have 2003. There are also 2 other smaller databases that are not split (.mdb).
Problem: In the split database, I've saved the Application .mdb as 2003, relinked and made a new .mde. But the Users still cannot open the database. (I did this in a copy until I figure out the nuances with 2007.) The 2 other unsplit databases can be saved as 2003 version and Users can open OK.
I'm grateful for any suggestions on working with Access 2007. I've been trying to tackle the ribbon which is a whole other question.
I've been using Access 2007 to run queries on a database where we eventually export results as separate Excel spreadsheets for individual clients. The process is quite involved, using queries to change fields from code letters to words and splitting the database up into different client tables, saving the tables under date order and with different client codes.
I now need to pass this role on to colleagues, so need to make everything as straightforward as possible.
I had thought to use a Form as the user interface, with a minimum number of buttons, however I need either the system or the user to amend the date for the initial table, then to use this new table and run a series of standard queries on it, then produce the separate tables.
I don't think I can just use macros behind the buttons, because the database name is changing each time.
I assume some parts will be too tricky to automate - it will be necessary for colleagues to follow instructions instead.....
I have a call tracking database with 5 active users. Ocasionally the database will freeze, usually for several people at once. It will say: "Can not update record; Currently Locked"
The only way to fix it is to close and re-open. I split the database, compiled it and then created an MDE file to distribute. They are using copies of the front-end, not links. The database locking more and more every day. But we are using it more. After parusing the forum I'm starting to suspect that this is a problem of "Primary Key duplication" If everyone trys to create a record at the same time, would the database lock because we all created the same record at the same time?
If so, how do I get around this? Use something other than Autonumber for the primary key? Or is this an entirely different problem?
I am developing a database for a company with about 10 users of the DB each using the DB on their own PC/Mac.
The workplace does not have a network. All work is done and stored on their own computers. They do not have a server either.
They do have a BT Business Hub providing broadband both wired and wirelessly.
The database is built using Access 2010.
I need to set up user groups/permissions on the database so certain staff can only see certain forms/tables etc...
What are my options here?
There is money available to buy a server or whatever. But ideally if there is another way of storing it and doing it, then I would like to give that a try.
I was reading somewhere you can use the Hub to create a small network, is this possible? And if so, whats the set up process for this?
I need to make sure that all users on the network can access a specific network drive folder.
Most users don't have a problem but some users have the network drive mapped differently so when running the feature required their system can't find the file at that specified location.
I have the location of the files hard wired into the VBA code.
E.g. Z:INVDATACOMPANIESCompanyAASSETS Pty LtdPrecedents 2015STAGE 3A - COLLS-DEMS
The location is outside of the project address.
I need to be able to make sure the address of the folder is the same for all users.
I have a database that i created for our tech support team. I have split the database and have given the 3 users each a shortcut. I als e only installed Access Runtime on their machines.
The first user in pilot testing had no issues and we have just added the other 2 users. At first all was well. However, the 2 new users are now having periodic lock ups where they go to enter a new record and the dbase freezes on them. It seems to release itself in a few minutes, but this does not help my productivity.
My original user is still not having any trouble. Does anyone have any ideas of something that perhaps I missed? Everything I have read, suggests that 3 users should not have any trouble using a split dbase.
Is it runtime that may be causing this? I have considered going to full versions if need be, but then I would need to figure out how to lock it down better.
I have a relatively small database of approx 28 MB - for 5 users max on Access 2007 -since the upgrade from '97 I have noticed a significant degradation in performance over the network - not so much on my machine which is the server/host machine but on the clients. All are Pentium/Celeron processors and recently I have beefed up the working memory to approx. 750MB/ 1GB on all these machines.
Even so, on occasion, I might type something in a field on a form - Access seems to 'fall asleep' for 10 seconds or so and then wake up again. Indeed, the window caption states (Not responding) .... This is not happening all the time but enough that when it does happen it is particulalry frustrating. Has anyone else had a similar experience?
I am thinking of splitting the database into a front / back end to resolve this issue but perhaps it is not a network traffic problem / the database isnt really that big considering others that I have read about on the forum that run into 100 of MBs! - can anyone suggest this a sensible idea / or could I regret at a later date?
I split my database and put the BE on a shared network folder. The users can access and change data on the database fine as long as someone else isn't using it. If another person is logged on and using the database another person will be able to open the database but if the new user tries to click on a button or go anywhere besides the main menu it says there is already a user in the database and wont allow the user to go anywhere. How can I fix this?
I have had this in another Access forum ... without a resolution. I am pulling out what little hair I have left, so hopefully someone can help me.
I have created a program using MS Access that a small business will use to enter service contracts and invoices. The program was created in Access 2000, but converted nicely to Access 2007 which resides on the 3 brand new Dell computers that the business bought.
I did some research and determined that since 3 people would possibly be using the database at once, I needed to split the database. I did so using the wizard contained in Access. One of the computers acts as the "server" and houses the back-end of the database (in a "Public" folder that is shared to anyone on the network ... with all rights). All three computers have the front-end (forms, queries, reports, etc.) The front-ends all point to the shared "Public" folder on the "server" computer.
In each of the computers, the program will open and work properly. The problem comes when trying to access any of the input forms (that get data from the back-end). Only 2 (and sometimes 1) of the computers will open the form at the same time. If 2 are currently open and I try to open the third, it will sit and crank away as if it cannot find the file. It will not open. If I close one of the other forms, the third will open immediately with no hesitation.
At first I thought it could be a network bandwidth problem, but all are running at 50 - 100 Mbs and shouldn't have a problem opening a simple database file. I can't find anywhere that says Access would limit the number of users to 2; everywhere I look says that a split database should allow up to 10 users at once.
Again, the program works fine ... I just can't get it to open by 3 concurrent users.
My database is used by a few users on a shared drive in "read-only" mode so they can't change any data.I set a scheduled task to copy an updated version of the database (which I edit) every morning.
The problem is that when the users forget to close the database file I can't overwrite it.For this purpose I wrote a small code that quits the application at 00:00 using Application.Quit.The code WORKS when I test it on my computer, but every morning when I try to open the file I see that it is opened by another user since I also open it as "read-only" - meaning the code didn't work...getting the code to work properly OR get a better solution to be able to overwrite the file even though it's opened by other users.
I have a split database consisting of an ACCDB backend helf on a shared drive, and a local ACCDR frontend distributed through email. I recently distributed a copy of the ACCDR to a user, and she says that the main menu opens fine, but when she clicks a button to open a form, the form opens but is completely blank. I've googled some possible causes for this (e.g. on Allen Brown's website) and none seem to be relevant, or would explain why this started happening all of a sudden. I can't reproduce this bug at all.
I found a somewhat related thread but thought I would post a new one just in case my problem happens to others who were 'forced' to "upgrade" to Windows Vista Home Basic when buying a new machine....
Okay, no more complaining, here's the issue:
I have a small (8 MB) Access 2007 database stored on a machine in the office which is running Windows Vista Home Premium. There are two other machines running Windows XP Home (SP2) that can connect to the Access 2007 database with no issues.
But on my Dell Inspiron Vista Home Basic machine, I cannot successfully open the database stored on the Home Premium machine. I CAN see this database, I can see other files and open/copy them, but I cannot open the database. I am attempting to do this using the wireless connection.
I tried running these two commands as the administrator (found on another forum) because I think this is more of a network problem rather than an Access 2007 problem specific to Windows Vista Home Basic
netsh interface tcp set global rss=disabled netsh interface tcp set global autotuninglevel=disabled
But these two commands did nothing.
I should also point out that when this database is stored on an XP machine (one of the others in the office) the database opens just fine on this Vista Basic machine!
I'm confused and frustrated! Any help is very much appreciated.
I have a split db with tables in the back end and my forms, reports, code etc. in the front end.
I encrypted the back end with a password. That worked fine.
I deleted and relinked my tables to the encrypted back end. That worked fine as well.
I have a function that will disable the shift key bypass. If I run that in my front end db then I can't save it as an accde because I can't get to the HOME screen. If I save it as an accde first then I can't run my 'disable shift key bypass' function because I can't get to the modules!
How can I secure a split Access 2010 database so that the user cannot execute shift bypass on the front end or make any changes to the code?
I have two separate database files, a front end with all my forms and a back end with all my tables. The backend is stored on a network drive, is there any way of being able to store the frontend on the drive with the backend? Users do not use the actual computer to store information or access files, everything is usually saved on the network drive. I have created a shortcut that launches the front end database in the read only kiosk mode.
I'm having some issues with a database that I have created. All this time only one user would be in the database. But now I have 2 to 3 people at a time in the database. I get an error and its not letting the users put any information in.
I have it split into a FE and BE. I have it set to share. Any thoughts or suggestions?
"how to COMPACT the DB by introducing delay of 10 seconds and then close the DB".In the Database, I'm able to accomplish the "Compact" the database using the function below.
Function Compact() SendKeys "%(FMC)", False End Function
As my DB is quite huge, the Compact action takes around 10 seconds to complete.Now, i would like to Close the Database after Compacting the DB. I tried including "DoCmd.Quit" in the function. The commands in the function, closes the DB but the Compact function doesn't seem to have executed as it needs 10 seconds to complete.
Function Compact() SendKeys "%(FMC)", False DoCmd.Quit End Function
how to introduce this delay of 10 seconds and then close the DB.
