[AccessD] Problem Writing to table

[Rocky Smolin]

I think I found it.  Writing to the list always makes me think of other
WAGs.

In this case if a record did not need processing but was transferred intact
to the target table I used this insert statement:

db.Execute "INSERT INTO tblWorkOrderHistory SELECT
tblWorkOrderHistory_Temp.* FROM " _
                & "tblWorkOrderHistory_Temp WHERE
tblWorkOrderHistory_Temp.fldWOHistoryID = " _
                & rsWOHistoryTemp!fldWOHistoryID & ";"

Which included the PK.  So I changed it to:

            db.Execute "INSERT INTO tblWorkOrderHistory_Temp (
fldWOHWorkOrder, fldWOHWOPartNumber, " _
                & "fldWOHComponentPartNumber,
fldWOHWorkOrderQuantityAllocated, fldWOHQPA, fldWOHQuantityIssued, " _
                & "fldWOHSource, fldWOHPONumber, fldWOHPOUOM, fldWOHBOMUOM,
fldWOHUOMConversion, fldWOHUnitCost, " _
                & "fldWOHTotalCost, fldWOHCurrency, fldWOHExchangeRate,
fldWOHCostUSD, fldWOHComment ) " _
                & "SELECT tblWorkOrderHistory.fldWOHWorkOrder,
tblWorkOrderHistory.fldWOHWOPartNumber, " _
                & "tblWorkOrderHistory.fldWOHComponentPartNumber,
tblWorkOrderHistory.fldWOHWorkOrderQuantityAllocated, " _
                & "tblWorkOrderHistory.fldWOHQPA,
tblWorkOrderHistory.fldWOHQuantityIssued, " _
                & "tblWorkOrderHistory.fldWOHSource,
tblWorkOrderHistory.fldWOHPONumber, " _
                & "tblWorkOrderHistory.fldWOHPOUOM,
tblWorkOrderHistory.fldWOHBOMUOM, " _
                & "tblWorkOrderHistory.fldWOHUOMConversion,
tblWorkOrderHistory.fldWOHUnitCost, " _
                & "tblWorkOrderHistory.fldWOHTotalCost,
tblWorkOrderHistory.fldWOHCurrency, " _
                & "tblWorkOrderHistory.fldWOHExchangeRate,
tblWorkOrderHistory.fldWOHCostUSD, " _
                & "tblWorkOrderHistory.fldWOHComment FROM
tblWorkOrderHistory " _
                & "WHERE tblWorkOrderHistory.fldWOHistoryID = " &
rsWOHistoryTemp!fldWOHistoryID & ";"

Which appends all the fields except the PK and walla! It (seems to ) work!

Tks

Rocky


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
jack drawbridge
Sent: Thursday, October 20, 2016 1:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem Writing to table

Rocky,

3022 is usually very definite ---duplicate.

How are you ensuring records are in a specific physical location???
Records are placed randomly (at least by algorithms I don't thiink I can
control). I always regard records in tables as "junk in a box". If you want
to process them in a specific order, use a query with an Order By.

Good luck.

On Thu, Oct 20, 2016 at 3:53 PM, Rocky Smolin <rockysmolin at bchacc.com>
wrote:

> Dear List:
>
>
>
> This is a problem with an mdb running on A2010.  Although I don't 
> think that may be relevant.
>
>
>
> I am getting an error 3022: The changes you requested to the table 
> were not successful because they would create duplicate values in the 
> index, primary key, or relationship."
>
>
>
> The scenario:
>
>
>
> I have a table (Table 1) I need to operate on and depending on the 
> record, either add some records to Table 1 right after the record in 
> question or just go on to the next record.
>
>
>
> The added records have to be in a specific order merged with the 
> existing records - can't just add to the end of the Table 1.  So I 
> copy all the records to a temp table (Table 2), which has the 
> identical structure as the Table 1.
>
>
>
> Both tables have autonumber PKs. Both table have identical structures.
> Except for the PK no fields are indexed.  When I copy from Table 1 to 
> Table 2, I select all the fields in Table 1 except for the PK.
>
>
>
> Then I loop through Table 2 table either 1) adding the record from 
> Table 2 to Table 1 using an INSERT statement, or add some additional 
> records to Table 1 and do not copy the record from Table 2 to Table 1.
>
>
>
> After about 60 or so records added to Table 2, I get the error.
>
>
>
> Since the only indexed field is the autonumber PK, I can't see how a 
> duplicate value could be generated.  But it is.  So that means 
> something else is going on here but I can't imagine what.
>
>
>
> Has anyone encountered this before or have any ideas how to get around it?
>
>
>
>
>
> MTIA
>
>
>
>
>
> Rocky Smolin
>
> Beach Access Software
>
> 760-683-5777
>
>  <http://www.bchacc.com> www.bchacc.com
>
>  <http://www.e-z-mrp.com> www.e-z-mrp.com
>
> Skype: rocky.smolin
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com