• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

Invalid Argument Err No 3001

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
naqayya
Asked:
naqayya
  • 6
  • 3
  • 2
  • +2
1 Solution
 
abaldwinCommented:
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
 
naqayyaAuthor Commented:
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
 
PaurthsCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PaurthsCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
naqayyaAuthor Commented:
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
 
naqayyaAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
naqayyaAuthor Commented:
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
 
naqayyaAuthor Commented:
Anyway, I would like to split the ponts between Paurths and JDettman.

Thanks guys.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
naqayyaAuthor Commented:
Thanks Jim, I will look for the Service Release.
0
 
Computer101Commented:
Points reduced to 50 for point split.

Thank you
Computer101
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now