Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Append Queries not executing

Posted on 2014-04-14
5
Medium Priority
?
420 Views
Last Modified: 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;
0
Comment
Question by:Declan_Basile
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39999578
How are you executing this query?  

Is it saved with parameters defined for each of the form control references?
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39999624
I'm actually surprised that this query will run at all, since there is no table referenced in the SELECT portion of the query.

Normally, when I want to do an insert like this, I would write and save the query, something like:

INSERT INTO ActualDistribs ( PartId, Quantity, LocationId, KitId, TransactionTypeId, StockPerson, StockDate, Notes, PartTrackInfoId )
Values ([FormPartID], [FormQuantity], [FormLocationID], [FormKitID], [FormTransTypeID],
             [FormStockPerson], [FormStockDate], [FormNotes], [FormPartTrackInfoID])

I would then declare each of those parameters in the query, and in my code, I would open a querydef to that query and set the parameters:
Dim db as DAO.Database
Dim qdf as DAO.QueryDef

Set db = Currentdb()
set qdf = db.QueryDefs("MyAppendQuery")

qdf.Parameters("FormPartID") = me.PartId
qdf.parameters("FormQuantity") = me.tbxQuantity
qdf.parameters("FormLocationID") = IIf(me.TransactionTypeId=2, me.LocationId, me.PhysicalLocationId) 

qdf.parameters("FormKitID") = me.cbxKitId
qdf.parameters("FormTransTypeID") = IIf(me.TransactionTypeId=2,4,3)
qdf.parameters("FormStockPerson") = CurrentUser()
qdf.parameters("FormStockDate") = Now()
qdf.parameters("FormNotes") = me.tbxWO & " - Kit " & me.tbxKit AS Expr2
qdf.parameters("PartTrackInFoID") = me.[PartTrackInfoId]

qdf.execute dbfailonerror

set qdf = nothing
set db = nothing

Open in new window

By using the dbfailonerror argument with the Execute method, I am assured of getting an error raised if anything goes wrong with the insert operation.
0
 
LVL 1

Author Comment

by:Declan_Basile
ID: 39999877
I used this code ... Docmd.OpenQuery "MyAppendQuery" ... to run the query.

No I didn't declare the parameters in the query.

Thank you very much for the code to raise an error if the query doesn't run correctly.  That's exactly what I needed.  I'm surprised that I used this implementation for years with no known problems.  I know that an error is raised when using  DoCmd.OpenQuery for append queries that select records from tables for example if there is a duplicate key problem, but do you know if an error is raised for append queries that select records from tables if there is a lock problem?  I'm questioning if I should change my code for all append queries or just the ones that don't select any records from tables.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40000016
Well, with that method, you would not know, especially if you have turned off your warnings using the docmd.SetWarnings method.

Currentdb also has an execute method, for executing SQL strings you develop on the fly.

NO, I don't know about an error associated with a record lock, but would assume there is one.  There are a number of record locking error codes listed on the FMS website.

https://www.fmsinc.com/MicrosoftAccess/Errors/ErrorNumber_Description.html
0
 
LVL 1

Author Closing Comment

by:Declan_Basile
ID: 40000154
Thanks very much.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question