Solved

Invalid Argument Err No 3001

Posted on 2001-09-05
13
625 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now