Error 3072 ?? 1

[cariengon]

Anyone ever get an Error 3072 when error trapping?

This was returned to me without a description. I'm pretty sure that it has to do with insufficient memory, but I wanted to make sure. I can't any reference to it in Access 97 help or this forum that is related to Access...

Thanks!
Carie

 

[JeremyNYC]

What's the code that's running? That will help more than the error number. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cariengon]

Are you sure you want all of this???

The error appears just after the code that 'Displays the record count', as the messagebox appears. Right after I click OKAY - I get the 3072. If I cancel at this point, it exits just fine.

I think it has to do with the Append query that appends the data from the temp table to the Main table. The wierd thing is that I was doing this whole procedure just fine yesterday and I have made no changes to the code or queries that have to do with this code. I've run it from my local hard drive, as well as on the network and I get it in both cases...

Thanks so much for your help Jeremy!

*************************************
Private Sub cmdImport_Click()

Dim strFile, SQLStmt As String
Dim intRecordNum, intTotRec As Integer
Dim StartDate, EndDate As Date
Dim intResponse, intSumDR, intSumCR As String

On Error GoTo Import_Err

DoCmd.SetWarnings False

'Begin Import
intResponse = MsgBox("Are you ready to begin the Import process?" & vbCrLf & vbCrLf & _
"Find the current Discoverer .txt export to import into the ART database.", vbOKCancel, "BEGIN IMPORT&quot
If intResponse = vbCancel Then
Exit Sub
End If

'Prompt the user to find the file to import
strFile = MSA_SimpleGetOpenFileName
If strFile = "" Then ' if user clicks Cancel button
MsgBox "File was not imported because you cancelled the last procedure.", vbOKOnly, "Import Cancelled"
Exit Sub
End If

'Create new record and insert File Name and Date/Time Stamp to 000 Import History table
intRecordNum = DMax("[ImpFile_Ref]", "[000 Import History]&quot + 1 'define the new value in ImpFile_REF
SQLStmt = "Insert into [000 Import History] ([ImpFile_Ref], [File Name], [Import Date/Time], [Posted Start Date], " & _
"[Posted End Date]) Values ( " & intRecordNum & ", '" & strFile & "', Now(), NULL, NULL)"
DoCmd.RunSQL SQLStmt

'Import the data into the '0001 Import Discoverer Extracts' table
DoCmd.TransferText acImportDelim, "AST Import Specs", "0001 Import Discoverer Extracts", strFile

'Update StartDate & EndDate in 000 Import History table
StartDate = DMin("[Posted Date]", "[0001 Import Discoverer Extracts]&quot
EndDate = DMax("[Posted Date]", "[0001 Import Discoverer Extracts]&quot

DoCmd.RunSQL "UPDATE [000 Import History] SET [Posted Start Date] = '" & StartDate & "' WHERE [ImpFile_REF] = " & intRecordNum & ""
DoCmd.RunSQL "UPDATE [000 Import History] SET [Posted End Date] = '" & EndDate & "' WHERE [ImpFile_REF] = " & intRecordNum & ""

'Calculate the Total Records and Sum of the DR & CR
intTotRec = DCount("[Account]", "0001 Import Discoverer Extracts&quot
intSumDR = Format(DSum("[0001 Import Discoverer Extracts]![Accounted DR]", "[0001 Import Discoverer Extracts]&quot, "##,##0.00&quot
intSumCR = Format(DSum("[0001 Import Discoverer Extracts]![Accounted CR]", "[0001 Import Discoverer Extracts]&quot, "##,##0.00&quot

'Display the Total Record Count, Debits & Credits. User must confirm and Click OK to continue.
' If user clicks Cancel, the records imported into '0001' are deleted, as well as the record
' just created in '000' that reports the imported filename and date/time stamp
intResponse = MsgBox("Total Record Count = " & intTotRec & vbCrLf & _
"Total Debits = " & intSumDR & vbCrLf & _
"Total Credits = " & intSumCR & vbCrLf & vbCrLf & _
"Do you wish to continue the import?" & vbCrLf & _
"If cancelled, no records will be imported.", vbOKCancel, "Import Totals&quot

If intResponse = vbCancel Then
DoCmd.RunSQL "DELETE * From [000 Import History] WHERE [ImpFile_REF] = " & intRecordNum & ""
DoCmd.RunSQL "DELETE * From [0001 Import Discoverer Extracts]"
Exit Sub
End If

'Append the 0001 records to 1001
' Duplicates are excluded due to query controls.
' Since SetWarnings is off, user will not know that the duplicates have been cleansed.
DoCmd.OpenQuery "0001 Append Discoverer Extracts", acViewNormal, acEdit

'Delete the 0001 records
DoCmd.RunSQL "DELETE * From [0001 Import Discoverer Extracts]"

'DoCmd.SetWarnings True

'refresh the form to see the new 000 Import History
Forms![10000 Import and Match]![10001 Import History sfrm].Requery

MsgBox "The Import is Complete! You must now 'Match' the imported entries.", vbOKOnly, "Import Complete!"

Exit Sub

Import_Err:
Select Case Err.Number

Case 3033 'no permissions: wrong workgroup file
'this error is triggered when the backup routine attempts to run, so no restore operation is necessary
MsgBox "You do not have permission to perform this operation." & vbCrLf & "Ask your database support person to set you up on the 'GWLA5500' workgroup file and try again."
Exit Sub

Case 3072 'not enough memory to complete procedure
MsgBox "There is not enough computer memory available to complete the import." & vbCrLf & _
"Close any other open applications and try again.", vbExclamation, "Import Failed"
Resume Restore_Routine

Case Else
MsgBox Err.Number & ": " & Err.Description & vbCrLf & _
"There is a problem with the data imported." & vbCrLf & _
"Filename: " & strFile & vbCrLf & "Previous database tables will be restored."
Resume Restore_Routine

End Select
Exit Sub

Restore_Routine:
'Not using copyobject as when run queries, data is appended/deleted in both backup and main table.
' DoCmd.CopyObject , "1001 Consolidated Pooled Ins", acTable, "previous_1001 Consolidated Pooled Ins"
DoCmd.RunSQL "DELETE * From [1001 Consolidated Pooled Ins] WHERE [ImpFile_REF] = " & intRecordNum & ""
DoCmd.RunSQL "DELETE * From [000 Import History] WHERE [ImpFile_REF] = " & intRecordNum & ""
DoCmd.RunSQL "DELETE * From [0001 Import Discoverer Extracts]"
Exit Sub

End Sub

 

[JeremyNYC]

Carrie,

OK, so it's going to take me a bit of time to get to this, as something just came up at the office. But one thing that I would suggest is that you look into using recordsets instead of the dreaded DSum, DCount, etc. This bit:
'Calculate the Total Records and Sum of the DR & CR
intTotRec = DCount("[Account]", "0001 Import Discoverer Extracts&quot
intSumDR = Format(DSum("[0001 Import Discoverer Extracts]![Accounted DR]", "[0001 Import Discoverer Extracts]&quot, "##,##0.00&quot
intSumCR = Format(DSum("[0001 Import Discoverer Extracts]![Accounted CR]", "[0001 Import Discoverer Extracts]&quot, "##,##0.00&quot

could be done with one recordset, and it would be much faster and much cleaner.

Also, you can use an update query to set more than one field at a time, so you can combine the two updates you do just above what I pasted in here.

And, you should know that when you do this:
Dim strFile, SQLStmt As String
the first variable is _not_ typed as string, it's a variant.

I know none of this answers your question. It's just easier to see this stuff at a glance than it is to come up with a solution.

But I _will_ get back to this.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[JeremyNYC]

Does the query 0001 Append Discoverer Extracts work when you run it outside of this procedure? That's the first thing that runs after that message box.

Ever debugged your code? Open up the window that shows this code and put a stop on the line that runs this query. Then go back to the form and click the button that calls this code. You'll go to that line. Hit f8 and you'll run your code one line at a time. Tell us what line generates the error. Then we'll get this sorted. (Oh, just reread your message and it looks like you know how to do all this stuff. Sorry. I'll leave it in, just in case I'm wrong. In any case, we'll need to know what line it is that hangs.)

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cariengon]

OH MY GOODNESS... I FEEL LIKE THE BIGGEST DORK!!!

I went and checked my query and sometime yesterday I renamed a table that was referenced in it. It turns out it's a Jet Engine Error as it didn't recognize the table name that was in it. It'd be nice if there was a description listed to easier narrow it down - isn't that what error trapping is all about?

Works fine now!! It would have taken me forever to find this had you not re-suggested me stepping through the code...

Sorry to take up your time on this, but I definitely will use your advise about the recordsets. The code moves real quick right now, so I may not change it here - but will keep in mind for other databases!!

Thanks,
Carie