duplicates query help & strategy for update queries with SetWarnings = False

[ARC]

Hello all,

So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution for
renaming the duplicate records? My thinking was to take the results of the
duplicate query, and somehow have it number each line where there is a
duplicate (tried a groups query, but "count" won't work), then do an update
query to change the duplicate to include the occurence #. For example, in a
pay types table, if "Discover" is duplicated (and you can't get rid of one
due to it potentially being in use), then I'd like to run an update query
that would update the 2nd one to: Discover (2). (Yes, I know you should just
set the pay type description to NOT allow duplicates, but the original db
did allow it, and the import db will not allow dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import
utility: When running a series of update queries to update data from one
database to another, I use a docmd.setwarnings false statement before
running each update or append query.

The problem is, if a query fails due to data validation rules, other misc.
table rules, etc., the "setwarnings = false" command is suppressing the one
warning that you actually do want to see, and it's blowing right by that
with no messages. If you don't put the setwarnings=false, then the user
get's 2 dialogs for every update query, which is not at all desirable since
we're talking 50 or so append queries. Does anyone have an alternative?
Here's the exact code I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that the
user can never be warned about the 2 standard messages: Running a query that
will change data, and Confirming the appending of records. But...I have to
know if a query fails to append any data due to key viloations, or any other
reasons.

Any strategy here would be helpful. I don't even think you can trap an error
code, because if any data fails to append, it doesn't trigger the On Error
events. Any ideas?

Thanks!

Andy

[Allen Browne]

The best way do do imports might be to sort out the problems before the data
appended to the real table. You create a table with all *Text* fields (so
you get no data mismatch errors), no validation rules, no relationships to
other tables, and an AutoNumber primary key field last in the table (so the
data gets appended into the preceding fields.)

Next you build a from to perform the import. The first button deletes any
data in the temp table (from previous imports), and performs the
TransferText into the temp table. Your code then runs a series of tests for
everything that could go wrong: zero-length text fields, wrong data type,
bad dates, values that don't match anything in foreign key fields, and so
on. You flag these records and load them into a list box or the form itself
(typically in Continuous view), so the user can fix up these situations.

Once the user has handled all the problems, you enable the final command
button at the bottom of the form, which executes an append query to add the
data to the real table(s). If any error occurs during this step (typically
something you forgot to check for), you can trap this error by running using
the Execute method with dbFailOnError. If that's new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A failed Execute can still leave you with partial records in the final
table, so you probably want to wrap that operation in a transaction so you
can roll the whole thing back. For an example of using a transaction, see:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <ac********@hotmail.comwrote in message
news:8c******************@newssvr12.news.prodigy.n et...

Hello all,

So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution
for renaming the duplicate records? My thinking was to take the results of
the duplicate query, and somehow have it number each line where there is a
duplicate (tried a groups query, but "count" won't work), then do an
update query to change the duplicate to include the occurence #. For
example, in a pay types table, if "Discover" is duplicated (and you can't
get rid of one due to it potentially being in use), then I'd like to run
an update query that would update the 2nd one to: Discover (2). (Yes, I
know you should just set the pay type description to NOT allow duplicates,
but the original db did allow it, and the import db will not allow
dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import
utility: When running a series of update queries to update data from one
database to another, I use a docmd.setwarnings false statement before
running each update or append query.

The problem is, if a query fails due to data validation rules, other misc.
table rules, etc., the "setwarnings = false" command is suppressing the
one warning that you actually do want to see, and it's blowing right by
that with no messages. If you don't put the setwarnings=false, then the
user get's 2 dialogs for every update query, which is not at all desirable
since we're talking 50 or so append queries. Does anyone have an
alternative? Here's the exact code I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that the
user can never be warned about the 2 standard messages: Running a query
that will change data, and Confirming the appending of records. But...I
have to know if a query fails to append any data due to key viloations, or
any other reasons.

Any strategy here would be helpful. I don't even think you can trap an
error code, because if any data fails to append, it doesn't trigger the On
Error events. Any ideas?

Thanks!

Andy

[ARC]

Allen,

That's perfect! Exactly what I was looking for, to be able to trap errors on
the append, and not warn with the other 2 boxes!

I dont' have to worry much about a transaction. I'm having the user select
the source and destination, and if the destination is present and contains
data, I'm giving an overwrite / backup dialog. So each time they try,
they'll get a blank database guaranteed.

I've done pretty much what you've said. I have many many queries that check
for orpaned data (for example, if there's a billing record with no matching
custID in the customer table, I'm putting up a form with the unmatched data,
and a quick way to update all unmatched to one archive type customer, etc.
Or if a part category is missing in the parts table, I'm creating a category
callled "Un-categorized", and setting any parts missing cat's to this
category. Or if the data is line-items that link to parts, if the partID
isn't there, I again put up a form so they can quickly replace missing data.
For things like customer call logs, or customer reminders, I'm doing a join
with the customer table, so it will only bring in the matched data. For
critical financial items, I'm putting up forms so they can fix unmatched
data.

For mainly static tables such as business source, customer type, etc., since
I'm not allowing blanks anymore, I'm just doing an IIF(isnull(..., etc.
where I'll set the description to "Not entered" if it's blank.

I was surprised to see duplicate entries in some of the static tables for
dropdown selections, so I have to solve this one now. Any ideas on the other
part of my question? If there's a duplicate, I want to rename the
description with a occurence number after the description. Such as "Federal
Express (1)" and "Federal Express (2)", etc.

Thanks!

Andy
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

The best way do do imports might be to sort out the problems before the
data appended to the real table. You create a table with all *Text* fields
(so you get no data mismatch errors), no validation rules, no
relationships to other tables, and an AutoNumber primary key field last in
the table (so the data gets appended into the preceding fields.)

Next you build a from to perform the import. The first button deletes any
data in the temp table (from previous imports), and performs the
TransferText into the temp table. Your code then runs a series of tests
for everything that could go wrong: zero-length text fields, wrong data
type, bad dates, values that don't match anything in foreign key fields,
and so on. You flag these records and load them into a list box or the
form itself (typically in Continuous view), so the user can fix up these
situations.

Once the user has handled all the problems, you enable the final command
button at the bottom of the form, which executes an append query to add
the data to the real table(s). If any error occurs during this step
(typically something you forgot to check for), you can trap this error by
running using the Execute method with dbFailOnError. If that's new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A failed Execute can still leave you with partial records in the final
table, so you probably want to wrap that operation in a transaction so you
can roll the whole thing back. For an example of using a transaction, see:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <ac********@hotmail.comwrote in message
news:8c******************@newssvr12.news.prodigy.n et...

>Hello all,

So I'm knee deep in this import utility program, and am coming up with
all sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution
for renaming the duplicate records? My thinking was to take the results
of the duplicate query, and somehow have it number each line where there
is a duplicate (tried a groups query, but "count" won't work), then do an
update query to change the duplicate to include the occurence #. For
example, in a pay types table, if "Discover" is duplicated (and you can't
get rid of one due to it potentially being in use), then I'd like to run
an update query that would update the 2nd one to: Discover (2). (Yes, I
know you should just set the pay type description to NOT allow
duplicates, but the original db did allow it, and the import db will not
allow dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import
utility: When running a series of update queries to update data from one
database to another, I use a docmd.setwarnings false statement before
running each update or append query.

The problem is, if a query fails due to data validation rules, other
misc. table rules, etc., the "setwarnings = false" command is suppressing
the one warning that you actually do want to see, and it's blowing right
by that with no messages. If you don't put the setwarnings=false, then
the user get's 2 dialogs for every update query, which is not at all
desirable since we're talking 50 or so append queries. Does anyone have
an alternative? Here's the exact code I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that
the user can never be warned about the 2 standard messages: Running a
query that will change data, and Confirming the appending of records.
But...I have to know if a query fails to append any data due to key
viloations, or any other reasons.

Any strategy here would be helpful. I don't even think you can trap an
error code, because if any data fails to append, it doesn't trigger the
On Error events. Any ideas?

Thanks!

Andy

[ARC]

Hi Allen,

This question may have been lost in the shuffle:

I was surprised to see duplicate entries in some of the static tables for
dropdown selections, so I have to solve this one now. Any ideas on the other
part of my question? If there's a duplicate, I want to rename the
description with a occurence number after the description. Such as "Federal
Express (1)" and "Federal Express (2)", etc.

P.S. I used the query wizard to create a "find duplicates" query, now I need
to adjust the results of that query to add an occurence counter/number, and
I'd be set. Just not quite sure how to do that.

Thanks!

Andy
"ARC" <ac********@hotmail.comwrote in message
news:_n*****************@newssvr13.news.prodigy.ne t...

Allen,

That's perfect! Exactly what I was looking for, to be able to trap errors
on the append, and not warn with the other 2 boxes!

I dont' have to worry much about a transaction. I'm having the user select
the source and destination, and if the destination is present and contains
data, I'm giving an overwrite / backup dialog. So each time they try,
they'll get a blank database guaranteed.

I've done pretty much what you've said. I have many many queries that
check for orpaned data (for example, if there's a billing record with no
matching custID in the customer table, I'm putting up a form with the
unmatched data, and a quick way to update all unmatched to one archive
type customer, etc. Or if a part category is missing in the parts table,
I'm creating a category callled "Un-categorized", and setting any parts
missing cat's to this category. Or if the data is line-items that link to
parts, if the partID isn't there, I again put up a form so they can
quickly replace missing data. For things like customer call logs, or
customer reminders, I'm doing a join with the customer table, so it will
only bring in the matched data. For critical financial items, I'm putting
up forms so they can fix unmatched data.

For mainly static tables such as business source, customer type, etc.,
since I'm not allowing blanks anymore, I'm just doing an IIF(isnull(...,
etc. where I'll set the description to "Not entered" if it's blank.

I was surprised to see duplicate entries in some of the static tables for
dropdown selections, so I have to solve this one now. Any ideas on the
other part of my question? If there's a duplicate, I want to rename the
description with a occurence number after the description. Such as
"Federal Express (1)" and "Federal Express (2)", etc.

Thanks!

Andy
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

>The best way do do imports might be to sort out the problems before the
data appended to the real table. You create a table with all *Text*
fields (so you get no data mismatch errors), no validation rules, no
relationships to other tables, and an AutoNumber primary key field last
in the table (so the data gets appended into the preceding fields.)

Next you build a from to perform the import. The first button deletes any
data in the temp table (from previous imports), and performs the
TransferText into the temp table. Your code then runs a series of tests
for everything that could go wrong: zero-length text fields, wrong data
type, bad dates, values that don't match anything in foreign key fields,
and so on. You flag these records and load them into a list box or the
form itself (typically in Continuous view), so the user can fix up these
situations.

Once the user has handled all the problems, you enable the final command
button at the bottom of the form, which executes an append query to add
the data to the real table(s). If any error occurs during this step
(typically something you forgot to check for), you can trap this error by
running using the Execute method with dbFailOnError. If that's new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A failed Execute can still leave you with partial records in the final
table, so you probably want to wrap that operation in a transaction so
you can roll the whole thing back. For an example of using a transaction,
see:
Archive: Move records to another table - copy + delete in a
transaction
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <ac********@hotmail.comwrote in message
news:8c******************@newssvr12.news.prodigy. net...

>>Hello all,

So I'm knee deep in this import utility program, and am coming up with
all sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution
for renaming the duplicate records? My thinking was to take the results
of the duplicate query, and somehow have it number each line where there
is a duplicate (tried a groups query, but "count" won't work), then do
an update query to change the duplicate to include the occurence #. For
example, in a pay types table, if "Discover" is duplicated (and you
can't get rid of one due to it potentially being in use), then I'd like
to run an update query that would update the 2nd one to: Discover (2).
(Yes, I know you should just set the pay type description to NOT allow
duplicates, but the original db did allow it, and the import db will not
allow dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import
utility: When running a series of update queries to update data from one
database to another, I use a docmd.setwarnings false statement before
running each update or append query.

The problem is, if a query fails due to data validation rules, other
misc. table rules, etc., the "setwarnings = false" command is
suppressing the one warning that you actually do want to see, and it's
blowing right by that with no messages. If you don't put the
setwarnings=false, then the user get's 2 dialogs for every update query,
which is not at all desirable since we're talking 50 or so append
queries. Does anyone have an alternative? Here's the exact code I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that
the user can never be warned about the 2 standard messages: Running a
query that will change data, and Confirming the appending of records.
But...I have to know if a query fails to append any data due to key
viloations, or any other reasons.

Any strategy here would be helpful. I don't even think you can trap an
error code, because if any data fails to append, it doesn't trigger the
On Error events. Any ideas?

Thanks!

Andy

[ARC]

Nevermind, I think I've got it. I'm calling a function that will set the
occurence number.

Thanks!

Andy
"ARC" <ac********@hotmail.comwrote in message
news:UV******************@newssvr13.news.prodigy.n et...

Hi Allen,

This question may have been lost in the shuffle:

I was surprised to see duplicate entries in some of the static tables for
dropdown selections, so I have to solve this one now. Any ideas on the
other
part of my question? If there's a duplicate, I want to rename the
description with a occurence number after the description. Such as
"Federal
Express (1)" and "Federal Express (2)", etc.

P.S. I used the query wizard to create a "find duplicates" query, now I
need to adjust the results of that query to add an occurence
counter/number, and I'd be set. Just not quite sure how to do that.

Thanks!

Andy
"ARC" <ac********@hotmail.comwrote in message
news:_n*****************@newssvr13.news.prodigy.ne t...

>Allen,

That's perfect! Exactly what I was looking for, to be able to trap errors
on the append, and not warn with the other 2 boxes!

I dont' have to worry much about a transaction. I'm having the user
select the source and destination, and if the destination is present and
contains data, I'm giving an overwrite / backup dialog. So each time they
try, they'll get a blank database guaranteed.

I've done pretty much what you've said. I have many many queries that
check for orpaned data (for example, if there's a billing record with no
matching custID in the customer table, I'm putting up a form with the
unmatched data, and a quick way to update all unmatched to one archive
type customer, etc. Or if a part category is missing in the parts table,
I'm creating a category callled "Un-categorized", and setting any parts
missing cat's to this category. Or if the data is line-items that link to
parts, if the partID isn't there, I again put up a form so they can
quickly replace missing data. For things like customer call logs, or
customer reminders, I'm doing a join with the customer table, so it will
only bring in the matched data. For critical financial items, I'm putting
up forms so they can fix unmatched data.

For mainly static tables such as business source, customer type, etc.,
since I'm not allowing blanks anymore, I'm just doing an IIF(isnull(...,
etc. where I'll set the description to "Not entered" if it's blank.

I was surprised to see duplicate entries in some of the static tables for
dropdown selections, so I have to solve this one now. Any ideas on the
other part of my question? If there's a duplicate, I want to rename the
description with a occurence number after the description. Such as
"Federal Express (1)" and "Federal Express (2)", etc.

Thanks!

Andy
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

>>The best way do do imports might be to sort out the problems before the
data appended to the real table. You create a table with all *Text*
fields (so you get no data mismatch errors), no validation rules, no
relationships to other tables, and an AutoNumber primary key field last
in the table (so the data gets appended into the preceding fields.)

Next you build a from to perform the import. The first button deletes
any data in the temp table (from previous imports), and performs the
TransferText into the temp table. Your code then runs a series of tests
for everything that could go wrong: zero-length text fields, wrong data
type, bad dates, values that don't match anything in foreign key fields,
and so on. You flag these records and load them into a list box or the
form itself (typically in Continuous view), so the user can fix up these
situations.

Once the user has handled all the problems, you enable the final command
button at the bottom of the form, which executes an append query to add
the data to the real table(s). If any error occurs during this step
(typically something you forgot to check for), you can trap this error
by running using the Execute method with dbFailOnError. If that's new,
see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A failed Execute can still leave you with partial records in the final
table, so you probably want to wrap that operation in a transaction so
you can roll the whole thing back. For an example of using a
transaction, see:
Archive: Move records to another table - copy + delete in a
transaction
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <ac********@hotmail.comwrote in message
news:8c******************@newssvr12.news.prodigy .net...
Hello all,

So I'm knee deep in this import utility program, and am coming up with
all sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution
for renaming the duplicate records? My thinking was to take the results
of the duplicate query, and somehow have it number each line where
there is a duplicate (tried a groups query, but "count" won't work),
then do an update query to change the duplicate to include the
occurence #. For example, in a pay types table, if "Discover" is
duplicated (and you can't get rid of one due to it potentially being in
use), then I'd like to run an update query that would update the 2nd
one to: Discover (2). (Yes, I know you should just set the pay type
description to NOT allow duplicates, but the original db did allow it,
and the import db will not allow dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import
utility: When running a series of update queries to update data from
one database to another, I use a docmd.setwarnings false statement
before running each update or append query.

The problem is, if a query fails due to data validation rules, other
misc. table rules, etc., the "setwarnings = false" command is
suppressing the one warning that you actually do want to see, and it's
blowing right by that with no messages. If you don't put the
setwarnings=false, then the user get's 2 dialogs for every update
query, which is not at all desirable since we're talking 50 or so
append queries. Does anyone have an alternative? Here's the exact code
I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that
the user can never be warned about the 2 standard messages: Running a
query that will change data, and Confirming the appending of records.
But...I have to know if a query fails to append any data due to key
viloations, or any other reasons.

Any strategy here would be helpful. I don't even think you can trap an
error code, because if any data fails to append, it doesn't trigger the
On Error events. Any ideas?

Thanks!

Andy

[ARC]

As the English would say "Sod that!!" After playing around with duplicates,
and a funtion to generate an occurence number, it looked like you would need
a handful of queries per table!!

So in 15 min's or so, I came up with a function that will take care of dup's
and nulls in one pass. I'll post the code in a new post for those searching
for such things, as it works quite nicely.

Andy
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...

The best way do do imports might be to sort out the problems before the
data appended to the real table. You create a table with all *Text* fields
(so you get no data mismatch errors), no validation rules, no
relationships to other tables, and an AutoNumber primary key field last in
the table (so the data gets appended into the preceding fields.)

Next you build a from to perform the import. The first button deletes any
data in the temp table (from previous imports), and performs the
TransferText into the temp table. Your code then runs a series of tests
for everything that could go wrong: zero-length text fields, wrong data
type, bad dates, values that don't match anything in foreign key fields,
and so on. You flag these records and load them into a list box or the
form itself (typically in Continuous view), so the user can fix up these
situations.

Once the user has handled all the problems, you enable the final command
button at the bottom of the form, which executes an append query to add
the data to the real table(s). If any error occurs during this step
(typically something you forgot to check for), you can trap this error by
running using the Execute method with dbFailOnError. If that's new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A failed Execute can still leave you with partial records in the final
table, so you probably want to wrap that operation in a transaction so you
can roll the whole thing back. For an example of using a transaction, see:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <ac********@hotmail.comwrote in message
news:8c******************@newssvr12.news.prodigy.n et...

>Hello all,

So I'm knee deep in this import utility program, and am coming up with
all sorts of "gotcha's!".

1st off. On a "Find Duplicates Query", does anyone have a good solution
for renaming the duplicate records? My thinking was to take the results
of the duplicate query, and somehow have it number each line where there
is a duplicate (tried a groups query, but "count" won't work), then do an
update query to change the duplicate to include the occurence #. For
example, in a pay types table, if "Discover" is duplicated (and you can't
get rid of one due to it potentially being in use), then I'd like to run
an update query that would update the 2nd one to: Discover (2). (Yes, I
know you should just set the pay type description to NOT allow
duplicates, but the original db did allow it, and the import db will not
allow dup's...)

2nd problem, and this is a biggie, maybe a showstopper for my import
utility: When running a series of update queries to update data from one
database to another, I use a docmd.setwarnings false statement before
running each update or append query.

The problem is, if a query fails due to data validation rules, other
misc. table rules, etc., the "setwarnings = false" command is suppressing
the one warning that you actually do want to see, and it's blowing right
by that with no messages. If you don't put the setwarnings=false, then
the user get's 2 dialogs for every update query, which is not at all
desirable since we're talking 50 or so append queries. Does anyone have
an alternative? Here's the exact code I use:

'Begin data importing******************************
'1st, Append all static/dropdown list tables
'
Call SetMessage("Appending Business Source Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qAppendBusSource"
'
Call SetMessage("Appending Customer Type Records ...")
DoCmd.SetWarnings warnyn
DoCmd.OpenQuery "qryAppendCustType"

And this goes on for about 50 queries or so. So the trick here is that
the user can never be warned about the 2 standard messages: Running a
query that will change data, and Confirming the appending of records.
But...I have to know if a query fails to append any data due to key
viloations, or any other reasons.

Any strategy here would be helpful. I don't even think you can trap an
error code, because if any data fails to append, it doesn't trigger the
On Error events. Any ideas?

Thanks!

Andy

[ARC]

Hi Allen,

Quick question for you. In regards to checking for empty strings, in your
experience, should I be checking just the memo fields?

In query design for the append queries, I'm using something like this for
memos:

xNotes: IIf([customers1].[Notes]="",Null,[customers1.[Notes])
appendto: Notes

Andy

[Allen Browne]

Andy, the best solution for this is to disallow zero-length strings (ZLS) in
table design.

There's a property there for each Text and Memo field, or you can use the
code in this link to set the property for all the Text, Memo, and Hyperlink
fields in your database:
http://allenbrowne.com/bug-09.html

There are rare cases where a ZLS is useful (e.g. for temporary import
tables), but in general all they contribute to your database is:
- inefficiency (querying for both types),
- bugs (where you forgot to query for both types), and
- confusion (where a user can't tell the difference.)

So save yourself the frustration and block them at the engine level.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <PC*****@PCESoft.invalidwrote in message
news:_N******************@newssvr13.news.prodigy.n et...

Hi Allen,

Quick question for you. In regards to checking for empty strings, in your
experience, should I be checking just the memo fields?

In query design for the append queries, I'm using something like this for
memos:

xNotes: IIf([customers1].[Notes]="",Null,[customers1.[Notes])
appendto: Notes

Andy

[ARC]

This is for the import routine for an old/existing db where it was not
blocked...

Thanks!

[Allen Browne]

In that case, you will need to test for both null and zls.

Criteria:
Is Null Or ""

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <PC*****@PCESoft.invalidwrote in message
news:zZ******************@newssvr13.news.prodigy.n et...

This is for the import routine for an old/existing db where it was not
blocked...

Thanks!

[ARC]

In just the memo fields? Many Thanks!

[ARC]

Allen,

Thanks again for the link! I just had my very first successful test of my
import utility, after having to code for just about everything! So thanks
again... Just ran into the problem your link mentioned about expecting
parameters. I'm using the new access 2007 tempvars, and it's treating that
like a parameter, so off for more changes...

Andy

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:46***********************@per-qv1-newsreader-01.iinet.net.au:

A failed Execute can still leave you with partial records in the
final table, so you probably want to wrap that operation in a
transaction so you can roll the whole thing back.

Er, doesn't dbFailOnError roll the whole thing back?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Allen Browne]

No, dbFailOnError does not rollback, David.

It used to before Access 97. The A97 help file wrongly states that it rolls
back. You had to read the ReadMe installed with A97 to discover that MS had
changed this. So now dbFailOnError quits when an error occurs, so leaves you
with partial updates - probably the worst possible result for many
situations.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:46***********************@per-qv1-newsreader-01.iinet.net.au:

>A failed Execute can still leave you with partial records in the
final table, so you probably want to wrap that operation in a
transaction so you can roll the whole thing back.

Er, doesn't dbFailOnError roll the whole thing back?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rick Brandt]

Allen Browne wrote:

No, dbFailOnError does not rollback, David.

It used to before Access 97. The A97 help file wrongly states that it
rolls back. You had to read the ReadMe installed with A97 to discover
that MS had changed this. So now dbFailOnError quits when an error
occurs, so leaves you with partial updates - probably the worst
possible result for many situations.

Hmm, in a simple test I just did dbFailOnError did roll back the entire
transaction. I tried an Append query of ten rows where the first two rows being
inserted are valid and all others produce duplicate key errors.

When I run the code and get the Duplicate Key error nothing is inserted. When
I removed dbFailOnError then the valid rows were inserted and the duplicate rows
were not (no error of course).

This was Access 97 SR2.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:46***********************@per-qv1-newsreader-01.iinet.net.au:

No, dbFailOnError does not rollback, David.

It used to before Access 97. The A97 help file wrongly states that
it rolls back. You had to read the ReadMe installed with A97 to
discover that MS had changed this. So now dbFailOnError quits when
an error occurs, so leaves you with partial updates - probably the
worst possible result for many situations.

Geez. How could I have missed that?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:46***********************@per-qv1-newsreader-01.iinet.net.au:

The Access 2003 help on Execute (DAO) states:
<quote>
In a Microsoft Jet workspace, if you provide a syntactically
correct SQL statement and have the appropriate permissions, the
Execute method won't fail - even if not a single row can be
modified or deleted. Therefore, always use the dbFailOnError
option when using the Execute method to run an update or delete
query. This option generates a run-time error and rolls back all
successful changes if any of the records affected are locked and
can't be updated or deleted.

In earlier versions of the Microsoft Jet Database Engine, SQL
statements were automatically embedded in implicit transactions.
If part of a statement executed with dbFailOnError failed, the
entire statement would be rolled back. To improve performance,
these implicit transactions were removed starting with version
3.5. If you are updating older DAO code, be sure to consider using
explicit transactions around Execute statements.
</quote>

That's pretty ambiguous. It seems to imply that the updates won't be
posted, but it happens in some magical way not involving an implicit
transaction.

Gad, I've got a lot of code to review.

Obviously, it's time to write a replacement for db.Execute.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/