Solved

How big can a recordset be?

Posted on 2001-07-03
20
203 Views
Last Modified: 2008-02-01
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
Comment
Question by:abrusko
  • 8
  • 6
  • 3
  • +3
20 Comments
 
LVL 2

Author Comment

by:abrusko
ID: 6250022
Sorry...I mean "HELD" in a recordset.
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250060
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
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6250091
It is my understanding the the recordsets size is only limited by the resources of the machine it is created on...
0
 
LVL 2

Author Comment

by:abrusko
ID: 6250093
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250115
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250118
CAn you post the code?
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6250123
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
 
LVL 2

Author Comment

by:abrusko
ID: 6250137
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
 
LVL 4

Expert Comment

by:nutwiss
ID: 6250296
do this whole process with a single SQL statement.

I'll get back to you when I work out your variable names.............
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250617
>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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 38

Expert Comment

by:PaulHews
ID: 6250622
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250625
Have you stepped through the execution of record 13,273?
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6250697
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250826
>>>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
 
LVL 17

Expert Comment

by:inthedark
ID: 6250958
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
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250970
>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
 
LVL 2

Author Comment

by:abrusko
ID: 6255493
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
 
LVL 2

Author Comment

by:abrusko
ID: 6266299
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
 
LVL 17

Accepted Solution

by:
inthedark earned 100 total points
ID: 6267360
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
 
LVL 2

Author Comment

by:abrusko
ID: 6269093
Thanks, Mr. Dark...seems to have me on the right track...Appreciate it!

Andy
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

707 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

20 Experts available now in Live!

Get 1:1 Help Now