I am trying to write a .vbs script that will add records to an access database. I can do that part alright however, I want to prevent a duplicates from being added.
If, in Access, i just don't allow duplicates, the vbscript will kick back an error about duplicates however I want the script to check if there is a duplicate and silently dismiss the error and stop processing. If there are no duplicates for that field in the database I want the script to add the fields.
My code to add the records is below. I want the script to check the database for the order number value being duplicate and if so just have msgbox say record already added or a record already added "overwrite existing record"
const adOpenStatic = 3
const adLockOptimistic = 3
Set objConnection = CreateObject ("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
dbasename = "M:\Mas200Customizations\PickPackDatabase\PickPack.mdb"
tblename = "PickPack"
objRecordSet.Open "SELECT * FROM " & tblename , objConnection, adOpenStatic, adLockOptimistic
objRecordSet.Fields.Item("OrderNumber").Value = SO_Shipping_bus_InvoiceNo
objRecordSet.Fields.Item("Picker").Value = SO_Shipping_bus_UDF_Picker
objRecordSet.Fields.Item("Packer").Value = SO_Shipping_bus_UDF_Shipper
objRecordSet.Fields.Item("Shipper").Value = SO_Shipping_bus_ShipperID
Msgbox "Shipper,Picker, and Packer Successfully Added To Database!"