Solved

Import csv error - Record is deleted

Posted on 2009-04-08
11
488 Views
Last Modified: 2013-11-28
Greetings,
I have a button that first deletes all records from 5 tables, then imports data using csv files.  I get the runtime error 3167 - Record is deleted that points to the Me.Requery line in the code.

The data are in the tables as they should be, but the controls all show #deleted#

The odd thing is, if I just bypass the error (click END) and rerun this, it all works OK.
Private Sub cmdImportMDB_Click()
'Turn off the warnings
DoCmd.SetWarnings False
'Provide data overwrite warning
response = MsgBox("Are you sure you want to OVERWRITE the existing data?", vbOKCancel, "Import Species List")
If response = 1 Then
DoCmd.RunSQL "DELETE FROM WetForm"
DoCmd.RunSQL "DELETE FROM WetVeg"
DoCmd.RunSQL "DELETE FROM WetHyd"
DoCmd.RunSQL "DELETE FROM WetSoil"
DoCmd.RunSQL "DELETE FROM SpeciesLookup"
DoCmd.TransferText acImportDelim, , "WetForm", "C:\WetForm\WetFormExport.csv", True
DoCmd.TransferText acImportDelim, , "WetVeg", "C:\WetForm\WetVegExport.csv", True
DoCmd.TransferText acImportDelim, , "WetHyd", "C:\WetForm\WetHydExport.csv", True
DoCmd.TransferText acImportDelim, , "WetSoil", "C:\WetForm\WetSoilExport.csv", True
DoCmd.TransferText acImportDelim, , "SpeciesLookup", "C:\WetForm\SpeciesLookupExport.csv", True
MsgBox "WetForm, WetVeg, WetHyd, WetSoil, SpeciesLookUp successfully Imported", vbOKOnly
Else
End If
Me.Requery ' This is where the error points to
DoCmd.SetWarnings True
End Sub

Open in new window

0
Comment
Question by:phmurphy
  • 6
  • 5
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24097279
try moving the Me.Requery after the MsgBox



MsgBox "WetForm, WetVeg, WetHyd, WetSoil, SpeciesLookUp successfully Imported", vbOKOnly

Me.Requery '

Else
End If

DoCmd.SetWarnings True
End Sub
0
 

Author Comment

by:phmurphy
ID: 24097357
I get the same error.  It is odd that just by running it a second time it works OK, and all subsequent runs are OK too.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24097488
what is the recordsource of the form?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:phmurphy
ID: 24097517
The table WetForm is the mainform and the other Wet... tables are subforms, and Specieslookup is a lookup table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24097639
try, instead of me.requery

me.recordsource="select * from wetform"

see if it makes a difference
0
 

Author Comment

by:phmurphy
ID: 24097817
Exact same error.  Same result (no error) when reruning the same routine a second time.
I also tried me.refresh.
Here is what the help info is on the error

Record is deleted. (Error 3167)
You referred to a record that you deleted or that another user in a multiuser environment deleted. Move to another record, and then try the operation again.

Perhaps I need to do something to the tables after I delete all the records?

 
0
 

Author Comment

by:phmurphy
ID: 24098183
It seems as if the first time I run the routine it puts the tables to "sleep", and when I run it a second time it wakes them up.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24098891
do you need to run this importing of new data on this form?

can you tell more of the process you are doing?
0
 

Author Comment

by:phmurphy
ID: 24098969
Yes I do need to use the csv import.

What I am doing is exporting and importing a csv file that will be used/edited on a PDA.  Using sync software is so glitchy and unreliable I just prefer doing it this way.  The tables are just fine during the import, the form is just having a hard time with it.  As I was saying, if I just ignore the error and run the import a second time, it works fine from then on.  I can export and import multiple times with no error.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24099036
<Yes I do need to use the csv import.> Do you have to use this form for importing the CSV?

or we can trap the error then just clear it..


'error handling codes

on error goto ImportEH





ImportEH:
      if err.number=3167  then
         err.clear
         resume next
     end if

0
 

Author Closing Comment

by:phmurphy
ID: 31568032
Sorry it took so long.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question