I am trying to define a relationship between tables. The relationship is one-to-many with forcing referential integrity. When I try to define it, I get the dabaase engine error:
"The database engine couldn't lock the table, because it is already in use by another person or process"
I am sure that the database is not opened by anyone else, it is 100% local, no other instance of Access is running, and there was no *.ldb file... What can be the problem?
I want to lock a table to disallow further records. I want to have only one record (I would have created this rec by myself) to store initial conditions and infos such Username/company etc. Any ideas? I'm also interesting in other solutions than locking a table if sucj a thing is not possible or nt functional.
I'm working on a database that calls on a table of zipcodes. All of these zipcodes have latitudes and longitudes connected to them..... I've searched around but cant seem to find anything on locking a table. Is it possible to lock down a table so changes cannot be made?.....(until I need to make them)
I'd like a code to lock a table in my access database so that it can not be mistakingly deleted by the user. It's a very basic database program i am using for basic reporting purpose. I'd like to lock the master table from the user. How can i achieve this? Any ideas?
I have a question, I'm building a MS ACCESS database and within it i have a table for putting in my program critical parameters. I don't want anyone to change the content of that table. That means not editing the 1 record in it and not adding a new record. Within the program is't a problem but if you make an other blanc database you can import the table, change it and export it back into the program (i know it is far looking but it happens).
How can i lock a table so it cannot change or it cannot be exported or imported???
Is there a simple way that I can lock a table or better yet configure it as view only. The table is used to populate a combo box in an entry form, and I do not want anyone to have the ability to add records to the table (options in the entry form), but I would like them to be able to view the table. I don't need a robust solution with passwords. I would imagine there is a simple preference to set, I am just not seeing it.
I have a form with a combo box that contains a list of customers. Select a customer from the combo box and the form fields populate with general customer info. The form has a subform on it with a combo box where you can select products. Select a product from the combo box and the subform populates with customer-specific product info. If I open the main form and select a customer, then without doing anything else I select another customer, everything works ok. It’s when I select a product on the subform, and then try to select a different customer on the main form that I get “Run-time error 3211: The database engine could not lock table tblCustomer because it is in use by another person or process.” Can anybody shed some light on this? I’ve been confounded by it for days.
I am having a problem running a make table query which is based on the same tables that I am using in a sub form.
Basically, the user selects certain records in the subform (which is in datasheet mode) using a check box, then once selected he hits a command button where there is some code that will run the make table query, which I use to filter other tables in a related reports.
However I keep getting this silly "database engine could not lock table" message relating to the sub form name. The subform is not linked to any records in the main form as the main form is unbound.
The solutions offered by putting DoEvents or Me.refresh/Me.requrey in the code do not work.
I have a live db running on a VPN network (FE/BE both centrally located on a main server , users 'view' their own personal front end through the VPN tunnel) ) and have recently provided a new updateto the FE. Until this time , most users were problem free but one persisted in getting the above error message. Now the new update is in place , they all get it, and this is when no one else is using the db and a single user logs in and tries to use the system. I guess it must be an internal fault ( ie my fault ) but then why does it work for some and not for others?. It also works no problem on my system at home. The user runs Access 2000 and the db was developed in 2003 ( default file format 2000) Any help most appreciated Thanks in advance.
In a Access 2003 database, I have an "Inscriptions" (subscription) database with a primary key on 2 fields idPersonnel (employee) and idSession. I have made a form so that user can select a session (in a listbox), then one or more employee (another listbox) and suscribe them to that session by using a button, which, on VBA side, first check that there is enough room on the session (defined by "MaxParticipants" field on "Sessions" table, linked to "Inscriptions" table on idSession), then insert data in "Inscriptions" table
This is working fine in a single-user environnement, but fails if 2 people want to join some employees on the same session at the same time, as I have a confirmation message between check and insertion. Therefore 2 users can select employees, get the confirmation message (at this point both are told there is enough room), resulting in having more people than expected joined to the session. Fortuneatly, if both users try to insert the same employee(s) to that table, one will get a duplicate error, but insertion will be made if employees are different.
On another DB engine, such as SQL server, I would use a stored procedure that would lock the table, do the check and the insertion then unlock the table.
But it does not seem to be possible in MS Access. What are the possibilities in MS Access to prevent a session from having more than maximum number of participants ?
I've added a list box to my form that displays a table. This table is completely independent and gains it's data from an Append query. There is also a Delete query, which clears the table for a new set of data, and an Alter query which resets an AutoNumber column in the table (so that new data sets always start numbered at 1).
Here's the problem: Since I've added the listbox and set the RowSource to the table, whenever I run the Append query I receive the error: "The database engine could not lock table 'tblLineSheet' because it is already in use by another person or process." I've made sure to close the table, but to no avail I still get this error. Without the List Box I don't encounter this error.
I have a DB with multiple import processes to take in information and populate various tables. This has been in place for some time and works with no issue.
The import process involves the creation of a local staging table, to which I import the received data, normalise it according to my own table structure, remove any duplication (i.e. same data imported repeatedly) and append the cleansed data to the main tables.
I've just added a validation step to check for data integrity. In other words, for duplicate data, rather than simply purge it as I was doing previously, I now check to see if the data has changed in any way and prompt the user if necessary (with the option to either ignore the change or update the existing record)
However, since I added this extra step, I am getting the following error message during my import process :
Error 3211 : The database engine could not lock table 'tblStaging' because it is already in use by another person or process
It only appears a) when a data change is detected and b) when the option to update is chosen, and seems to occur after the update has taken place (i.e. I can see the records suitably updated)
But I can't replicate the error while debugging so it seems to be a run-time issue. It's definitely not another user as the staging table is deliberately local (specific to that user's front-end)
I suspect I can probably solve the problem by adding some kind of DoEvents call or brief pause somewhere but the problem is, I don't know where the problem is occuring, so I don't know where to add the line? Or if that would even solve the problem at all?
Is there an easy function I can call to check if the staging table is locked? Which I could then use to debug and identify exactly where the lock is causing a problem? Or even apply the DoEvents to release the lock? (It must eventually get released as if I step through the code, I don't get the error?)
I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.
Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.
I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table.
I have a form that contains a subform. The subform is a datasheet which calls the results of a query of 4 tables (tbl_companies, tbl_deals, tbl_products, tbl_vl).
In the main form there is a button which opens a report with a historical record of dates of value changes of each product up to a chosen date. This chosen date is determined by inputting a date into a text box.
This report also sources (tbl_companies, tbl_deals, tbl_products, tbl_vl).
When I try to open the report, I get the run-time error "3211".
'The database engine could not lock the table "Tbl_companies" because its already in use by another person or process.'
I'm guessing that because the query for the subform is already calling the table, the query for the report can't call the table at the same time?
I am trying to put together a sales/purchase/stock control/accounting program using Access. I have put together a number of tables that I feel will be required and now I'm trying to set the relationships between them. The primary end document to be printed will be a Sales Order and a Purchase Order, likely generated from the reports module, so I have a table called Purchase Order and another called Sales Orders, related to Purchase Orders will be a table called Suppliers and another called Customers will be related to Sales Orders. Another table is called products. For the Purchase Orders table, do I only add fields that are the primary key from the Suppliers table in order to list the full customer name and address stored only in Suppliers table. Would the supplier table be the parent and the Purchase Order the child ?
When a booking is made for just any time, it is in the tblBookings table. Regular bookings for example, every week, are put in the tblRegularBookings. In each of these tables, a booking cannot be made for the same date and time period. I am wanting to link the tables, so that when a normal booking is made in the one table, it cannot be the same date and time period as a regular booking as well.
The fields in each are as follow:
tblBookings: Booking ID Customer ID (linked to tblCustomers) Date for Booking * Time Period * Weddings/Birthdays (yes/no) Extension (yes/no) Cost
tblRegularBookings Regular Booking ID RegCust ID Date For * Time Period * Extension Cost
What should i link to allow me to not create a normal booking on the same date as a regular?
Here are my relationships for my 5 tables I created. My database is something that a turn key automotive performance shop would use. Tell me if my relationships are correct and what I need to do. I think I got a good start but I am not sure on a few things. Also what do I need to do for my validation and look up tables. Any help on that would be great. Thanks.
This is a simple video library database, 1 member can have many loans, 1 video can have many loans and 1 loan can have many videos, so with that in mind woud anyone be able to make my relationships right? because at the moment you cant take out more than one video on each loan?
I have restructed my original tables into 3NF and added relationships. But I think I am missing a few more items to go on to next step. Help me, please! I am putting together an automated Time Card Entry Database based on a timesheet and other reports already existed in Excel. I have the following fields on this form as follows: 1. Week Ending: automatically populates with "mm/dd/yyyy" date format from tblPayrollSchedule. This field will insert the correct pay period with comparing against today's date. 2. Employee Number: combo box that will populate next fields (1. Employee Last Name; 2. Employee First Name) after user select correct Employee Number. 3. Employee Last Name: automatically populates when Employee Number is selected. 4. Employee First Name: automatically populates when Employee Number is selected. 5. Acct Id: combo box containing a list of labor description that has associated fields as follows. 6. Description: Text Box will populate after selecting Acct Id. 7. Cost Center: Text Box will populate after selecting Acct Id. 8. Acct: Text Box will populate after selecting Acct Id. 9. Category: Text Box will populate after selecting Acct Id. 10 Pay Type Id: combo box that will populate Pay Type. 10. Pay Type: automatically populates when Pay Type Id is selected. 11. Allocation: Free form, to type comments. 12. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri 13. Total Wk Hrs: total hours for the week for per line of Acct Id.
Here are my tables: 1. tblEmployees: 1) pkeyEmployeeId = AutoNumber 2) strLastName = Text 3) intStaffNumber = Number 4) fkeyEmployeeTypeId = Number
2. tblEmployeeType: 1) pkeyEmployeeTypeId = AutoNumber 2) strEmployeeClass = Text 3) strEmployeeType = Text
3. tblAccounts: 1) pkeyAcctId = AutoNumber 2) intCostCenter = Number 3) intAcctNo = Number 4) intCategory = Text 5) strAcctName = Text 6) strAcctDescription = Text
3. tblPayType: 1) pkeyPayTypeId = AutoNumber 2) strPayType = Text 3) strDescription = Text
5. tblTimecard: 1) pkeyTimecardId = AutoNumber 2) intStaffNumber = Number 3) fkeyPayPeriodId = Number
6. tblTimecardHours: 1) pkeyTimecardDetailId = AutoNumber 2) fkeyTimecardId = Number 3) fkeyAcctId = Number 4) fkeyPayTypeId = Number 5) strAllocation = Text 6) intSat = Number 7) intSun = Number 8) intMon = Number 9) intTue = Number 10) intWed = Number 11) intThu = Number 12) intFri = Number
Here is the layout of my form in this order: 1. Pay Period Id 2. Week Ending 3. Employee Number 4. Employee Last Name 5. Employee First Name 6. Acct Id 7. Description 8. Cost Center 9. Acct 10. Category 11. Pay Type Id 12. Pay Type 13. Allocation 14. SAT 15. SUN 16. MON 17. TUE 18. WED 19. THU 20. FRI 18. Wk Hrs
Question 1: Please review table relationships to see if I overlooked any tables that can be broken down or named its column differently?
Question 2: I have restructured these tables into 3NF, please see my previous posts under AccessRookie (although before 3/2005, someone used this ID).
Question 3: what code do I need to automatically populate "Week Ending" field with the correct "CheckDate" that will compare against today's date and insert into "Week Ending" field? Currently, user selects from Combo Box(Pay Period Id) then it populates Text Box(Week Ending).
Question 4: how come "tblTimecardHours.fkeyTimecardId" field is not populating? I think it is my table relationship: tblTimecard & tblTimecardHours.
Question 5: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI). No need to store total since it is only need to display in data entry form (subform) and printing report.
I need your assistance since sometimes, it just takes another pair of eyes to review another peers' work. It's been ages since I've done any development from scratch. Help!!! Is there any way, I can attach my zipped database? It is 217KB zipped but this site only allow 100KB attachments. Sincerely, AccessRookie =)
I Have These Tables: Case, Agents, Forwarders, Orders. 1 Case Has Each Time 1 Agent, Many Forwarders And Each Forwarder Many Orders 1 Agent Many Forwarders Can Someone Suggest The Best Way To Link Them?
