473,837 Members | 1,450 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Transferspreads heet Trapping Error for Duplicate records

I have a procedure to automate bringing several Excel files into our
Access tables, on a daily basis.

The problem is that if the user has a problem, and tries to run the
import again (maybe 3 files imported then there was a data problem and
they want to re-import after fixing the problem), I can't get the Error
handling to fire if the user is attempting to import duplicate key
records.

Message when I try to import records already on the table. There are
17 records on the .xls.

Microsoft Access was unable to append all the data to the table

The contents in the fields of 0 record(s) were deleted, and 17
record(s) were lost due to key violations.

Here is my code. I've tried trapping error 3022, 10014, 10018 and none
of them works.

I looked on prior posts, but didn't see the same problem. Thank you
very much.
Sara

Sub ImportData()
On Error GoTo ImportData_Err

SaleDate = Me.getSaleDate
strMsg = "Nothing Imported"
'------------------------------------------------------------
' POS10A - Store Sales T:StoreSalesDat a
'------------------------------------------------------------

strStepErrorMsg = "POS10A"
strImportFileNa me = Format([SaleDate], "mm-dd-yy") & " POS10A" &
".xls"

' MsgBox strImportFileNa me

DoCmd.TransferS preadsheet acImport, 8, "T:Store Sales Data", _
"\\server-03\Building19\P roductionImport s\" & strImportFileNa me,
True, ""

strMsg = "POS10A imported"

' 9 other import files here - all basically the same format
'------------------------------------------------------------
' MDYYMMDD.xls - Spags Tax Data
'------------------------------------------------------------

strImportFileNa me = "TX" & Format([SaleDate], "yymmdd") & ".xls"

' MsgBox strImportFileNa me

DoCmd.TransferS preadsheet acImport, 8, "tblSpagsTaxInf o", _
"\\server-03\Building19\P roductionImport s\" &
strImportFileNa me, True, ""

strMsg = "Spags Tax, " & strMsg

Importdata_Exit :
Exit Sub

ImportData_Err:
If Err.Number = 10014 Then
' If the problem is duplicate key, tell the user what was already
imported
' and the query or table that had the problem (**??? tried error
3022?)
MsgBox "Data was imported for " & strMsg
MsgBox "The problem file or query is " & strStepErrorMsg
Else
If Err.Number = 10018 Then
' If the problem is duplicate key, tell the user what was already
imported
' and the query or table that had the problem
MsgBox "Data was imported for " & strMsg
MsgBox "The problem file or query is " & strStepErrorMsg

Else
' If the problem is not dup key, display the error information
MsgBox "Data was imported for " & strMsg
MsgBox "The problem file or query is " & strStepErrorMsg
MsgBox Err.Number & " " & Err.Description & "Please call
Sara with this info."
End If
End If

Resume Importdata_Exit

End Sub

Jan 10 '06 #1
6 8210
Red
I just want to reiterate my issues with the transferspreads heet
method...

I hate using it, it's SO buggy.... and definately gives you errors (as
seen by Sara's problems with the above)

Now, Onto Sara...

There are two ways to 'error trap' this situation...

I will describe breifly how to do each..

1) Use the Transferspreads heet methos to x-fer to a 'temp' table, and
then have a query add data that is not duplicated between the 2 tables

2) (The better way IMHO) Import progmatically, checking on each record.
Assuming you don't have thousands of lines in any of the spreadsheets,
it's really not a big deal to do it progrmatically. ..

I have started making a wiki on MrExcel.com on how to progmatically
transfer data to and from Excel/Access.. if you require more info, I
will point you in the right direction.

~Red

Jan 10 '06 #2
I appreciate the thoughts, but I am not that sophisticated, so I'd
rather do the error trapping if I could.

Anyone else have ideas?

Thanks

Jan 10 '06 #3
Change the error handler so that it will show you the error number that
occurs when the user tries to add duplicate records:

msgbox err.Number & " " & err.Description

Then you'll know what error is occuring so you can trap it.

Linda

"sara" <sa*******@yaho o.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .
I appreciate the thoughts, but I am not that sophisticated, so I'd
rather do the error trapping if I could.

Anyone else have ideas?

Thanks

Jan 10 '06 #4
I do display the Error Number. The message I put in the post comes up
BEFORE it gets to error handling, so there is no number. If I say "NO"
to the message, I get 2501 - the Transferspreads heet was cancelled. If
I say "Yes" the code just moves on, which isn't what I want.

Any ideas? Anyone?

Thanks

Jan 11 '06 #5
Red
LMAO... I think I just got what you are trying to do... heh...

kinda anyhow....

To get rid of the annoying "The contents in the fields of 0 record(s)
were deleted, and 17
record(s) were lost due to key violations. " message, use the command
"DoCmd.Setwarni ngs = False"

However you said:
If I say "NO"
to the message, I get 2501 - the Transferspreads heet was cancelled. If
I say "Yes" the code just moves on, which isn't what I want.


So, what exactly DO you want it do after the message?

~Red

Jan 11 '06 #6
I have been setting warnings to False (and then true later). However,
IF the user tries to import a day she already imported or (more likely)
corrected some data and wanted to import the NEW data, the message that
tells her there is already data for that date on the table is
suppressed, due to False.

I want to be able to trab the message that there are duplicate keys and
tell the user. The manager could then evaluate: Delete the "old"
records that they were trying to correct, or whatever.

We get our data emailed to us from a 3rd party every day, and, on
occasion, we have these problems. I just can't get the code to NOT
suppress that message - so I can deal with it myself.

Is this more clear? Any ideas?

Thank you -
Sara

Jan 11 '06 #7

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

Similar topics

1
838
by: Gary Lundquest | last post by:
It appears to me that MySQL version 4 returns an error messge when doing an Insert that results in duplicate entries. Version 3 did NOT return an error - it dropped the duplicate entries and ran to completion. Version 4 seems to STOP when it encounters a duplicate entry, so that the records before the duplicate are inserted and the records after the duplicate are not inserted. 3.22.27.1 - previous ver MySQL that did not return error...
4
2209
by: Doo-Dah Man | last post by:
I hope this is the right group for this question, if it's not please direct me. I have a data-driven website that allows users to enter records for sales leads. It all works perfectly. The only thing I want to do right now is prohibit users from entering the same lead twice. I have a PK field in the MS Access database that, obviously, disallows duplicate records. However, when such an attempt is made, the browser redirects to some...
0
2734
by: Frances | last post by:
Hi All, I'm having a problem trying to add a record to a simple Access 2000 db (db is very similar to an address book but with more info than the usual address, phone, etc.). The database is one table, 36 fields. The record_id field is an autonumber field (long int) and primary key. The rest of the fields comprise of 30 text fields, 3 memo fields, 1 date field and 1 currency field. Text fields vary in number of characters allowed. ...
3
4629
by: Paul | last post by:
I have an Access 2000 database with a form that is giving me some major headaches. When you open the form, it displays all records and allows editing, but has AllowAdditions set to False so that the user has to use my New Record button. When you click the New Record button, the form presents a new record for editing. My client wants to use the Escape key to cancel changes to new or existing records. On existing records, Access already...
3
11077
by: user_5701 | last post by:
Hello, I'm getting an error with a Docmd.Transferspreadsheet line of code: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "tblTest", pathAndFilename, True The above line works perfectly fine, but when I change the table name argument (tblTest) to the name of a query, it fails and gives the following error:
3
6906
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
9
2113
by: 47computers | last post by:
Pretty new to PHP, I recently started learning about error trapping. As of right now, I include the following into a page in my website: -------BEGIN PASTE-------- error_reporting(E_ERROR | E_PARSE); set_error_handler("SendErrorReport"); function SendErrorReport($errorNumber, $errorMessage, $errorFile, $errorLine, $vars) {
0
1852
by: robert.waters | last post by:
Hello, When executing DoCmd.TransferSpreadsheet to an .xls file that's being used as a datasource for a Word mail merge document, if that Word document is currently open, the TransferSpreadsheet action fails with an 'Error 0'. I have tried different methods of trapping that error, and I still cannot get my app to indicate that the user should exit Word: I have tried disabling the error handler before DoCmd is called, looking for an Err...
4
7611
by: franc sutherland | last post by:
Hello, I am using Access 2003. I am having trouble trapping the "can't append all the records in the append query" error message when appending data to a query from a table which is linked to an excel spreadsheet. There are two tables. One is a list of general contacts, and the other is a list of clubs. The clubs contain members who are within the contacts table. When I add a list of new club members from the
0
9842
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
10874
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10566
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10623
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7806
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
5668
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...
0
5848
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4040
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3124
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.