Tracking data append and updates with code 
Author Message
 Tracking data append and updates with code

My database development goals involve creating a routine that append
data and update records at the same time while tracking data changes
and errors.  In other words, the routine would programmatically act as
an update/append query, yet add the functionality for logging changes
and errors, which typical action queries can not provide.

My confusion lies in whether to use the QueryDef or Recordset SQL
parameter. The challenge begins when deciding whether to update or add
the record. How do I efficiently discover if the record exists
(Dlookup, DMax, FindFirst, or create my own test)? The second
challenge relates to passing original values to a log table; or
capturing errors. Finally, I would like to know if the data import was
an update or new addition for manual review. The purpose behind these
goals relates to reviewing import data; identifying what import
records were duplicates; and locate any append records that should
have been updates where the name field was misspelled.  Hopefully,
someone could give me some insight and lead me in the right direction.

Quick Summary:
In essence, these goals hope to bulletproof my data imports. The code
is a direct response to the free form nature of the import data.  This
project encompasses my personal endeavor. It ultimately serves my
utility and furthers my Access Skills.  The import process works, yet
I hope to bulletproof it and build upon it. The data entry point is
not in my hands; unless I type the data in my database myself, which
defeat the purpose of the code.  This post refers to Access 2002.



Mon, 14 Nov 2005 10:14:13 GMT  
 Tracking data append and updates with code
You don't mind a challenge, eh Robert? :-)

So many things can go wrong with imports, that I generally find it easiest
to import into a temp table. That allows you to massage the data, correctly
interpret the data types, e.g. (dates, currency fields, and those that have
no data in the first few rows), run validation checks, assign an ImportID
key and other fields to track of what you've done, create queries to
outer-join on existing tables to discover which ones have matches, update
the matches, and append the new records.

In terms of logging, you may find it's enough to assign a new batch number
for the operation, and use that as a field of your main table.
Alternatively, you may want a related table containing the ultimate key
value, update/insert flag, and batch number, to give you a history of which
records where updated/appended when.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> My database development goals involve creating a routine that append
> data and update records at the same time while tracking data changes
> and errors.  In other words, the routine would programmatically act as
> an update/append query, yet add the functionality for logging changes
> and errors, which typical action queries can not provide.

> My confusion lies in whether to use the QueryDef or Recordset SQL
> parameter. The challenge begins when deciding whether to update or add
> the record. How do I efficiently discover if the record exists
> (Dlookup, DMax, FindFirst, or create my own test)? The second
> challenge relates to passing original values to a log table; or
> capturing errors. Finally, I would like to know if the data import was
> an update or new addition for manual review. The purpose behind these
> goals relates to reviewing import data; identifying what import
> records were duplicates; and locate any append records that should
> have been updates where the name field was misspelled.  Hopefully,
> someone could give me some insight and lead me in the right direction.

> Quick Summary:
> In essence, these goals hope to bulletproof my data imports. The code
> is a direct response to the free form nature of the import data.  This
> project encompasses my personal endeavor. It ultimately serves my
> utility and furthers my Access Skills.  The import process works, yet
> I hope to bulletproof it and build upon it. The data entry point is
> not in my hands; unless I type the data in my database myself, which
> defeat the purpose of the code.  This post refers to Access 2002.



Mon, 14 Nov 2005 11:14:06 GMT  
 Tracking data append and updates with code
On Thu, 29 May 2003 11:14:06 +0800, "Allen Browne"

Quote:

>You don't mind a challenge, eh Robert? :-)

>So many things can go wrong with imports, that I generally find it easiest
>to import into a temp table. That allows you to massage the data, correctly
>interpret the data types, e.g. (dates, currency fields, and those that have
>no data in the first few rows), run validation checks, assign an ImportID
>key and other fields to track of what you've done, create queries to
>outer-join on existing tables to discover which ones have matches, update
>the matches, and append the new records.

>In terms of logging, you may find it's enough to assign a new batch number
>for the operation, and use that as a field of your main table.
>Alternatively, you may want a related table containing the ultimate key
>value, update/insert flag, and batch number, to give you a history of which
>records where updated/appended when.

Allen,

The temporary table is completely relevant. I already import the data
into temporary tables from a text file. Then I run some update queries
and manually check the data myself.   My post relates to the next step
of appending and updating data into the normal tables.  Many
inconsistencies still exist like incorrect spelling and other data
entry errors (I have NO control over data entry).  The final step
requires a manually validation like when a project name changes.
Validation code can not anticipate such an occurrence. So my plan is
to make it easier for me to review imported data edits and additions.
The bonus question relates to capturing errors as well.

In this case, update and append queries do not suffice for attaching
data to the main  tables since the many duplicates cause the auto
number/primary key field to unnecessarily increment.

 The only way to further oneself is to challenge oneself abilities and
strengthen your focus. My programming knowledge needs heavy
conditioning.



Tue, 15 Nov 2005 15:16:04 GMT  
 Tracking data append and updates with code
Sounds like a pretty reasonable approach.

The one I did last week have an interface with command buttons for:
- Select the file to import
- Import this data.

The first one calls the FileOpen dialog, imports into the temp table and
displays the results in a subform, runs the validation checks, and displays
the problems in a list box. Problems are rated Critical or Warning. The
Import button is disabled while Critical problems remain not sorted out.

"Critical" is typically:
- mismatched data (RI failure),
- wrong data type,
- required fields missing,
- anything that would prevent the data from importing properly.

"Warning" is stuff like:
- possible duplicates;
- unlikely values.

The two-button-with-user-intervention-in-between approach sounds like what
you are doing. Most of my validations are SQL statements, and I write the
problems into a separate temporary error-logging table, so it doesn't sound
much different from your approach.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> On Thu, 29 May 2003 11:14:06 +0800, "Allen Browne"

> >You don't mind a challenge, eh Robert? :-)

> >So many things can go wrong with imports, that I generally find it
easiest
> >to import into a temp table. That allows you to massage the data,
correctly
> >interpret the data types, e.g. (dates, currency fields, and those that
have
> >no data in the first few rows), run validation checks, assign an ImportID
> >key and other fields to track of what you've done, create queries to
> >outer-join on existing tables to discover which ones have matches, update
> >the matches, and append the new records.

> >In terms of logging, you may find it's enough to assign a new batch
number
> >for the operation, and use that as a field of your main table.
> >Alternatively, you may want a related table containing the ultimate key
> >value, update/insert flag, and batch number, to give you a history of
which
> >records where updated/appended when.

> Allen,

> The temporary table is completely relevant. I already import the data
> into temporary tables from a text file. Then I run some update queries
> and manually check the data myself.   My post relates to the next step
> of appending and updating data into the normal tables.  Many
> inconsistencies still exist like incorrect spelling and other data
> entry errors (I have NO control over data entry).  The final step
> requires a manually validation like when a project name changes.
> Validation code can not anticipate such an occurrence. So my plan is
> to make it easier for me to review imported data edits and additions.
> The bonus question relates to capturing errors as well.

> In this case, update and append queries do not suffice for attaching
> data to the main  tables since the many duplicates cause the auto
> number/primary key field to unnecessarily increment.

>  The only way to further oneself is to challenge oneself abilities and
> strengthen your focus. My programming knowledge needs heavy
> conditioning.



Tue, 15 Nov 2005 16:04:54 GMT  
 Tracking data append and updates with code
Handle the Update or append decision by using two queries.

After importing the data into a temp table use one query
that joins the temp table with the permanent table by the
primary key in the permanent table.  If the primary key is
a surrogate (like an autonumber field) then you may have
to join all of the fields you want to match. This will
produce a recordset with only those records that already
exist in permanent table.

The second query is identical to the first except:
  1.  Use an outer join to the permanent table.
  2.  Add the primary key of the permanent table to the
query and select only those records where the primary key
of the permanent table is null.

This will give you a recordset of those records that do
not exist in the permanent table.

Quote:
>-----Original Message-----
>My database development goals involve creating a routine
that append
>data and update records at the same time while tracking
data changes
>and errors.  In other words, the routine would

programmatically act as
Quote:
>an update/append query, yet add the functionality for
logging changes
>and errors, which typical action queries can not provide.

>My confusion lies in whether to use the QueryDef or
Recordset SQL
>parameter. The challenge begins when deciding whether to
update or add
>the record. How do I efficiently discover if the record
exists
>(Dlookup, DMax, FindFirst, or create my own test)? The
second
>challenge relates to passing original values to a log
table; or
>capturing errors. Finally, I would like to know if the
data import was
>an update or new addition for manual review. The purpose
behind these
>goals relates to reviewing import data; identifying what
import
>records were duplicates; and locate any append records
that should
>have been updates where the name field was misspelled.  
Hopefully,
>someone could give me some insight and lead me in the
right direction.

>Quick Summary:
>In essence, these goals hope to bulletproof my data
imports. The code
>is a direct response to the free form nature of the
import data.  This
>project encompasses my personal endeavor. It ultimately
serves my
>utility and furthers my Access Skills.  The import
process works, yet
>I hope to bulletproof it and build upon it. The data
entry point is
>not in my hands; unless I type the data in my database
myself, which
>defeat the purpose of the code.  This post refers to
Access 2002.

>.



Tue, 15 Nov 2005 19:16:18 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Prevent Duplicates when updating with code or append

2. Append query that appends to and updates an archive table

3. Code needs amended to track data changes

4. Append Query Error Tracking Table

5. Exporting Data to Excel and append or add data

6. Data updating class source codes...

7. need help in data updating (source codes included )

8. Code to check for Data Control Update.

9. How to track update of webpage

10. How to track if a webpage was updated

11. Progress bar to track db update time

12. Query records, update then append?

 

 
Powered by phpBB® Forum Software