?
Solved

Invalid Argument Err No 3001

Posted on 2001-09-05
13
Medium Priority
?
634 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
Industry Leaders: 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 200 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 58
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 58
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 58
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 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