473,841 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to clear lock on table if error 3211?

I'm trying to import an Excel Spreadsheet into an Access Table but sometimes
things get buttered up if the spreadsheet does not have the right columns.
So I want to drop the Access table and recreate it with a DDL query when
that happens. The problem is I get an error when trying to drop the table -
Error 3211: The database engine could not lock table 'tblExcelTx' because it
is already in use by another person or process.

Is there any way to clear the lock? All I want to do is blow away the
table...
Thanks in advance.
Nov 13 '05 #1
4 41056
deko wrote:
I'm trying to import an Excel Spreadsheet into an Access Table but sometimes
things get buttered up if the spreadsheet does not have the right columns.
So I want to drop the Access table and recreate it with a DDL query when
that happens. The problem is I get an error when trying to drop the table -
Error 3211: The database engine could not lock table 'tblExcelTx' because it
is already in use by another person or process.

Is there any way to clear the lock? All I want to do is blow away the
table...
Thanks in advance.


CurrentDb.Table Defs.Delete "tablename"

But that still doesn't handle the problem since if the table is in use
somewhere it can't be deleted. Have you opened a recordset on the table
somewhere? If so, close the recordset first.

Nov 13 '05 #2
> CurrentDb.Table Defs.Delete "tablename"

But that still doesn't handle the problem since if the table is in use
somewhere it can't be deleted. Have you opened a recordset on the table
somewhere? If so, close the recordset first.


Thanks - I'll give it a shot. The reason the table is locked is because it
gets assigned as the recordsource for a subform. I try resetting with
recordsource = "" before dropping the table but no luck...
Nov 13 '05 #3
deko wrote:
CurrentDb.Tab leDefs.Delete "tablename"

But that still doesn't handle the problem since if the table is in use
somewhere it can't be deleted. Have you opened a recordset on the table
somewhere? If so, close the recordset first.

Thanks - I'll give it a shot. The reason the table is locked is because it
gets assigned as the recordsource for a subform. I try resetting with
recordsource = "" before dropping the table but no luck...


I'm not sure you can change the record source of a subform when it's
open. Good luck in your quest.
Nov 13 '05 #4
> I'm not sure you can change the record source of a subform when it's
open. Good luck in your quest.


I ended up closing and reopening the form in error handling code:

Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case 3211
DoCmd.Close acForm, "frmTools"
DoCmd.OpenForm "frmTools"
End Select
Resume Exit_Here
End Function

kind of a hack but it works
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4581
by: leecho | last post by:
Hi, recently, i was assigned as a new dba for our system. I found that my statistic keep change from time to table. To look for the cause, i wanna to lock a table, means only allow user to insert. Others than that, noway. Can i use the Lock table command? or others comand? p/s: we are using 9i client and 6i developer regards,
0
4356
by: Colleyville Alan | last post by:
My app is giving me this error. Run-time error 3211: The database engine could not lock table 'Sorted_Template' because it is already in use by another person or process. When I run the app the first time through, it runs fine. I get this when I run a make-table query that tries to write out the table called "Sorted_Template". I tried using a TableDefs.Delete command thinking that make using the docmd.openquery was causing the...
1
7080
by: Sandy | last post by:
Hi I want to lock a table using JDBC as I want to perform some query's (read and write) in exclusive mode. Different threads will be executing the same code simultaneously. I am using the following statements Statement stmt = connection.createStatement(); stmt.executeUpdate("lock table STATUS_TBL in EXCLUSIVE mode"); but am getting the following error
0
2352
by: sang | last post by:
Hi I want to lock the table with both read and write there is no action held after lock the table. I know how to lock the table with read and write, lock table mytable read; lock table mytable write; lock table mytable read write;
3
10688
by: Himmel | last post by:
I have added a section of code to a function that is designed to copy data from several tables and place them into a single table. The new table already contains a unique ID and name, and I am working on adding data from the second table to it. The problem is that the second table has multiple records for each ID. For example, review this sample data: New Table: ID NAME OBJ1 1 Billy 2 Bobby 3 Sue
12
49447
by: ewarts | last post by:
Hey Guys, I have an access database with a few tables and forms. I was trying to add a new combo box to one of my forms and to do so i needed to add the referenced data to my reference data table. simple or so I thought, but while attempting to do so I got the following error ""database engine could not lock table because it is already in use by other " . I am currently the only user with access to this database so i'm at a lost as to how it...
1
1558
by: tembil | last post by:
Hi, can anyone plz help me, I'm trying to insert data into a table from another table and I'm getting this error: The total number of locks exceeds the lock table size, how can I fix this. thanx
3
2382
by: Kan09 | last post by:
i have (another) problem. The database i've been trying to design has been going trough a bit of testing. During those tests we've noticed that when trying to access a form from the swich board the "Error Executing the command" occurs (this is happening only when a user has allready accessed the form and others try to do the same). This is strange because the other 2 forms on the swichboard don't have this problem...and their design is...
3
7623
by: Frederick Tant | last post by:
DB2 V9.7 The DB is used by 1 application with no concurrent applications. I got the problem that large update/insert cause lock escalation, to solve the issue I set a lock on the table so there's no row locking any more. BUT after setting a lock on a table (LOCK TABLE <table> IN EXCLUSIVE MODE;) I remain having the message " DB2 is performing lock escalation". This together with very bad performance. Any sugestion why LOCK table...
0
9865
marktang
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...
0
9708
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10932
Oralloy
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...
0
10609
jinu1996
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...
1
10668
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9446
agi2029
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...
1
7851
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4498
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
3
3140
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.