UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Capture Duplicate Record Error On Append., Access 2016    
 
   
dflak
post Aug 9 2018, 11:14 AM
Post#1


Utter Access VIP
Posts: 6,245
Joined: 22-June 04
From: North Carolina


The attached is an application that appends Data in an Excel Table to an Access Table. It works by writing an "Instruction" XML file (thanks to Jon Smith on this board). If you unzip this to a "spare" folder, you can configure it by changing the value in Cell C5 on the control panel on the spreadsheet to the path of the folder.

Click on the Make XML File and it will create Table_Inventory.xml in the same directory.

I've written code to check that the Excel Table exists and that the specified headers exist in the table. The code also checks that the database exists, the table exists and the specified fields in the table exist.

When you click on the Append to Data Button. The rows in the Excel Table are appended to the Access Table.

Here are my requests.

Request 1: In the example, I have a key field composite of Date & SKU. If I attempt to add the same date and SKU to the table, I will get an error message. What I would like to do is trap the error, and warn the user and exit the program gracefully. FYI, the existing two records in the Excel Table have the formula =Now() so they will not produce a duplicate record. Add in a fixed date to test. Ideally, I'd like to get a count of records added and records rejected. In my real world, it will most likely be all or none, so stopping on the first error is acceptable.

Request 2: I would like to do a better check on data types. For example, If inv_date is a Date, I'll need to check my Excel table to make sure it's not passing a string or some other invalid data type before I attempt the append. I'll work that out on the Excel side, but I need to know what data type it is going into. So, while I am checking to see that the field exists, I also need to get its data type.

I have a strong background in VBA but only an intermediate level knowledge of MS-Access.
Attached File(s)
Attached File  XML_Append_to_Access_DB.zip ( 183.02K )Number of downloads: 1
 
Go to the top of the page
 
dflak
post Aug 10 2018, 10:53 AM
Post#2


Utter Access VIP
Posts: 6,245
Joined: 22-June 04
From: North Carolina


I got part 2 done. Version 1.1 of the program preforms a data check on the Excel Table before importing the data.


Attached File(s)
Attached File  XML_Append_to_Access_DB_1.1.zip ( 240.14K )Number of downloads: 0
 
Go to the top of the page
 
dflak
post Aug 10 2018, 01:47 PM
Post#3


Utter Access VIP
Posts: 6,245
Joined: 22-June 04
From: North Carolina


Requirement #1 solved.

My primary concern was with detecting duplicate record errors.

Initially, I wanted to trap this error and keep on processing. Instead I decided to trap ALL errors and stop processing immediately. It is an easier thing to do, it limits the damage and is more in line with how I want to do business.


Attached File(s)
Attached File  XML_Append_to_Access_DB_1.2.zip ( 259.58K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post Aug 11 2018, 05:00 AM
Post#4


UtterAccess VIP
Posts: 10,609
Joined: 6-December 03
From: Telegraph Hill


Hi Dan,

Just flying past ...

You say you have created a compound index based on Date & SKU. In the index properties, try setting it to Unique.

You should get an error when you insert a combo that already exists in the table.

I haven't checked your attachment (I'll try and have a look later), so apologies if you have already done this.

d
Go to the top of the page
 
dflak
post Aug 13 2018, 09:16 AM
Post#5


Utter Access VIP
Posts: 6,245
Joined: 22-June 04
From: North Carolina


I think I am good. As soon as the code finds an error of ANY kind, it writes it to the Log sheet and exits.

In my real world, the most likely error would be to try to import the same data download twice, so it is most likely that it will fail on the first record and I won't even have any cleanup to do.

The error detection module writes the Excel row number of the failure, the error code and the error description to the log file, so I can see how many records have been appended. This makes the cleanup easier to perform.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 11:27 PM