Append Queries not executing
Posted on 2014-04-14
I'm running Access 2013 application programs with SQL Server linked tables. Most people have the runtime version of Access on their computers. I created append queries to reference data from controls on forms and append a record to a table with that data (Note: The queries don't select records from any tables). I wrote VB code to execute these queries and they've worked fine for years, but lately, and intermittently, these queries are not being executed when the code runs.
If the table that the record is being appended to is locked, I've seen Access try to run the query for a certain amount of time, then eventually give up with an execution error (if there is no error handling) and kick people out of the program. This is not happening in this case. The user gets no warning and thinks that the data was processed as expected. Could this be a lock problem even though Access doesn't output any kind of error message? Has anyone ever seen Access randomly skip lines of codes when executing a procedure? The following is the SQL from one of the Append queries that intermittently isn't executing.
INSERT INTO ActualDistribs ( PartId, Quantity, LocationId, KitId, TransactionTypeId, StockPerson, StockDate, Notes, PartTrackInfoId )
SELECT forms!ReStockParts!PartId AS Expr1, forms!ReStockParts!tbxQuantity AS Expr6, IIf(forms!ReStockParts!TransactionTypeId=2,forms!ReStockParts!LocationId,forms!ReStockParts!PhysicalLocationId) AS Expr4, forms!ReStockParts!cbxKitId AS Expr5, IIf(forms!ReStockParts!TransactionTypeId=2,4,3) AS Expr8, CurrentUser() AS Expr7, Now() AS Expr3, forms!ReStockParts!tbxWO & " - Kit " & forms!ReStockParts!tbxKit AS Expr2, [forms]![ReStockParts]![PartTrackInfoId] AS Expr9;