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 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 Sub

4

1 Answer

I made a form filtering a query and tried to make a report. I got this same error saying can not lock table by database engine.

What I did is simply gone to the report in desighn view and changed Record Locks as No Locks. It worked.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.