Append query in macro get key violation - by itself no problem

[Don Johnson]

I have a macro in Access 2007 that executes some VBA code which creates a
number of tables and then runs a series of append and update queries to
append records to ODBC linked SQL files.

When the append queries listed below is executed by the macro, I get a key
violations message, "Microsoft Office Access can't append all of the records
in the append query. Microsoft Office Access set 0 field(s) to Null due to a
type conversion failure, and it didn't add 2 record(s) to the table due to
key violations, 0 record(s) due to lock violations, and 0 record(s) due to
validation rule violations. Do you want to run the action query anyway? To
ignore the error(s) and run the query, click Yes. For an explanation of the
causes of the violations, click Help."

I am trying to add two records at this point. Clicking yes does not add
them. Clicking on the Help button simply gets me to a generic Access Help
window with no specific link to my problem.

Yet, if I run the the append query by itself, rather than through the macro,
I do not get a key violation and the records are added.

I suspect the key violations relate to stored procedures in the SQL file
concerning how the date and time fields are stored. I cannot understand if I
have solved these so that the append query will execute properly by itself,
why it should thwen have a problem when run in a macro.

The append query is SQL view is as follows:
INSERT INTO dbo_IV00102 ( ITEMNMBR, LOCNCODE, BINNMBR, RCRDTYPE, PRIMVNDR,
ITMFRFLG, BGNGQTY, LSORDQTY, LRCPTQTY, LSTORDDT, LSORDVND, LSRCPTDT,
QTYRQSTN, QTYONORD, QTYBKORD, QTY_Drop_Shipped, QTYINUSE, QTYINSVC, QTYRTRND,
QTYDMGED, QTYONHND, ATYALLOC, QTYCOMTD, QTYSOLD, NXTCNTDT, NXTCNTTM,
LSTCNTDT, LSTCNTTM, STCKCNTINTRVL, Landed_Cost_Group_ID, BUYERID, PLANNERID,
ORDERPOLICY, FXDORDRQTY, ORDRPNTQTY, NMBROFDYS, MNMMORDRQTY, MXMMORDRQTY,
ORDERMULTIPLE, REPLENISHMENTMETHOD, SHRINKAGEFACTOR, PRCHSNGLDTM,
MNFCTRNGFXDLDTM, MNFCTRNGVRBLLDTM, STAGINGLDTME, PLNNNGTMFNCDYS,
DMNDTMFNCPRDS, INCLDDINPLNNNG, CALCULATEATP, AUTOCHKATP, PLNFNLPAB,
FRCSTCNSMPTNPRD, ORDRUPTOLVL, SFTYSTCKQTY, REORDERVARIANCE, PORECEIPTBIN,
PORETRNBIN, SOFULFILLMENTBIN, SORETURNBIN, BOMRCPTBIN, MATERIALISSUEBIN,
MORECEIPTBIN, REPAIRISSUESBIN, ReplenishmentLevel, POPOrderMethod,
MasterLocationCode, POPVendorSelection, POPPricingSelection, PurchasePrice,
IncludeAllocations, IncludeBackorders, IncludeRequisitions,
PICKTICKETITEMOPT, INCLDMRPMOVEIN, INCLDMRPMOVEOUT, INCLDMRPCANCEL,
DEX_ROW_ID )
SELECT tblItemQuantityMstr.ITEMNMBR, tblItemQuantityMstr.LOCNCODE,
tblItemQuantityMstr.BINNMBR, tblItemQuantityMstr.RCRDTYPE,
tblItemQuantityMstr.PRIMVNDR, tblItemQuantityMstr.ITMFRFLG,
tblItemQuantityMstr.BGNGQTY, tblItemQuantityMstr.LSORDQTY,
tblItemQuantityMstr.LRCPTQTY, tblItemQuantityMstr.LSTORDDT,
tblItemQuantityMstr.LSORDVND, tblItemQuantityMstr.LSRCPTDT,
tblItemQuantityMstr.QTYRQSTN, tblItemQuantityMstr.QTYONORD,
tblItemQuantityMstr.QTYBKORD, tblItemQuantityMstr.QTY_Drop_Shipped,
tblItemQuantityMstr.QTYINUSE, tblItemQuantityMstr.QTYINSVC,
tblItemQuantityMstr.QTYRTRND, tblItemQuantityMstr.QTYDMGED,
tblItemQuantityMstr.QTYONHND, tblItemQuantityMstr.ATYALLOC,
tblItemQuantityMstr.QTYCOMTD, tblItemQuantityMstr.QTYSOLD,
tblItemQuantityMstr.NXTCNTDT, tblItemQuantityMstr.NXTCNTTM,
tblItemQuantityMstr.LSTCNTDT, tblItemQuantityMstr.LSTCNTTM,
tblItemQuantityMstr.STCKCNTINTRVL, tblItemQuantityMstr.Landed_Cost_Group_ID,
tblItemQuantityMstr.BUYERID, tblItemQuantityMstr.PLANNERID,
tblItemQuantityMstr.ORDERPOLICY, tblItemQuantityMstr.FXDORDRQTY,
tblItemQuantityMstr.ORDRPNTQTY, tblItemQuantityMstr.NMBROFDYS,
tblItemQuantityMstr.MNMMORDRQTY, tblItemQuantityMstr.MXMMORDRQTY,
tblItemQuantityMstr.ORDERMULTIPLE, tblItemQuantityMstr.REPLENISHMENTMETHOD,
tblItemQuantityMstr.SHRINKAGEFACTOR, tblItemQuantityMstr.PRCHSNGLDTM,
tblItemQuantityMstr.MNFCTRNGFXDLDTM, tblItemQuantityMstr.MNFCTRNGVRBLLDTM,
tblItemQuantityMstr.STAGINGLDTME, tblItemQuantityMstr.PLNNNGTMFNCDYS,
tblItemQuantityMstr.DMNDTMFNCPRDS, tblItemQuantityMstr.INCLDDINPLNNNG,
tblItemQuantityMstr.CALCULATEATP, tblItemQuantityMstr.AUTOCHKATP,
tblItemQuantityMstr.PLNFNLPAB, tblItemQuantityMstr.FRCSTCNSMPTNPRD,
tblItemQuantityMstr.ORDRUPTOLVL, tblItemQuantityMstr.SFTYSTCKQTY,
tblItemQuantityMstr.REORDERVARIANCE, tblItemQuantityMstr.PORECEIPTBIN,
tblItemQuantityMstr.PORETRNBIN, tblItemQuantityMstr.SOFULFILLMENTBIN,
tblItemQuantityMstr.SORETURNBIN, tblItemQuantityMstr.BOMRCPTBIN,
tblItemQuantityMstr.MATERIALISSUEBIN, tblItemQuantityMstr.MORECEIPTBIN,
tblItemQuantityMstr.REPAIRISSUESBIN, tblItemQuantityMstr.ReplenishmentLevel,
tblItemQuantityMstr.POPOrderMethod, tblItemQuantityMstr.MasterLocationCode,
tblItemQuantityMstr.POPVendorSelection,
tblItemQuantityMstr.POPPricingSelection, tblItemQuantityMstr.PurchasePrice,
tblItemQuantityMstr.IncludeAllocations,
tblItemQuantityMstr.IncludeBackorders,
tblItemQuantityMstr.IncludeRequisitions,
tblItemQuantityMstr.PICKTICKETITEMOPT, tblItemQuantityMstr.INCLDMRPMOVEIN,
tblItemQuantityMstr.INCLDMRPMOVEOUT, tblItemQuantityMstr.INCLDMRPCANCEL,
tblItemQuantityMstr.DEX_ROW_ID
FROM tblItemQuantityMstr;

Any thoughts as to how I can get this append query to run in the macro?

 

[Mark Han[MSFT]]

Hi Don,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when you executed a macro in
access 2007, an error happened.
If I have misunderstood, please let me know.

in order to resolve the issue, I would like to explain the following
1 based on your description, I know that the append query is able to
executed successfully out of the macro. also I know in the macro, some VBA
code is executed to create the destination tables. that might be the casue
of the issue.

2 Based on the error message, I would like to explain:
--Lock Violation — Is the destination table locked? The destination table
could be open in Design view or open by another user on the network. This
would cause a lock violation and make the append query fail.

--Key Violation — The destination table may have a primary key or an index
with the No Duplicates property set to Yes. Check the design of the
destination table for the primary key and any index with No Duplicates set
to Yes. Check the data you are appending to make sure there are no key
violations in the primary key or any index with No Duplicates set to Yes.

--Validation Rule Violation — Check the design of the destination table for
any required fields. If any field is required and you do not append data to
this field, you get this error. Also, check the destination table for any
Text fields with Allow Zero Length set to No. If you are appending blank
data to a Text field that has Allow Zero Length set to No, you can get this
error. Also, check for validation rules for the fields. For example, you
may have the following validation rule for the Quantity field: >=10 — in
this case, you cannot append records with a quantity less than 10.

Besides, there is an article to share with
you:http://office.microsoft.com/en-us/access/HA011860631033.aspx

Hope the above helpful.

If there is anything unclear, please do not hesitate to let me know.

Have a nice day.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Don Johnson]

Dear Mark:

Thank you for your input.

After posting this message, I did some more experimenting. I created a new
macro that only ran the append query for which I was getting the key
violations. That macro ran fine with no error or key violations.

To that macro, I then added another append query which added records to a
different SQL table but one that used the same ODBC link as the first append
query. This time the first append query ran with no problem, but when it got
to the second append query, which was the one that I had previously gotten
the key violation error, I again got this error.

I then reversed the order of the two append queries in this macro and tried
again. This time, the first append query, which is the one for which I was
getting the error, completed with no errors, but now the second append query
gave me a key violation error.

These append queries as running against a test database in which I am the
only one who accesses this database and these tables.

After further deliberation, I ran across a article discussing macros versus
VBA, which gave me an idea to include the running the of the append query
that I was giving me the most problems from the VBA rather than the macro and
use the macro to run the other append queries and the update query from the
macro. This worked with no key violations.

So while my immediate problem is solved, it still bugs me why I would get
the error. It was almost as though the macro was operating too fast and
Access or SQL was unable close the previous table and this somehow locked the
next table.

 

[Mark Han[MSFT]]

Hi Don,

Thank you for the reply and confirmation. I'm glad that my explanation is
helpful.

Based on your description, I know that the issue is resolved now and you
would like to know the root cause. So if it is convenient to you, please
post the script (including VBA and append query script) which runs without
error; and also post the script which runs with error.

Besides, I agree with you that the table might be locked somehow.

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================