Runtime error while updating recordset in VBA

L

le_s

Please help me with the following code. Every time it runs, it stops halfway
through and gives me a runtime error. The .mdb size goes from <100 MB, to
over 2 GB, which I believe is the source of the problem, but I can't figure
out why since all I'm doing is attempting to fill in blanks in my table (with
fields selected through SQL), with the last nonblank entry above it.

Here's the code:

Sub fillInBlanks()

Dim mySQL As String, lastNonblank As String
mySQL = "SELECT [Dash Data].[Custom Plan 1], [Dash Data].Plan, [Dash
Data].[Custom Demographic 2], [Dash Data].[Time Period], [Dash Data].[Custom
Geographic 1] FROM [Dash Data] ORDER BY [Dash Data].ID;"

Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset(mySQL, dbOpenDynaset)

For i = 0 To RS.Fields.Count - 1
RS.MoveFirst
Do Until RS.EOF
If IsNull(RS(i)) Then
RS.Edit
RS(i) = lastNonblank
RS.Update
Else
lastNonblank = RS(i)
End If
RS.MoveNext
Loop
Next i

End Sub

Thanks!
 
S

Stuart McCall

le_s said:
Please help me with the following code. Every time it runs, it stops
halfway
through and gives me a runtime error. The .mdb size goes from <100 MB, to
over 2 GB, which I believe is the source of the problem, but I can't
figure
out why since all I'm doing is attempting to fill in blanks in my table
(with
fields selected through SQL), with the last nonblank entry above it.

Here's the code:

Sub fillInBlanks()

Dim mySQL As String, lastNonblank As String
mySQL = "SELECT [Dash Data].[Custom Plan 1], [Dash Data].Plan, [Dash
Data].[Custom Demographic 2], [Dash Data].[Time Period], [Dash
Data].[Custom
Geographic 1] FROM [Dash Data] ORDER BY [Dash Data].ID;"

Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset(mySQL, dbOpenDynaset)

For i = 0 To RS.Fields.Count - 1
RS.MoveFirst
Do Until RS.EOF
If IsNull(RS(i)) Then
RS.Edit
RS(i) = lastNonblank
RS.Update
Else
lastNonblank = RS(i)
End If
RS.MoveNext
Loop
Next i

End Sub

Thanks!
Click to expand...

You've got your loops inside out. As it stands, once for every field, you
loop over the entire recordset. It ought to look like:

RS.MoveFirst
Do Until RS.EOF
For i = 0 To RS.Fields.Count - 1
If IsNull(RS(i)) Then
RS.Edit
RS(i) = lastNonblank
RS.Update
Else
lastNonblank = RS(i)
End If
Next i
RS.MoveNext
Loop
 
L

le_s

Thanks for your response. What I'm trying to do is fill in the blank spaces
in each column, with the last nonblank data in the column. So I do want to
cycle through the entire recordset for each field. For example, I have a
"Custom Plan 1" column, with cost data corresponding to it, shown by month.
The program I used to generate the data, shows "Plan A" only once for the
first month, and then is blank for all other "Plan A" months, until the first
"Plan B" month. The column is then blank again until the first month of
"Plan C" data. I want to fill in all the blanks so that it should say "Plan
A" all the way down until "Plan B", and so on.

Stuart McCall said:
le_s said:
Please help me with the following code. Every time it runs, it stops
halfway
through and gives me a runtime error. The .mdb size goes from <100 MB, to
over 2 GB, which I believe is the source of the problem, but I can't
figure
out why since all I'm doing is attempting to fill in blanks in my table
(with
fields selected through SQL), with the last nonblank entry above it.

Here's the code:

Sub fillInBlanks()

Dim mySQL As String, lastNonblank As String
mySQL = "SELECT [Dash Data].[Custom Plan 1], [Dash Data].Plan, [Dash
Data].[Custom Demographic 2], [Dash Data].[Time Period], [Dash
Data].[Custom
Geographic 1] FROM [Dash Data] ORDER BY [Dash Data].ID;"

Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset(mySQL, dbOpenDynaset)

For i = 0 To RS.Fields.Count - 1
RS.MoveFirst
Do Until RS.EOF
If IsNull(RS(i)) Then
RS.Edit
RS(i) = lastNonblank
RS.Update
Else
lastNonblank = RS(i)
End If
RS.MoveNext
Loop
Next i

End Sub

Thanks!
Click to expand...

You've got your loops inside out. As it stands, once for every field, you
loop over the entire recordset. It ought to look like:

RS.MoveFirst
Do Until RS.EOF
For i = 0 To RS.Fields.Count - 1
If IsNull(RS(i)) Then
RS.Edit
RS(i) = lastNonblank
RS.Update
Else
lastNonblank = RS(i)
End If
Next i
RS.MoveNext
Loop
Click to expand...
 
S

Stuart McCall

le_s said:
Thanks for your response. What I'm trying to do is fill in the blank
spaces
in each column, with the last nonblank data in the column. So I do want to
cycle through the entire recordset for each field. For example, I have a
"Custom Plan 1" column, with cost data corresponding to it, shown by
month.
The program I used to generate the data, shows "Plan A" only once for the
first month, and then is blank for all other "Plan A" months, until the
first
"Plan B" month. The column is then blank again until the first month of
"Plan C" data. I want to fill in all the blanks so that it should say
"Plan
A" all the way down until "Plan B", and so on.
Click to expand...

OIC. Well (if I understand you correctly) wouldn't this work: use an array
of lastNonblank, one element for each field, like this:

Dim lastNonblank(4)

RS.MoveFirst
Do Until RS.EOF
For i = 0 To RS.Fields.Count - 1
If IsNull(RS(i)) Then
RS.Edit
RS(i) = lastNonblank(i)
RS.Update
Else
lastNonblank(i) = RS(i)
End If
Next i
RS.MoveNext
Loop
 
L

le_s

Thanks Stuart. This works as well, but it doesn't solve the real problem,
which is that for some reason my database explodes in size when I try to
update the recordset. I'm only dealing with about 200,000 records, nothing
too huge, and as I mentioned before, the database is about 30 MB before
running the code. However, as it runs, the size keeps getting larger and
larger until it hits the 2 GB point, at which point it throws an error. I
guess I can stop the code before the 2 GB point, compact and rerun, but I'm
wondering why this is happening???
 
S

Stuart McCall

le_s said:
Thanks Stuart. This works as well, but it doesn't solve the real problem,
which is that for some reason my database explodes in size when I try to
update the recordset. I'm only dealing with about 200,000 records,
nothing
too huge, and as I mentioned before, the database is about 30 MB before
running the code. However, as it runs, the size keeps getting larger and
larger until it hits the 2 GB point, at which point it throws an error. I
guess I can stop the code before the 2 GB point, compact and rerun, but
I'm
wondering why this is happening???
Click to expand...

Sorry but I can't see any reason for this to occur. The code is fairly
trivial and 200,000 records is hardly gargantuan. One thing to try would be
to wrap the recordset loop in a transaction and commit every 10,000 records
or so. After that I'm out of ideas..
 
T

Tony Toews [MVP]

le_s said:
Thanks Stuart. This works as well, but it doesn't solve the real problem,
which is that for some reason my database explodes in size when I try to
update the recordset. I'm only dealing with about 200,000 records, nothing
too huge, and as I mentioned before, the database is about 30 MB before
running the code. However, as it runs, the size keeps getting larger and
larger until it hits the 2 GB point, at which point it throws an error. I
guess I can stop the code before the 2 GB point, compact and rerun, but I'm
wondering why this is happening???
Click to expand...

Are there any other users in the database? It seems like Access 2000
or newer (actually Jet 4.0) puts each record when updating on it's own
page . So that's 4 kb for 200,000 records. Hmm, that's only 800
Mb. OTOH if you are going through each record multiple times then it
could easily be that Jet invalidates the old page even if there is
only one record around.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
S

Stuart McCall

Stuart McCall said:
Sorry but I can't see any reason for this to occur. The code is fairly
trivial and 200,000 records is hardly gargantuan. One thing to try would
be to wrap the recordset loop in a transaction and commit every 10,000
records or so. After that I'm out of ideas..
Click to expand...

One thing just occurred to me: did you do a compact before trying my "use an
array" idea? I ask because I have the same suspicion as Tony, namely that
Access doesn't free up record space (which exists in 4k chunks as he
mentions) when you're hitting all records more than once (once for each
field). Using my method you only loop over the recordset once.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

traversing a large recordset 4
Compile Error - Don't know WHY? 6
Create Recordset in VBA 1
MS Access setting outlook reminders from a DAO recordset 0
can't open recordset 2
Import File Information 0
How to speed up performance while updating records ? 3
Save Text file 1

Top