473,703 Members | 4,141 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.setwarnin gs 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 "setwarning s = 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=fal se, 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("App ending Business Source Records ...")
DoCmd.SetWarnin gs warnyn
DoCmd.OpenQuery "qAppendBusSour ce"
'
Call SetMessage("App ending Customer Type Records ...")
DoCmd.SetWarnin gs warnyn
DoCmd.OpenQuery "qryAppendCustT ype"

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

Sep 21 '07 #1
16 3504
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********@hot mail.comwrote in message
news:8c******** **********@news svr12.news.prod igy.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.setwarnin gs 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 "setwarning s = 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=fal se, 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("App ending Business Source Records ...")
DoCmd.SetWarnin gs warnyn
DoCmd.OpenQuery "qAppendBusSour ce"
'
Call SetMessage("App ending Customer Type Records ...")
DoCmd.SetWarnin gs warnyn
DoCmd.OpenQuery "qryAppendCustT ype"

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
Sep 21 '07 #2
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*********@Se eSig.Invalidwro te 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********@hot mail.comwrote in message
news:8c******** **********@news svr12.news.prod igy.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.setwarnin gs 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 "setwarning s = 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=fal se, 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("App ending Business Source Records ...")
DoCmd.SetWarni ngs warnyn
DoCmd.OpenQuer y "qAppendBusSour ce"
'
Call SetMessage("App ending Customer Type Records ...")
DoCmd.SetWarni ngs warnyn
DoCmd.OpenQuer y "qryAppendCustT ype"

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
Sep 21 '07 #3
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********@hot mail.comwrote in message
news:_n******** *********@newss vr13.news.prodi gy.net...
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*********@Se eSig.Invalidwro te 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
relationship s 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********@hot mail.comwrote in message
news:8c******* ***********@new ssvr12.news.pro digy.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.setwarnin gs 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 "setwarning s = 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=f alse, 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("App ending Business Source Records ...")
DoCmd.SetWarn ings warnyn
DoCmd.OpenQue ry "qAppendBusSour ce"
'
Call SetMessage("App ending Customer Type Records ...")
DoCmd.SetWarn ings warnyn
DoCmd.OpenQue ry "qryAppendCustT ype"

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
Sep 21 '07 #4
ARC
Nevermind, I think I've got it. I'm calling a function that will set the
occurence number.

Thanks!

Andy
"ARC" <ac********@hot mail.comwrote in message
news:UV******** **********@news svr13.news.prod igy.net...
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********@hot mail.comwrote in message
news:_n******** *********@newss vr13.news.prodi gy.net...
>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*********@Se eSig.Invalidwro te 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
relationshi ps 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
TransferTex t 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********@hot mail.comwrote in message
news:8c****** ************@ne wssvr12.news.pr odigy.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
descriptio n 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.setwarnin gs 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 "setwarning s = false" command is
suppressin g 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("App ending Business Source Records ...")
DoCmd.SetWar nings warnyn
DoCmd.OpenQu ery "qAppendBusSour ce"
'
Call SetMessage("App ending Customer Type Records ...")
DoCmd.SetWar nings warnyn
DoCmd.OpenQu ery "qryAppendCustT ype"

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
Sep 21 '07 #5
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*********@Se eSig.Invalidwro te 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********@hot mail.comwrote in message
news:8c******** **********@news svr12.news.prod igy.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.setwarnin gs 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 "setwarning s = 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=fal se, 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("App ending Business Source Records ...")
DoCmd.SetWarni ngs warnyn
DoCmd.OpenQuer y "qAppendBusSour ce"
'
Call SetMessage("App ending Customer Type Records ...")
DoCmd.SetWarni ngs warnyn
DoCmd.OpenQuer y "qryAppendCustT ype"

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
Sep 21 '07 #6
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
Sep 21 '07 #7
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*****@PCESof t.invalidwrote in message
news:_N******** **********@news svr13.news.prod igy.net...
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
Sep 21 '07 #8
ARC
This is for the import routine for an old/existing db where it was not
blocked...

Thanks!

Sep 21 '07 #9
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*****@PCESof t.invalidwrote in message
news:zZ******** **********@news svr13.news.prod igy.net...
This is for the import routine for an old/existing db where it was not
blocked...

Thanks!
Sep 21 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
8385
by: Clive Moss | last post by:
Anyone able to help? I want to update a specified number of records that match a criteria. If I use an update query it alters all the records that match - I need to alter only a variable number. Is there a way to do this? Specifically: I have a list of available serial numbers that will satisfy an order. Some of those will have been previously allocated to another order, those that
5
4477
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this error: Deleting data in a linked table is not supported by this ISAM. From what I understand, indexed sequential access method (ISAM) drivers are used to update "non-Microsoft" file formats. So why doesn't Access
5
2726
by: Brandon Mackie | last post by:
I am incredibly new to access and trying to learn as I go. I have set up a few queries one of which is executed by a docmd.runsql in visual basic. Because it is an update query it asks for confirmation every time it wants to update the table. Is there any way to get around this?
6
2400
by: Marlene | last post by:
Hi All I have the following scenario, where I have found all the duplicates in a table, based on an order number and a part number (item).I might have something like this: Order PODate Rec Qty Invoice# Item Supplier Status POReceivedDate 570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004 570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004 570133 03/09/2004 50 0 DMEDIUM L0010 PENDING 03/09/2004
7
5085
by: rednexgfx_k | last post by:
All, Problem Summary: I've running about 30 make table queries via VBA in Access 2000, and my database goes from 14,000k to over 2,000,000k. In addition, the longer the procedure runs, the bigger the performance hit VBA takes. I'm wondering how to prevent or reduce this. Details: I have a database table of queries I want to run. This table contains the query name, the SQL text of the query, the name of the target table, and whether...
3
6898
by: Nathan Bloomfield | last post by:
Hi there, I am having difficulty with a piece of code which would work wonders for my application if only the error trapping worked properly. Basically, it works as follows: - adds records from rsSource into rsDest - if it finds a key violation then it deletes the current record from rsDest and adds the new record from rsSource. This works perfectly - but only for the first found duplicate record, it brings up the error
2
1508
by: HydroPnik | last post by:
Hello all. Access 2003/Windows XP. I have set up a command button where three queries are run. The first and last queries are select and the second is an update. I only want to display the results of the last query to the user. Can someone help me out? Here's the code. Private Sub btn_Submit_Click() On Error GoTo Err_btn_Submit_Click Dim stDocName As String stDocName = "qry_Submit1"
1
2656
by: Starke | last post by:
I have some code written and it works fine, except the lines that update the form text box. I want to update the text box to let the user know what qry is executing. If I take out the Docmd.setwarnings line, it updates fine,but the user has to click yes 2 times due to them being make table queries. Code below: Me.txtStatus.Value = "Processing: qry66400aIRHebron"
4
15756
by: Mike D | last post by:
OS: Windows XP Professional Microsoft Access 2003 I am trying to update a table in my DB from a tempory table. I need to ensure that if records in the main table match records in the temp table (on the keys) they are replaced/updated by the new data in the temp table. the vba code i am running is below: Dim mySQL As String
0
8761
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8672
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8969
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7876
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6594
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5923
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4434
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2462
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.