I am trying to re-create the existing table once it gets updated via form. However, I am getting error "The database engine could not lock table "tbl_test" because it is already used by another person or process".
Please help me find a way to get around this
Thanks
Aug 2 '10
49  5983 
Subform/Datasheet is bound to tableB (which is created by a crosstab query) and TableA is being updated using VBA sqlstring. Is there any way I can close the form once I close it, I recreate the table? I tried doing that using docmd.close and docmd.open method but when I try to run the macro which recreates the TableB, it gives me the error mentioned before. Any way to clear the MS Access memoro / unlock that table?
Thanks a lot for your help
Subform/Datasheet is bound to tableB (which is created by a crosstab query) and TableA is being updated using VBA sqlstring. Is there any way I can close the form once I close it, I recreate the table? I tried doing that using docmd.close and docmd.open method but when I try to run the macro which recreates the TableB, it gives me the error mentioned before. Any way to clear the MS Access memoro / unlock that table?
Thanks a lot for your help.
Memory really doesn't have anything to do with it. More than likely the error is caused because your code is in the form module and if you think about, the form really isn't closed if you're running code out of it. And if the form is n't closed, then anything bound to it will be locked.
Mainly I think you need to realize that the error is caused by the fact that you're trying to operate outside of a 'best-practice'. I'm sure there are ways around it, but if this is a sizeable table then you're bloating you're database everytime you do this and causing the DB to creep ever closer to it's size limit for the sake of adding one record. This affect will grow with every record. You'll also preclude this form from ever being used in a multi-user environment.
There are usually several ways to code something, some better than others and it's ok if you get it working with a less desirable method. But sometimes there's a case where the method is just the wrong way, even if you can get it to work. This is one of those cases.
My table is really small ~4000 records and I am updating everytime user click refresh button. Any other way to do this by staying in the same form? I know I can have another form pop up for the update and once it gets updated I can display the result in the 1st form via query as a source of that datasheet. But I do want to avoid going into another form for the updates
Without seeing it directly, I can't really say what to do. Attached is a DB that has what I think you are trying to do.
Open the form called "TestForm_wSubf orm". The main form is bound to a table in which you can add new records. The subform is based on a crosstab QUERY that will update when you click on the button.
They're linked on the Branch field so you'll have to use a preexisting branch number to see it work
Here's the db that I have with truncated data. Please let me know if I can explain it to you futher
Sorry, I only have Access 2003 so I can't view this. You may be able to convert it though on your end and resend.
Here you go. Thank you so much for your help
What parts of the form is the user updating specifically
last subform with 2 lines
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: randi_clausen |
last post by:
Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!
|
by: Oreo Bomb |
last post by:
I have a secured database that contains a Read-Only group. This group
has permissions to view reports, but cannot add, edit, or delete any
DB objects. One of the reports the group needs access to is generated
with a Make-Table Query. Since the Make-Table Query deletes the
previous table it created everytime it's ran, this poses a problem
since the Read-Only group doesn't have the permission to delete
objects. This causes the report...
|
by: DebbieG |
last post by:
I have a form based on this query:
SELECT Students.LastSerDT, OtherInfo.Served, OtherInfo.HSGradYr,
OtherInfo.ActivePart, OtherInfo.Served, Students.SSN, & ", " &
& " " & AS Name, Students.LastNM, Students.FirstNM,
Students.MI, Students.DOB, Students.GenderCD, Students.EthnicityCD,
Students.EligibilityCD, Students.UBInitiative, Students.NCESSchID,
Students.ProjEntryDT, Students.ProjReEntDT, Students.LastSerDT,
Students.Reason,...
|
by: bmoos1 |
last post by:
I have a form that when the monthly payment is entered, it calculates
the annual payment. The correct numbers appear in the form, but they
are not in the table or query from which the form was created. The
calculation is done in the form. All new record entries update the
query/table, but I need to get those records that were there before the
form was created updated with the annual payment so that my year-end
report is correct. I...
|
by: Doc |
last post by:
Per earlier post, I am trying to save 'out' production data from a
program called Solomon - basically (I think) this was /is an Access/Sql
based program.
We are updating to different application and I need to bring over ONLY
certain DATA.
When I did an ODBC link to the DB all the tables are present.
I linked the three or four tables and have my fields (these are the ones
I want to modify for LATER import into the new application)
| | |
by: The Frog |
last post by:
Hello Everyone,
I have been asked to try and create a single SQL query to retrieve
product information from a database. The way that data is arranged is
that in some tables there are user defined "attributes" or
"dimensions" that in turn connect to the actual product table via a
many-many (using a linking table). In each linking table there is a
combination of the "dimension", the productID, and the "fact" that is
stored against the...
|
by: sbitaxi |
last post by:
I have a query that performs a simple sum, a running total
essentially. I want to be able to use that in a calculation in a form
but I can't seem to get it to display anything beyond #Name?
Structure -
Table A feeds the primary form, recording particulars about a mailing
and calculating costs
Table B feeds a sub-form, line items to track individual expenses in
the mailing
|
by: MNNovice |
last post by:
I have a Form where there are various combo boxes. These combo boxes are used as references for the parameter of various fields in a query. A command button opens a query with the parameters selected from the form.
I am having problem with two combo boxes which pulls data from the same table. These two combo boxes are called: txtActor and txtDirector. I am trying to generate a query to display any combination of actor and director (Or...
|
by: Laurel |
last post by:
I am completely new to access. I have a make-table query that combines data from two/more tables, asks for a criteria, deletes the old table and produces a new table every time under the same name. The table is always linked up with a form to allows user to fill in certain rolls of the table.
A. How would you make the form pop out after the make-table query is done and the new table is made? Can I add a openform procedure in the sql code of...
|
by: Debbie Toney |
last post by:
Access 2007 3 table query: Contact table, Event table, Payment table. Contact may have an Event but no Payment, a Payment but no Event, or an Event and Payment. My query is duplicating the Event and Payments. I have attached an example: The Contact Smith has 2 Events (Rome night and Men club) and 2 Payments ($4000 and $100) that are not associated. I want the query to return 4 records: 2 showing the Payments with associated Contact info...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
