By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

testing for Key Violation

P: n/a
Hello.

I have a small db that downloads a txt file then
transfers it into the database.
I have built a composite key in the import table from
dteDate, dteTime, and txtFROM

Once I process the messages etc. I set them as 'Done' using a Yes/No field
so that they don't get processed again next time.

The problem is that next time I download I get all the old messages again,
plus any new.
This then results in an error about Key Violations....0 records added dull
to null....12 not added due to Key violations....press yes to contine, no to
stop.

Which at the moment I just hit YES..since I know that this is expected (all
the old records can't be added again)

QUESTION:
Can I programattical test for the KeyViolation error and avoid the dialog
box and just move ahead with a YES response.

The setup for all this is a form button that
-downloads to the local drive
-runs a TransferText command to import the file (this is where the error
occurs)
-runs a query to 'clean up' the data.
etc.

Any Ideas??
Mal.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Mal Reeve" <lo*****@earthlink.net> wrote in message news:<9i*******************@newsread2.news.atl.ear thlink.net>...
Hello.

I have a small db that downloads a txt file then
transfers it into the database.
I have built a composite key in the import table from
dteDate, dteTime, and txtFROM

Once I process the messages etc. I set them as 'Done' using a Yes/No field
so that they don't get processed again next time.

The problem is that next time I download I get all the old messages again,
plus any new.
This then results in an error about Key Violations....0 records added dull
to null....12 not added due to Key violations....press yes to contine, no to
stop.

Which at the moment I just hit YES..since I know that this is expected (all
the old records can't be added again)

QUESTION:
Can I programattical test for the KeyViolation error and avoid the dialog
box and just move ahead with a YES response.

The setup for all this is a form button that
-downloads to the local drive
-runs a TransferText command to import the file (this is where the error
occurs)
-runs a query to 'clean up' the data.
etc.

Any Ideas??
Mal.


One way to do it is to import into a temp table that has the same
structure as the real destination table and then use an outer join to
get only the non-matching records and import them.
Nov 13 '05 #2

P: n/a
Is this a special kind of table (temp),...
or simply a table that I create, import into and then delete all records
once I have taken out the ones I want to the 'Real' table???

Mal.
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"Mal Reeve" <lo*****@earthlink.net> wrote in message

news:<9i*******************@newsread2.news.atl.ear thlink.net>...
Hello.

I have a small db that downloads a txt file then
transfers it into the database.
I have built a composite key in the import table from
dteDate, dteTime, and txtFROM

Once I process the messages etc. I set them as 'Done' using a Yes/No field so that they don't get processed again next time.

The problem is that next time I download I get all the old messages again, plus any new.
This then results in an error about Key Violations....0 records added dull to null....12 not added due to Key violations....press yes to contine, no to stop.

Which at the moment I just hit YES..since I know that this is expected (all the old records can't be added again)

QUESTION:
Can I programattical test for the KeyViolation error and avoid the dialog box and just move ahead with a YES response.

The setup for all this is a form button that
-downloads to the local drive
-runs a TransferText command to import the file (this is where the error occurs)
-runs a query to 'clean up' the data.
etc.

Any Ideas??
Mal.


One way to do it is to import into a temp table that has the same
structure as the real destination table and then use an outer join to
get only the non-matching records and import them.

Nov 13 '05 #3

P: n/a
"Mal Reeve" <lo*****@earthlink.net> wrote:
Is this a special kind of table (temp),...
or simply a table that I create, import into and then delete all records
once I have taken out the ones I want to the 'Real' table???


Nothing special. Just one you use for a short period of time.

See the TempTables.MDB page at my website which illustrates how to use a temporary
MDB in your app. http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.