Access 2010 Runtime Error 3211
help? Post your question and get tips & solutions from a community of 418,417 IT Pros & Developers. It's quick run-time error '3211' the database engine could not lock table & easy. How to clear lock on table if error 3211? P: n/a the database engine could not lock table access 2013 deko I'm trying to import an Excel Spreadsheet into an Access Table but sometimes things get buttered up if database engine could not lock table because it is already in use by other 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 Post Reply Share this Question 4 Replies P: n/a Salad 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.TableDefs.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 P: n/a deko > CurrentDb.TableDefs.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 reas
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. https://bytes.com/topic/access/answers/204233-how-clear-lock-table-if-error-3211-a Join them; it only takes a minute: Sign up “The Database Could Not Lock Table” Error when trying to close report, kill current tables tied to report up vote 0 down vote favorite I have a report that updates from tables that export from a separate database. I have the report on a timer to requery every 5 http://stackoverflow.com/questions/20225637/the-database-could-not-lock-table-error-when-trying-to-close-report-kill-curr minutes. The process that I am trying to execute is close the report, kill the tables behind the query that generates the report, import the the updated files to tables with the same names that were just killed and reexecute the query and report. I keep getting Run-time error '3211': "The Database Could Not Lock Table". Code is below and any help for this novice is greatly appreciated. Private Sub Report_Timer() 'Close Report DoCmd.Close acReport, "SMT Progress Report" 'Kill existing tables Set dbs = CurrentDb dbs.TableDefs.Delete ("SMT2Updated") ' error here dbs.TableDefs.Delete ("SMT3Updated") dbs.TableDefs.Delete ("SMT4Updated") dbs.TableDefs.Delete ("SMT5Updated") Set dbs = Nothing 'Import Files to Tables DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SMT2Updated", "\\ct13nt003\mfg\SMT_Schedule_Files\SMT Line Progress Files\SMT2Updated.xlsx", True DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SMT3Updated", "\\ct13nt003\mfg\SMT_Schedule_Files\SMT Line Progress Files\SMT3Updated.xlsx", True DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SMT4Updated", "\\ct13nt003\mfg\SMT_Schedule_Files\SMT Line Progress Files\SMT4Updated.xlsx", True DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SMT5Updated", "\\ct13nt003\mfg\SMT_Schedule_Files\SMT Line Progress Files\SMT5Updated.xlsx", True 'Refresh and Open Query/Report Me.Requery DoCmd.OpenReport "SMT Progress Report", acViewReport 'Export status file DoCmd.OutputTo acOutputReport, "SMT Progress Report Export Only", acFormatPDF, "\\ct13nt003\MFG\SMT Live Report\SMTLive" & "_" & Format(Now(), "mmddyyyy-hhmm") & ".pdf", False End S
Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login https://www.experts-exchange.com/questions/23606755/Runtime-error-3211-using-Access-VBA.html Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts http://www.utteraccess.com/forum/runtime-error-3211-t1981743.html Exchange > Questions > Runtime error 3211 using Access VBA Want to Advertise Here? Solved Runtime error 3211 using Access VBA Posted on 2008-07-30 MS Access 1 Verified Solution 7 Comments 3,432 Views Last Modified: 2013-11-27 Hi experts, I encountered the run-time error 3211: The databse engine couldn't lock 'TempTable' because it is in use by another person or process. Currently, database engine I built in the function in MS Access using VBA such that each time I click on "Update" button, a table "TempTable" will be imported from Excel. Then information I need will be picked up from "TempTable" and copied to other tables that I have. After this, the "TempTable" will be deleted after the command: CurrentDb.Execute "DROP TABLE TempTable" This run-time error happens each time when I try to click on "Update" for the second time. If database engine could I close the program, restart it and click on "Update", it works fine. Can anyone help me explain the error? Any solutions on this? Thanks! 0 Question by:angie_angie Facebook Twitter LinkedIn Google LVL 16 Best Solution byRick_Rickards It's probably inside the code that's tapping the TempTable. Any Open Recordsets that may still be attached to it? Might help to see the code you're using when you Click Update. Go to Solution 7 Comments LVL 16 Overall: Level 16 MS Access 15 Message Accepted Solution by:Rick_Rickards2008-07-30 It's probably inside the code that's tapping the TempTable. Any Open Recordsets that may still be attached to it? Might help to see the code you're using when you Click Update. 0 LVL 64 Overall: Level 64 MS Access 42 Message Active today Expert Comment by:Jim Horn2008-07-30 {having a vague memory here} If you edit/delete a table in VBA code, you need to do a refresh of the database window before you can use that table again. With CurrentDb .Execute "DROP TABLE TempTable" Application.RefreshDatabaseWindow '<-- This has to happen here .Execute "SELECT stuff INTO TempTable FROM StuffBox" End With 0 LVL 16 Overall: Level 16 MS Access 15 Message Expert Comment by:Rick_Rickards2008-07-30 Actually to amplify Jim's statement if your'e code is not seeing the table you may also need to Refresh the Tabledefs collection. If you'r
Custom Search UtterAccess Forums>Microsoft® Access>Access Modules Runtime Error 3211 Forum HomeSearchHelpUA Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- Access Code Archive|-- Access Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- Access Queries|-- Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access Database Container|-- Tool bars, Menu bars + Ribbon|-- Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|-- Q & A - Access Wiki Options Wood ButcherView Member Profile Feb 5 2012, 12:51 AM Post#1Posts: 54Joined: 16-December 10Set up this little chunk of code. There is one query that has all the information i need (UpcomingLoads) and a requery off of that which is a summation query showing just the email addresses (UpcomingLoadsEmails)This code goes thru and basiclly makes a tmpTableU