identify key violations

slimjen1 · Jan 13, 2012

All,

Using Access 2003. I have a database with 4 users who import records from a spreadsheet. Im using the Transferspreadsheet method and appending to a temporary table then a main table with primary key. When they run the import; it gives them a warning that "some records were not able to append due to key violations. It tells them how many but not what they are. The import may consist of up to 1000 records. They need a report that identifies which records are in Key Violation. I know as a developer I can go into the BE table and do a comparision. But how do I set it up to give them a report identifying the specific duplicates so they can make the necessary changes to reimport? I hope I made it clear enough to understand. Thank you

MSAccessRookie · Jan 13, 2012

slimjen1 said:
All,

Using Access 2003. I have a database with 4 users who import records from a spreadsheet. Im using the Transferspreadsheet method and appending to a temporary table then a main table with primary key. When they run the import; it gives them a warning that "some records were not able to append due to key violations. It tells them how many but not what they are. The import may consist of up to 1000 records. They need a report that identifies which records are in Key Violation. I know as a developer I can go into the BE table and do a comparision. But how do I set it up to give them a report identifying the specific duplicates so they can make the necessary changes to reimport? I hope I made it clear enough to understand. Thank you

Start by building the Query that you refer to. If you can create a Query that will "go into the BE table and do a comparision", you can also create a report that is based on that Query which should provide what the users are interested in.

-- Rookie

slimjen1 · Jan 13, 2012

Thank you for the reply. I need to know how to go about doing this because when the append query runs; it does not let me know which records do not append. It just does not append. So how can I tell the database to put those records into a specific table. I can go from there to create a report based on a query using that table. But the key is to have the records that do not append go into that table.
Thanks

spikepl · Jan 13, 2012

Follow MsAccessRookie's advice and run the SELECT query identifying doubles prior to appending the data to the main table.

slimjen1 · Jan 13, 2012

O I see. Below is my code:

Code:

Public Function ImportText()
 On Error GoTo Err_ImportText
 
'Delete records from table
            
            DoCmd.RunSQL ("delete from tblSP_SHIPMENT_temp")
            DoCmd.RunSQL ("delete from tblDTF_SHIPMENT_temp")
                        
'Import Text files into corresponding tables
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
   "tblSP_SHIPMENT_temp", "S:\NSC Public\FINANCE\ACFAST\UPLOADS\INVOICEREPORT\SMALLPACKAGEIMPORT.xls", True
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    "tblDTF_SHIPMENT_temp", "S:\NSC Public\FINANCE\ACFAST\UPLOADS\INVOICEREPORT\DUTIESANDTAXESIMPORT.xls", True

[COLOR="Red"]'New query would go here before appending to perm table???[/COLOR]DoCmd.OpenQuery "qry_DailyInvoiceReportAppend"
DoCmd.OpenQuery "qry_DailyInvoiceReportAppend_DTF"

MsgBox "Import Completed"

Exit_ImportText:
    Exit Function

Err_ImportText:
    MsgBox Err.Description
    Resume Exit_ImportText

End Function

So; create a query that identified the dups between the perm and temp tables before appending the temp to the perm tables?

identify key violations

slimjen1

Registered User.

MSAccessRookie

AWF VIP

slimjen1

Registered User.

spikepl

Eledittingent Beliped

slimjen1

Registered User.

Similar threads

Users who are viewing this thread