Solved

Invalid Argument Err No 3001

Posted on 2001-09-05
13
631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

726 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