Split Database Locking
Jan 5, 2007
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?
By the way, my record locks are set to "No Locks"
Thanks!
View Replies
ADVERTISEMENT
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
[code]....
View 8 Replies
View Related
May 24, 2005
Hi, I just recently split my Access db (FE/BE), copied the FE to each of the clients, and it seemed to be working fine. This morning however, two users went into the db, and the second user kept getting the error can't find the file \servernameshare_namedb_fe.mdb. Now, none of my users can launch it. How can I resolve this? Will this keep happening?
AP
View 1 Replies
View Related
May 8, 2005
I have a company wanting to asses my database and i am willing to show them but i need to be able to lock the database so that they can view it but cannot edit anything in it either adding deleting or plan editing. How can i do this.
Bullfrog
The Amateur
View 4 Replies
View Related
Dec 6, 2007
Hello,
As my username states, I am a rookie to MS Access. I built a database using Access 2002, I will distributed it to other people, I will like to lock changes to certain areas such as forms, design view, etc.
Can this be done? The help is greatly appreciated!
Rookie2007
View 14 Replies
View Related
Feb 17, 2005
I have looked on the forums and cant quite find what im looking for so i wanted to ask you lot for help.
I have a database i have created.
It will be sent out to people to populate via a series of forms i have created.
How can I lock it down so that:
1) you cant access any of the database apart from the forms
2) Lock it so no one can get in to view the code anywhere or make any amendments to the forms and database what so ever
View 3 Replies
View Related
Feb 1, 2005
I am new at this stuff. So please excuse my ignorance. I created a database which a lot of my colleagues want a copy. I don't mind giving them a copy. However I don't want them to change the design or copy it to give to others. I don't mind them adding their records.
Is this possible to do? Or should I just keep the database to myself.
Thanks in advance to your advice.
View 1 Replies
View Related
Nov 29, 2005
I have prepared an access database on a shared drive location(lan) with 5 tables. The main table has over 110 fields and is linked to a form. On an average the table stores 12000 records, which are updated via a form.
Normally, 7 users work on this form and do add/change/delete operations on the records.
The frequent issues I face are:
Corruption of records.
"Could not update, currently locked" errors.
I have constantly tried compact and repair option, but the same does not give required results.
Kindy advise a solution.
View 2 Replies
View Related
Feb 2, 2006
Does anyone know how to lock a database so it can be accessed by only one person at a time? We have several users using a 'local' copy of the same database and frequently synchronizing with a network copy. We want to avoid a situation where more than one person is trying to synchronize with the master. Please advise - thanks!
View 2 Replies
View Related
Aug 7, 2007
Dear All,
is there any way to look-down the structure of tables within a database so users cannot change tables?
Is it possible to make this password protected so only certain users have rights to change the layout of the tables?
Would this still allow users to create queries and enter data?
Thanks, Steve
View 1 Replies
View Related
Nov 13, 2007
Have an Access database that is used by multiple users, the lock file seems to have vanished. Yet the database is in a read only state and still locked. How do I unlock the database? How do I find out who is locking it? How do I kick the person off is neccesary to unlock the database? Thanks.
View 5 Replies
View Related
Oct 13, 2005
why is it that as soon as you fix one problem another one needs dealing with :mad:
OK, the problem i have is that i have a BE/FE configuration database in a multiuser environment. I have built all the tables and the relationships in the BE configuration. In the FE configurations i have built the main forms to input data. It has no come to the point where i have begun to create the queries to allow reporting and data searching. And this is where i have a problem. I am trying to create a simply query and i do mean simple (at the moment i simply want 2 fields from 1 table and 2 fields from another table) no calculations or anything i just want it to display the data. I have tried creating this query numerous times and i keep getting the same error.
"The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"
the above is the error i get when i use a wizard to create the query, after getting this message and going into design view i try to view the results in datasheet view and get the following error "Type Mismatch in expression"
If i dont use a wizard and create it in design view, when i go to view the results i get the second error message everytime.
If i create a query from 1 table only the query works without errors, but i always get errors when i have more than one table in the query.
Also i am the only person with the database open so therefore it is total impossible for the database to be open by another user in exclusive mode.
Anyone know what the problem might be or how to fix. Your answers will be very much appreciated.
View 1 Replies
View Related
Feb 16, 2006
Another baffling problem - there are far more traps for new players than I ever imagined. Any suggestions gratefully received.
I have a fe/be split database with multiple users on a network. All users have the same front end on their PCs and are either running Access 2000 or 2003.
Problem 1:
Some users cannot open the database at all, getting the error message "Could not open xxxxx.mdb - File already in use."
Problem 2:
Some users can open the database but get a message in the status bar saying "Recordset not updateable."
Other users, running either Access 2000 or 2003 have been able to use the database without difficulty. The number of users is in single figures and it is unlikely that more than 4 people have attempted to log on simultaneously.
The database default open mode is set to "shared", the default record locking is set to "No locks" and the "Open database using record-level locking box is checked".
I have been having difficulties setting the security for the database (see other postings on this site) and so it is currently unsecured with no password set for the admin user. Users are joined to their default system Workgroup Information Files.
My guess is that there is a setting on the Access installation of the user's PC that needs to be changed but this is only a guess - anybody any idea what I should try next?
Regards,
Keith.
View 5 Replies
View Related
Jul 23, 2012
I have created two DBs, one Back End, and one FrontEnd. My FrontEnd contains all the UIs/Forms, Linked Tables, and the queries fetching data from linked tables, and the Back End consists of all the Tables.
Now, in my Model, the BackEnd DB will be stored in a common drive and will be accessed by multiple users through same FrontEnd.
Now when I am trying to fire a query in FrontEnd (aka FE) DB, it is locking up the Back Up database. Even though I have set the RecordLock property to No Locks to query and the form.
how to remove the locking?
View 5 Replies
View Related
Jun 13, 2013
I have access BD and every one use it through shared folder. My problem is in attachment ! , if i use the regular attachment filed in access there is one user only able to attach files and others can`t attach any thing until he finish.
I tried to do attachment through VBA like below which make it worse as it lock the DB so, no one can even open the DB and one user only access the DB .
Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2
Set db = CurrentDb
Set rsParent = me.Recordset
[Code] .....
How multi users attach files at the same time without locking the DB.....
View 3 Replies
View Related
Mar 9, 2006
All,
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.
--------------------------------------------------
Question Time:
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.
Hope you can help.
Cheers,
Ian Mac
View 4 Replies
View Related
Jan 12, 2006
:confused: I have read a quite a few threads on spiting the database. My database is 50 Meg and running very slow. I have compacted and repair, still the same. This just happen all of sudden. Could that be possible?. What should I look out before I try to spit it?. It’s given that I will make a backup. I am running Access 2003. I have over 150 users.
View 5 Replies
View Related
Aug 12, 2006
I am almost at the finishing line with a great deal of help from here.
I have now normalised the tables, built complicated queries, forms, reports, all from scratch as I knew absolutely nothing before I started with Access three months ago.
I have compacted and repaired the database using the wizard, and also complied the code and analyzed all tables, forms, queries, and reports and relationships.
The final step is to make it an MDE file so that it can go on the local network at work (not on the internet).
My questions are these;
Do I need to split the database into front and back ends?
If I save a copy of the original MDB file and then make it a MDE file would that not suffice in making it run more efficiently?
Can a split MDB database be made into an MDE database?
Any advice, as always, is most welcome.
View 4 Replies
View Related
Nov 21, 2006
I have a database that resides on a file server share. I have split the database to create a backend. I then posted a shortcut of the front end to the desktops of two users who have access to the share. I then tried to access the front end from the two machines simultaneously. It opened on the first PC but not the second? When I viewed the server share it had a 'padlock' icon?
Where am I going wrong? This is the first time I have attempted this scenario!!
Thanks in advance,
Phil
View 4 Replies
View Related
Dec 21, 2006
Hello,
Should splitting only be done when all tables,queries,forms,reports, etc. are done. Or can it be done anytime. I am almost to that stage but didnt want to jump the gun and run into problems later on.
Thanks.
I will have about 20 users sharing this.
View 6 Replies
View Related
Dec 27, 2006
I was having a discussion with Rickster57 concerning my newly developed database, and he recommended that I split it so that it has a front end and a back end. Rick listed a number of very good reasons for doing this (so I will definitely do it). But I wanted to pose the question so I could get some of the reasons the more seasoned Access programmers have for creating a front and back end to their programs.
View 9 Replies
View Related
Jun 29, 2007
I recently split my database and also used Bob Larson's Autoupdating tool. Everything appeared to be working fine until a few users were unable to access the db. I later found out that because they are at a different site, they don't map to the server that houses the database BE. However, even if they manually map to the correct server, they are unable to bring up the db Form. They get an error stating the BE "...is not a valid path". My questions are:
1) If I were to copy all the files (the Master FE, BE, and MDE) to a public folder that ALL users have access to, will everyone be able to bring up the database Form?
2) How would this affect what was already set up when I ran Bob Larson's utility (or even the splitting and MDE creation)?
3) Is there a way to "Undo" what was previously "Done" to this database (ie: Splitting, Larson Utility, MDE creation) in order to correct the issue, or do I need to start from scratch and split the database while it's on a Public drive.
I hope this makes sense. Thanks in advance for your help.
Michael
View 10 Replies
View Related
Jul 2, 2007
Hi!
I just want to know that " How to split the Access database in Front End and Back End application"? I am creating a database that i want to store on server and simultaneously want to give access to 20 terminals (cleints) to access the database.
Secondly, I have to provide a field in a form "Document number". However as per the requirement user can enter "PIR No", "Serial No" or at times both as document number. Both the things "PIR No" and "Serial No" have different format types like "PIR No 001" and "Srl No001".
On basis of document number later i have to give the option to search the particular document. Please guide me how to provide the option to enter the field values.
Appreciate any help on above.
Regards,
Nick
View 3 Replies
View Related
Aug 12, 2006
I am almost at the finishing line with a great deal of help from here.
I have now normalised the tables, built complicated queries, forms, reports, all from scratch as I knew absolutely nothing before I started with Access three months ago.
I have compacted and repaired the database using the wizard, and also complied the code and analyzed all tables, forms, queries, and reports and relationships.
The final step is to make it an MDE file so that it can go on the local network at work (not on the internet).
My questions are these;
Do I need to split the database into front and back ends?
If I save a copy of the original MDB file and then make it a MDE file would that not suffice in making it run more efficiently?
Can a split MDB database be made into an MDE database?
Any advice, as always, is most welcome.
View 12 Replies
View Related
Sep 11, 2005
I am combining 12 Databases. I have split all 12 between the server and the desktop. My question is “Should I combing all of the data table on the server into one database or should I leave all of the individual application data tables in separate Databases on the server”. Additionally, is there a limit to the number of tables an Access DB can handle? My inclination is to keep the functionality separated but the problem I have is that some of the functionality within the applications overlap. Recommendations!
View 2 Replies
View Related
Nov 9, 2005
'ello all,
The moment of truth finally came and I split my database FE/BE for testing only the truth was a bit sour.
Background:
I have several forms in my database that have a common series of buttons at the top which open their corresponding form. Before the split, everything was fine - you can click on the button and the desired form would open. After the split, I'm getting the error message:
"Microsoft Office Access couldn't find the toolbar 'EVM Database Menu'."
This is referencing a toolbar that I used during development to help jump to forms.
I tried removing the toolbar from each form (Tools > Customize > Toolbars > unchecked the custom toolbar), and re-split the database FE/BE ends, but to no avail..
So, I checked the event codes to be sure I didn't program something wrong. There are 9 buttons total to open forms, 7 of them give this problem, the remaining 2 do not. The code for the problematic and non-problematic ones are identical!
Example - Non-Working (error message re: Custom Toolbar):
-----------------------------------------------------------
Private Sub cmdActuals_Click()
On Error GoTo Err_cmdActuals_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmDataActuals"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdActuals_Click:
Exit Sub
Err_cmdActuals_Click:
MsgBox err.Description
Resume Exit_cmdActuals_Click
End Sub
Example - Working (no error message):
-----------------------------------------------------------
Private Sub cmdPerfProj_Click()
On Error GoTo Err_cmdPerfProj_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmPerfProj"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdPerfProj_Click:
Exit Sub
Err_cmdPerfProj_Click:
MsgBox err.Description
Resume Exit_cmdPerfProj_Click
End Sub
Questions:
So..this has nothing to do with code, it must be a problem in the linking somehow, right? A bug possibly? What else could be causing this? More importantly, is there a solution to my predicament that anyone could help out with? Searches so far have returned no help either from forums (including here), MS site (no Q-articles), and VBA Help. I'll continue searching though.
Any help would be appreciated. Thanks!
View 2 Replies
View Related
