Solved

Invalid Argument Err No 3001

Posted on 2001-09-05
13
629 Views
Last Modified: 2010-05-18
I am running some VBA code that splits a name field into FirstName, MiddleNames and LastName. The code uses a recordset that has 410,000 records.

The code seems to work fine until about 300,000 records and then I get the error:

Invalid argument Err No 3001.

After this error occurs and I stop the program, I cannot insert values into any of the new fields, I get the same error. Even if I close the table (from which the records are taken) and then reopen the table and try to copy the table I get the same error.

This is the code I am using:

Private Sub cmdDonorNameSplit_Click()

    Dim dbMain As DAO.Database
    Dim rsPOTDONORS As DAO.Recordset
    Dim myMiddleNames As String
    Dim myName() As String
    Dim i As Integer
   
    Set dbMain = CurrentDb
    Set rsPOTDONORS = dbMain.OpenRecordset("POT_DONORS", dbOpenDynaset, dbConsistent, dbOptimistic)
   
    With rsPOTDONORS
        .MoveFirst
        Do While Not .EOF
           
            If !Field3 Like "INA*" Or !Field3 Like "INU*" Or !Field3 Like "SIA*" Or !Field3 Like "SIU*" Or !Field3 Like "VOU*" Or !Field3 Like "DRU*" Then
                myName = Split(!Field1, " ", -1, vbTextCompare)
               
                .Edit
                If UBound(myName) <> 0 Then !FirstName = myName(0)
                ![LastName/OrgName] = myName(UBound(myName))
               
                myMiddleNames = ""
               
                For i = 1 To (UBound(myName) - 1)
                    myMiddleNames = myMiddleNames & " " & myName(i)
                Next i
               
                If myMiddleNames <> "" Then !MiddleNames = myMiddleNames
                .Update
               
            Else
                .Edit
                ![LastName/OrgName] = !Field1
                .Update
            End If
           
            .MoveNext
        Loop
        .Close
    End With
               
    MsgBox "The Donor Name Split is complete!"
   
End Sub


The error always occurs on the .Update statement after the For...Next loop.

Can someone please tell me how to fix this problem?

Thanks.
0
Comment
Question by:naqayya
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 4

Expert Comment

by:abaldwin
ID: 6457916
check the size of the middle MiddleNames field to make sure it is big enough to hold the data being put into it.

Andy
0
 

Author Comment

by:naqayya
ID: 6457952
Andy,

The size is OK (50), and the error does not occur on one particular record, it occurs on any record after about 300,000 records.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6458069
try without the dbConsistant constant.
instead of :
Set rsPOTDONORS = dbMain.OpenRecordset("POT_DONORS", dbOpenDynaset, dbConsistent, dbOptimistic)
   

try:  
Set rsPOTDONORS = dbMain.OpenRecordset("POT_DONORS", dbOpenDynaset,,  dbOptimistic)
   
   
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 12

Accepted Solution

by:
Paurths earned 50 total points
ID: 6458073
if this is a one time operation (i assume it is...)
i would even drop the other constants.

Set rsPOTDONORS = dbMain.OpenRecordset("POT_DONORS")
   
   
0
 
LVL 57
ID: 6458510
I think it's an environment problem.  My gues this MDB is on a Novell server and your running out of locks.  Is that the case?

If not, then I'd check the amount of free disk space that you have, verify TEMP & TMP points to a valid drive/directory, and delete any temp files you have with all applications closed.

Jim.
0
 

Author Comment

by:naqayya
ID: 6459990
Hi guys,

I think I'm in big trouble now! Help!

When I tried to change the VBA code (ie take off the constants in the openrecordset statement) Access won't let me save the code. It keeps saying:

Invalid argument

and then

cannot find object "

I don't understand this. I checked and double-checked the code and it is OK.

Then I tried to do a Compact and repair and I got the same error:

Invalid argument

I tried Compact and repair again and now my CPU's (both of them) have started to do work but nothing is being read or written to the hard drive. It has been about 5 minutes and my CPU's are still doing something.

Can anyone help please? Thanks.
0
 

Author Comment

by:naqayya
ID: 6459997
Oh and Jim, the mdb is sitting on my Hard drive, although I am part of a Novell network. But I am doing this work without logging on to the network. And I have enough space in my temps, about 6 Gigs.
0
 
LVL 57
ID: 6460418
nagayya,

OK.  Just double check TEMP and TMP environment variables.

Then do:
1. Backup
2. Start Access/MDB with /decompile:

  C:\..\MSACCESS.EXE /DECOMPILE C:\....\myMDB.MDB

3. Open a module in design mode
4. Click tools/ references
5. Uncheck one (note which)
6. Close the MDB and Access
7. Reopen and recheck the same reference.
8. Also not if any are listed as "MISSING" or "BROKEN" (if so you need to figure out why).
9. Now compile.  You should not get any errors.

10.  Try the query again.  If it still fails, then set the queries UseTransaction property to false.  This allows Access to break up the query process into multiple parts.  If this works, then it's definitly an environment problem in one way, shape, or form.

Let me know,
Jim.
0
 

Author Comment

by:naqayya
ID: 6470384
Sorted the problem, guys.

The problem was that during the running of the code Access bloated to the max 2Gb. Thats why Access wouldn't do anything after that, not even let me change the code.

I solved the problem by creating a new blank db and importing the old db into the new one (all tables, queries, etc.) The result was a healthy 800 Mb db.

Then I split the 400,000 records table into two 200,000 ones and ran the code separetly on each table. When the first half was finished Access bloated to 1.5 Gb, so I had to run the Compact and Repair before I started the second half.
0
 

Author Comment

by:naqayya
ID: 6470386
Anyway, I would like to split the ponts between Paurths and JDettman.

Thanks guys.
0
 
LVL 57
ID: 6470418
Actually, I don't think you should.  You solved the problem on your own (good work BTW).

  First time I've heard of a database bump into the 2GB limit during an operation. As a side note on that, make sure your up to date on JET Service Packs/Releases.  There was a bug in JET 4.0 in the initial release that cause database bloat.

Jim.

0
 

Author Comment

by:naqayya
ID: 6470430
Thanks Jim, I will look for the Service Release.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 6472953
Points reduced to 50 for point split.

Thank you
Computer101
Community Support Moderator
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 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