Resolving Duplicate Vendor Sync Errors.

The following error may be received if a vendor has been deleted from QuickBooks or if a QuickBooks rebuild recreated the related Vendor record, assigning a new internal ID to the vendor.

Error    Vendor    [SynchronizeQBVendors]Microsoft OLE DB Provider for SQL Server (-2147217873) The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.tbVendor’ and the index name ‘Vendor_VendorID’. The duplicate key value is (SomeVendorID).

The following error can be resolved one of two ways.

Option 1

If the related Vendor was manually removed in QuickBooks you should try selecting a different vendor on any open purchase orders that referenced the old Vendor record.  You can accomplish this by opening up the Purchase Order window, selecting the Lookup button, then filtering the PO Lookup by Vendor Name.

Option 2

Assuming the vendor wasn’t purposely deleted from QuickBooks, you can follow these steps to resolve the vendor issue.

1. In QuickBooks, find the related vendor that has been recreated, possibly by a QuickBooks verify and rebuild.
2. Rename the vendor in QuickBooks to be something similar, e.g. rename Vendor ‘ABCVendor’ to be ‘ABCVendor2’, and save.
3. Run a Sync with QuickBooks in Acctivate.
4. Open the two vendors in tbVendor.  You will need to copy the VendorListID value from the newly created ‘ABCVendor2’ vendor record and set this as the new VendorListID for the old ‘ABCVendor’ vendor record.  This step will require access to the Acctivate company database.  We recommend you contact our Support Team to assist you with this process.
5. Delete the newly created ‘ABCVendor2’ vendor record in the Acctivate database.
6. In QuickBooks, rename the Vendor record from ‘ABCVendor2’ to ‘ABCVendor’.  At this point, you should have only one vendor record in both Acctivate and QuickBooks and the name of the Vendor should match between the two as well.
7. Run a Sync.  You should no longer receive the sync error at this point.

The VendorListID field contains the value of the QuickBooks unique internal ID of the Vendor.  This is created and assigned by QuickBooks.  Option 2, above, is a set of instructions on how to relink the vendor record in Acctivate back to the vendor record in QuickBooks using the new VendorListID.