I would like to clarify the concept of record-level locking Say I have a TBLA which consists of two fields TBLA_FLD1 & TBLA_FLD2...TBLB which consists of three fields TBLB_FLD1 & TBLB_FLD2 & TBLB_FLD3
Now two users have declared the usage of the database as shared and pessimistic...I have generated a query in addition to TBLA & TBLB which is QryCombined which basically just a join of the two and consists of all five fields...If user1 has brought in a record query from QryCombined and edits on TBLA_FLD1 & TBLB_FLD3...At the same time user2 is trying to bring in as well QryCombined but with TBLA_FLD2 & TBLB_FLD1 and trying to edit both, will user2 be blocked by user1 who's editing the other fields
For all I know, the locking is based on record-level locking so I would suppose one record of QryCombined will comprise of all five fields so editing in any of them should actually block another user from editing. However I am not sure whether it is just a subset of the record that is the contesting area because user1 recordset has only two fields out of five, which do not happen to intercept the other fields that user2 is working on.
When I made my Database, my Access Objects window displayed only the tables I created apart from the default Create table in design view, Create table using wizard and Create table by entering data. After finalising the database, I transferrred it to another PC and made some changes. The changed database was copied in pendrive and put in my harddrive. When I opened the DB, I find the following additional things in the Objects window :
I have a bit of a big query and dont really know what some aspects of it are doing. Can someone tell me why there are so many group by functions? Does there need to be one for every FIRST in the query? If so, what does this accomplish? Also tmp_recdata_trades has around 20,000 records in it. And the query returns one record? Why just one? And its a record in the middle of the table somewhere so there must be something special about it?
SELECT DISTINCT tmp_recdata_trades.[Client SPN Number] AS spn_id, First(tmp_recdata_trades.[Client SPN Name]) AS spn_name, First(tmp_recdata_trades.[Revenue Event Day]) AS trade_date, First(tmp_recdata_trades.[Product Name - Level 4]) AS product_name, First(tmp_recdata_trades.[Client Domicile Name - Level 0]) AS client_domicile_name, First(tmp_recdata_trades.[Team Name - Level 1]) AS team_name, First(tmp_recdata_trades.[Person Name]) AS person_name, First(tmp_recdata_trades.[Source System Name]) AS source_system_name, First(tmp_recdata_trades.[Business Hierarchy Name - Level 1]) AS business_hierarchy_name, First(tmp_recdata_trades.[Client Role]) AS client_role, First(tmp_recdata_trades.[Client Sector Name - Level 3]) AS client_sector_name, tmp_recdata_trades.dox_fsa, tmp_recdata_trades.dox_aml, tmp_recdata_trades.dox_aof, tmp_recdata_trades.dox_sub_acct, tmp_recdata_trades.notes_aof_live, tmp_recdata_trades.notes_aof_archive, tmp_recdata_trades.remediation_project, tmp_recdata_trades.remediation, tmp_recdata_trades.custody, tmp_recdata_trades.pb, tmp_recdata_trades.xbus_signoff, tmp_recdata_trades.wss_signoff, tmp_recdata_trades.man_approved, tmp_recdata_trades.fsa_class, tmp_recdata_trades.opted_up
FROM tmp_recdata_trades
GROUP BY tmp_recdata_trades.[Client SPN Number], tmp_recdata_trades.dox_fsa, tmp_recdata_trades.dox_aml, tmp_recdata_trades.dox_aof, tmp_recdata_trades.dox_sub_acct, tmp_recdata_trades.notes_aof_live, tmp_recdata_trades.notes_aof_archive, tmp_recdata_trades.remediation_project, tmp_recdata_trades.remediation, tmp_recdata_trades.custody, tmp_recdata_trades.pb, tmp_recdata_trades.xbus_signoff, tmp_recdata_trades.wss_signoff, tmp_recdata_trades.man_approved, tmp_recdata_trades.fsa_class, tmp_recdata_trades.opted_up;
I have a list box that displays the contents of a back-end table that is has a new record added roughly 45 times per hour. I'd like the listbox to refresh automatically when a new record is added and, ultimately, notify the user that the new record is there. Are there any non-form events I can use to accomplish this? Right now, my workaround is to refresh the box once a minute using the form's timer. It works fine, but it's not pretty. Ideally, I could create some sort of event based on the new record being saved in the table.
is it possible once a record has been entered, to lock it so that it can not be changed by just anyone but that the administrator could change it using a password?
Had a good look at previous posts but couldn't find exactly what I am after. Having entered a record I don't want users to go back and edit/amend the input. I can see how to lock and unlock records but controlling this wilt say a checkbox also means any user can 'uncheck' and edit. Is there a method to restrict who can unlock ('uncheck') and edit a record, other than using Access Security permissions. Thanks RussG
I can't do nothing in my database because it keeps saying users have it open. There is nobody in the database though. I open up the file and saw two admins names and I deleted them both because we out of the database. The database is on a network drive and when I try to do any updates I get the message "Can't save changes or make changes because the database is open"
I DON"T KNOW WHAT TO DO, FOR THE LOVE OF GOD SOMEONE HELP ME PLEASE, Help me Jesus, Help Me Oprah, Help Me Tom Cruise!
This is my first post and I am really hoping someone will be able to help me.
I have created a database for a friend and multiple users will be using it. I would like to know if anyone is able to assist me in being able to lock a specific record when a particula option on a combo box is selected.
For example:
I have a combo box with several status'. Open, Pending and Closed.
Once the status is changed to closed, can that record be locked to unable ant further editing?
I have created a form using Access 2003 which users have to enter data into.
What I want is a button on the form which will display "lock record". When the user clicks this it will lock the record so that another user can’t accidentally change the field values. If they do want to change the field values the will have to press another button displaying “unlock form”
I have a split database, with identical front end databases that have tables linked to the back end database. All forms are all located in the client databases.
Both databases are set to "no locks" (I believe this is referred to as "optimistic" record locking?) and are set to "shared" open mode.
However 2 seperate users cannot open the main form, even when opening different records - it comes up with the record locking error message.
Question - The form they open is filtered to show only one record (as selected on opening the form) - will this lock the whole table or just the one record it is looking at?
I'm not sure I've explained myself brilliantly here - I'm not up on all the lingo! If you don't understand please say & I'll try to explain better.
:confused: I created a data base in which 4 users enter information using a form. I have no record locking applied. When 2 or more of the users enter the form at the same time but have not entered any information they get a record lock error when one of them tries to enter data. The company wants to open this up to more people so I need to stop this from happening. Any ideas? I'm using access 2003 and windows xp.
Is there a way to assign a new record upon opening the form rather than waiting for data to be entered? I think this would be my last resort because it could create alot of blank records. All help is appreciated.
I've created a database and split it so that the data tables are held separately. Everything works fine apart from when 2 users simultaneosly try to create a new record on the same table. I'm guessing this is a record locking issue but can't work out what I should be amending.
Sorry for creating yet another thread on table design/relationships for surveys, I did search just needed a bit of clarification.
From what I understand from my search the best way to create a basic db for surveys would be a question table, response options table, and response table, this is similar to a database I have implemented for a simple online surveying system. Now this works with Y/N, or even limited response questions.
However I can't get it to fit with my current project, and I don't think it will, but I thought I'd check to see if I'm being stupid.
I have a survey, 20ish questions expecting 200-250 responses. The questions consist of a mixture of:
Yes/No answers Select all that apply answers Select one from list Select one from list or select Other and enter it yourself Text/Comments
Because of the wide range of values, some completely unique and the possible number of answers per question per survey it seems the above method (and that implemented in At Your Survey which i've had a look at) would not work.
I only need to store responses (not questions), so I'm proposing a main table with related tables for those questions that require it.
So in essence a flat file response table with each field being a different question, each record being a separate survey response. For those questions with multiple answers a separate table, related to the main response response table.
Data entry will be twofold, a web version of the survey automatically recording responses and manual entry into custom form within access for those paper returns.
Reporting will be faily widespread with a lot of cross referencing.
I know this is not a reusable format, and it is pretty much a one time only project anyway, relatively short lived. But I'd still like to create the most efficient product I can, within the time constraints.
In OPTIONS -> Advanced I have the option Open Database using Record-Level locking ticked.
What is strange is record level locking works on Form1 (Form1.recordlocks= Edited) as long as Form2 has Form2.recordlocks = No Locks. If I change the Form2.recordlocks = Edited, Form1's locking becomes page level locking.
This is even true if Form2 is not open...
Additionally, Form2's locking is always page level locking if Form2.recordlock = edited.
I would like all my forms to perform RECORD level locking not page Level locking. How do I accomplish this.
I've been trying to resolve this issue for about 2 months now but no answers from the community.
I have a form that at certain times, requeries tables to update pricing. My problem, I have now found, is if users access this form at the same time (even if they are accessing different records) the second user will get error message 3009: "You tried to lock table while opening it, but the table cannot be locked because it is currently in use. Wait a moment..." when requery code runs.
Does anyone have any recommendation on how to handle this? Anyway around this? I am at the end of a very long creation process and hope I don't have to reinvent the wheel. I'll hold my breath! Thanks!
ps I have record-level locking set as well as edited-record record locking
Ok, here's the deal. I'm tired of fixing our gigantic table because of corrupt records in my firm's database. I find that the problem ordinarily comes up when someone opens the same record in two different forms (whether on the same computer or on a different machine) and both attempt to make changes. Is there a way (preferably simple) to just LOCK a record in a particular table to keep this from happening? I assume there has to be, but I've just never taken the time to find out. I'm not sure if this locking is done on the form level or somewhere in a table option or something.
I am opening a form, from another form, that is based on a table of 20000 records.
I am using the WHERE criteria to do this, code is included below. I need to provide database access to several users on a network (and only want to lock the record that the user has searched for), and was wondering if this mode of data access will lload and or lock all 20 000 records on the users sytem, or if it will just load and lock the specific record that has been opened using the WHERE condition to open the form...
On my form is an option button called "optClosed". When the option button is selected, I want to lock the current record so that data cannot be changed. Is this possible?
hi, I've got a database that is split -front/back on a server. It is used by roughly 5 people. Usually one or two people use it at one time, with one of the users who leaves the application open at all times. Sometimes when the users are working in the screen, which would be in the subform, an error like "record is locked" pops up and they cannot enter any data. This happens on a date field where I have put some code to auto fill the date if the previous record has today's date in it. It doesn't seem like it should be locking on that. Should it? This is what I have in the code: Code:Option Compare DatabasePublic DateValue As DatePublic DatePresent As BooleanPrivate Sub DateLog_AfterUpdate() 'fill the date with previous entry - if it has been entered in same session DateValue = Me.DateLog.Value DatePresent = True End SubPrivate Sub DateLog_GotFocus() If (DatePresent) Then Me.DateLog.Value = DateValue End IfEnd SubPrivate Sub Form_Load()Me.RequeryDatePresent = FalseEnd Sub
How can I remove a Recording-Locking file without re-booting the computer? I've tried cut and copy and paste, with no results. No one is using the mdb file, but it has a record locking file associated with it.
I have some security issues going on all of sudden. I now have 2 Record locking information files on my drive. I have several users sharing the database. Any clues why this is happen?
How can i apply the following code to the immediate record in my subform. I would like when the assigned check box value = true, then the fields for that record become locked.
Ive written the following code to an onclick event for a check box labeled "assigned" on my form, the problem is it executes on all the records in my form.
Q. How do i apply the code to only the immediate record?
Private Sub Assigned_Click() If Me.Assigned.Value = True Then Me.Serial_Number.Enabled = False Me.Component_Group_ID.Enabled = False Me.TypeID.Enabled = False Me.Description.Enabled = False Me.Status.Enabled = False
Why do I have 2 Office Record locking, with different names. One is called Secured (which is ok), but the second one is called the same name of the Database. My database is running real slow. I have compacted and repair, but still running slow. I thought it might be the network, but when I open my old backups they run normal.
