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