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

How big can a recordset be?

Can someone please tell me what, if anything, determines how many records can be help in a recordset in VB 6.0?

Is there a limit within VB?

Thanks!
Andy
0
abrusko
Asked:
abrusko
  • 8
  • 6
  • 3
  • +3
1 Solution
 
abruskoAuthor Commented:
Sorry...I mean "HELD" in a recordset.
0
 
PaulHewsCommented:
Don't know the answer, but I'm sure it will hold more than you would ever realistically try to manipulate.  You are always better off loading records in smaller groups to work with them, even if you are processing every record in the table.

What exactly are you trying to do?
0
 
Dave_GreeneCommented:
It is my understanding the the recordsets size is only limited by the resources of the machine it is created on...
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
abruskoAuthor Commented:
I'm reading the data from an Access table and calculating some stuff for each record and then updating each record.  I blew out 2 times in a row on record number 13,273 on error "3020"  "Update or CancelUpdate without AddNew or Edit"

I wonder if it's a machine memory issue...I'm rerunning on a machine with more memory.

It doesn't make sense for that error to occur since it's all part of a loop and the "Edit" is being issued each time.
0
 
PaulHewsCommented:
That rings a bell... I once had a similar problem, and I think the error turned out to be bogus.  Something else was causing the problem...  
0
 
PaulHewsCommented:
CAn you post the code?
0
 
rkot2000Commented:
You are using loop to update 13xxx rows, and this is problem with MSAccess.

You need to run update sql statements, plus It will be faster (you can include functions with in update)

0
 
abruskoAuthor Commented:
Here's the code...it works perfectly until Access97 record number 13,273

This is the "READ" routine:

Public Sub ReadAccess()

Set rsRateTable = dbFreight.OpenRecordset("Select pro, frt_order, bl, accessorial_rc, [returned rate], [returned charge], [minimum charge], scacc, shipto, state, city, shipdate, shipfrom, shipper_state, shipper_city, class, weight from [tstRateEngine] order by scacc, shipfrom, shipto, pro")

rsRateTable.MoveLast
RecCounter = rsRateTable.RecordCount

rsRateTable.MoveFirst

RemoveNulls

RecordPointer = 1

End Sub

-------------------------------

This is the "UPDATE" routine:

Public Sub UpdateAccess()

Dim ProLookup As String
Dim OrderLookup As String
Dim BlLookup As String
Dim Accessorial_RCLookup As String
Dim FullLookup As String
Dim w As Integer

For w = 0 To RateStructure.Header.s_DetailLines - 1
   
   ProLookup = "pro = '" & InternalRateTable(w).IRR_Pro & "'"
   OrderLookup = "frt_order = '" & InternalRateTable(w).IRR_Order & "'"
   BlLookup = "bl = '" & InternalRateTable(w).IRR_Bl & "'"
   Accessorial_RCLookup = "accessorial_rc = '" & InternalRateTable(w).IRR_Accessorial_RC & "'"
   FullLookup = ProLookup & " and " & OrderLookup & " and " & BlLookup & " and " & _
                Accessorial_RCLookup
   
   With rsRateTable
      .MoveFirst
      .FindFirst FullLookup
   End With
   
   If rsRateTable.NoMatch Then
      MsgBox "Unable to find record with" & vbCrLf & _
             "pro = " & InternalRateTable(w).IRR_Pro & vbCrLf & _
             "order = " & InternalRateTable(w).IRR_Order & vbCrLf & _
             "bl = " & InternalRateTable(w).IRR_Bl & vbCrLf & _
             "accessorial_rc = " & InternalRateTable(w).IRR_Accessorial_RC, vbCritical, "ERROR"
   End If
   rsRateTable.Edit
   
   If IsNumeric(InternalRateTable(w).IRR_ReturnedRate) Then
      rsRateTable![returned rate] = InternalRateTable(w).IRR_ReturnedRate
   Else
      rsRateTable![returned rate] = 0
   End If
   
   If IsNumeric(InternalRateTable(w).IRR_ReturnedCharge) Then
      rsRateTable![returned charge] = InternalRateTable(w).IRR_ReturnedCharge
   Else
      rsRateTable![returned charge] = 0
   End If
   
   If IsNumeric(InternalRateTable(w).IRR_ReturnedMinChg) Then
      rsRateTable![minimum charge] = InternalRateTable(w).IRR_ReturnedMinChg
   Else
      rsRateTable![minimum charge] = 0
   End If
   
   rsRateTable.Update

Next w

IRTCounter = 0
rsRateTable.MoveNext

End Sub
0
 
nutwissCommented:
do this whole process with a single SQL statement.

I'll get back to you when I work out your variable names.............
0
 
PaulHewsCommented:
>rsRateTable.MoveLast
RecCounter = rsRateTable.RecordCount

Just as a suggestion, running movelast on a large recordset is a waste of resources, loading all the record pointers before you need them.  Use a Select Count(fieldname) query instead to get the recordcount.  It probably isn't what's causing your problem, but it doesn't hurt to keep it tight.

0
 
PaulHewsCommented:
I'm guessing you are never supposed to have NoMatch?

If rsRateTable.NoMatch Then
     MsgBox "Unable to find record with" & vbCrLf & _
            "pro = " & InternalRateTable(w).IRR_Pro & vbCrLf & _
            "order = " & InternalRateTable(w).IRR_Order & vbCrLf & _
            "bl = " & InternalRateTable(w).IRR_Bl & vbCrLf & _
            "accessorial_rc = " & InternalRateTable(w).IRR_Accessorial_RC, vbCritical, "ERROR"
  End If
  rsRateTable.Edit


Otherwise your NoMatch runs right into an Edit.

0
 
PaulHewsCommented:
Have you stepped through the execution of record 13,273?
0
 
inthedarkCommented:
The question was: "How big can a recordset be?" In Access 97 the answer is that no one table can be over 1GB of data.  In practice you can't reach this limit because of the space that is used by system overheads.  

rkot2000 & nutwiss suggest using SQL update queries.  This should be ok unless the database is huge. The problem with using SQL on really large databases they don't work. You have to make sure that you don't exceed the server's maximum record locks.

Which means that if you are writing an application that is to be used on a wide variety of servers you should not update more than about 50,000 records at any one time.

On some versions of Novel you can update as little as 10,000 records and still cause the server to hang.

In your case you have not posted all of the code so we can't realy see what makes the problem.  Furthermore, your DAO inifile can effect how your code works when handling large files. (e.g. Lockedits behaviour optimistic/pessimistic locking,  etc.)

Some observations from your code:

1)  The following reads the whole Dynaset into your local system and so lack of resources is unlikely at it would probably blow out here:

rsRateTable.MoveLast
RecCounter = rsRateTable.RecordCount

2)  "rsRateTable.MoveNext" as the end of your update routine seems to be a waste of time as you are not looping through the file as you are resetting the files pointer with:

Width rsRateTable
     .MoveFirst
     .FindFirst FullLookup
End With
 
3) You are not letting the system handle updated & locked records:

Before you use and Edit & Update you want to enter into a transaction.

e.g.

workspaces(0).Begintrans
rsRateTable.Edit

.....change the fields here

rsRateTable.Update

WorkSpaces(0).committrans

DBEngine.Idle dbFreeLocks
DoEvents

You must put a DoEvents in any loop (at least this makes your code better than Microsoft's)

If you don't use a transaction the DBengine will decide how many records to keep locked.

4) Avoide the use of FindFirst in large tables it gets slower and slower.  Create an index and use the opentable, index and seek propererties and methods for faster results.

5) Replace all "as Integer" to "As Long"



We don't see how InternalRateTable() comes into life and what RateStructure.Header.s_DetailLines is based upon.




 



0
 
PaulHewsCommented:
>>>1)  The following reads the whole Dynaset into your local system and so lack of resources is unlikely
at it would probably blow out here:

rsRateTable.MoveLast
RecCounter = rsRateTable.RecordCount<<<

Just the record pointers.  But no, it's not necessary.  It could still be resource problem IMO.

>>>2)  "rsRateTable.MoveNext" as the end of your update routine seems to be a waste of time as you are
not looping through the file as you are resetting the files pointer with:<<<

Agree here.  Take it out.  (in the name of eliminating possible complications.)


>Before you use and Edit & Update you want to enter into a transaction.<

Avoid flaky transactions in DAO.

>4) Avoide the use of FindFirst in large tables it gets slower and slower.  Create an index and use the
opentable, index and seek propererties and methods for faster results.<

Agree here although it probably won't help the problem.  Table type recordsets with indices are much faster than Dynaset.  I think Dynaset may use more resources too, but I don't remember.

>5) Replace all "as Integer" to "As Long"<

Too minor to bother with.  Waste more time coding than will be saved as optimization.
0
 
inthedarkCommented:
PaulHews, I expect you may be right that there could be a resource problem.  We get the impression that W can be large and therefore InternalRateTable() must be large too.

Further, the point of changing the loop counter to a Long is that it is a good habit to get in to. The maximum for an integer to hold is 32,768 and 13,273 is well on the way to this figure.  

0
 
PaulHewsCommented:
>Further, the point of changing the loop counter to a Long is that it is a good habit to get in to.

Ah I understand.  Thought you meant use longs as a general optimization.  Longs are a tiny bit faster, but not enough to worry about.
0
 
abruskoAuthor Commented:
Hi everyone....was out since tuesday.  I see there are a lot of comments...thanks.  I'm going to print and read them all and see if I can get to the bottom of this...I'll be in touch...thanks!

Andy
0
 
abruskoAuthor Commented:
Boy, do I owe you guys an apology.  I found the problem.  I had an edit further down in th eprogram that was chacking one of the fields in the record for nulls and converting it to spaces.  It was on this instruction that I got the error...so it was a valid message and related directly to a missing "edit".

Very sorry.

BUT...inthedark, you mentioned creating an index and using the opentable, index and seek propererties and methods.  I am attempting to do that, but I am getting a runtime error (3251) that says: Operation is not supported for this type of object.  What might be wrong? (Help me out with this one and I'll gladly give you the original points)...Thanks!

Andy
0
 
inthedarkCommented:
Before you can use indexes:

1) In your access db create an index.

Each index can be given a name. The name will vary between the versions of access that you have. In 97 just click on the Indexs icom when the table is open to see the index names.

2) Make sure you open the recordset by passing the table name and openning with the dbOpenTable option e.g.

Set RS=DB.OepnRecordset("MyTable",dbopentable,dbreadonly)

3) Before accessing an index you need to make sure that there is some data in the file and handle wen there isn't

If RS.recordCount<1 Then
    msgbox "No Records so can't use the index."
    exit sub
end if

4) Now you can Reference the index.

After the following command the file will be sequence by the index.

RS.Index="PrimaryKey"
 
You can use:
RS.MoveFirst ,next,last, previous etc.

And also you can use the Seek method:

RS.Seek "=",1234
or like RS.Seek ">=", StartAccount

To find an exact record

RS.Seek "=",RecID
If RS.NoMatch Then
    MsgBox "No such record"
end if

Post the code that is failing.
0
 
abruskoAuthor Commented:
Thanks, Mr. Dark...seems to have me on the right track...Appreciate it!

Andy
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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