VBA Recordset Update Error 3049

[empire03]

Good afternoon.
I have a table that is generated from a MTQ (Mate Table Query). The table that is generated has 7 columns and around 650,000 records and growing each day.

Due to the reporting nature I have the need to add sequence #'s and record counts to the table. I am doing this via 2 modules. The first creates 3 additional fields and appends them to the table. The second module loops thru the recordset until the record count reaches the last record (.movelast).

Anyhow what happens is the code runs fine until it thows the runtime error of 3049. As far as I can tell what is happening is the database size is exploding in size until it reaches the 2GB threshold then just quits on me.

So my question to all of you pros out there is how do I get around this problem and still get my table populated via my code. Any suggestions and or recommendations are welcome and appricated

Here is the code for the 2 modules:
======================================
Option Compare Database
------------------------------------------------
Sub AddColumnsToTempWFTbl()
'Adds 3 columns to the tempTbl
' 1. [Record #] -- Record Counter
' 2. [RT Seq]Rev --Trac Overall Seq Counter
' 3. [Ver_Seq]-- Rev-Trac / Seq Counter

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("TmpTbl /RSC/T_RM_3D - Workflow - Ordered List") ' table already exist

'Add a field to the table.
tdf.Fields.Append tdf.CreateField("Record", dbLong)
tdf.Fields.Append tdf.CreateField("RT_Seq", dbInteger)
tdf.Fields.Append tdf.CreateField("Ver_Seq", dbInteger)

'Destroy object variables
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub

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

Sub populateWFSeqValues()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lRecordsInTbl As Long
Dim lCurrentRec As Long
Dim lRTNumCurrentRecord As Long 'Rev-Trac # of Current Record
Dim lRTNumPrevRecord As Long 'Rev-Trac # of Previous Record
Dim iVerNumCurrentRecord As Integer 'Version # of Current Record
Dim iVerNumPrevRecord As Integer 'Version # of Previous Record
Dim iseqRT As Integer 'Rev-Trac WF Sequence Counter (All steps)
Dim iseqVer As Integer 'Rev-Trac WF Sequence Counter by Version #

'*** Initialize Variables
Set db = CurrentDb()
Set rs = db.OpenRecordset("TmpTbl /RSC/T_RM_3D - Workflow - Ordered List")

lCurrentRec = 1
iseqRT = 1
iseqVer = 1

With rs
.MoveFirst
.MoveLast
lRecordsInTbl = .RecordCount 'Record Count
.MoveFirst
lRTNumCurrentRecord = rs.Fields("Rev-Trac #") 'RevTrac # of 1st Record
iVerNumCurrentRecord = rs.Fields("WF_Version") 'WF Version # of 1st Record
End With

'*** Create Values for 1st Record in table.
With rs
.MoveFirst
.Edit
![Record] = 1
![RT_Seq] = 1
![Ver_Seq] = 1
.Update

Do While lCurrentRec <= lRecordsInTbl
lRTNumPrevRecord = lRTNumCurrentRecord 'Get values for Prev RT # record (Current to Prev variable)
iVerNumPrevRecord = iVerNumCurrentRecord 'Get values for Prev Ver # record (Current to Prev variable)
lCurrentRec = lCurrentRec + 1 'Increment Current Record + 1 for each else statement executed in loop

.MoveNext 'Move to next record and update current record variables
lRTNumCurrentRecord = rs.Fields("Rev-Trac #") 'Get Current records Rev-Trac #
iVerNumCurrentRecord = rs.Fields("WF_Version") 'Get Current records Version #

.Edit
![Record] = lCurrentRec 'input incremented record # in to record set
.Update

If lRTNumCurrentRecord = lRTNumPrevRecord Then 'Test if RT# from Prev matches Current records' RT#
iseqRT = iseqRT + 1 'If Prev & Curret RT # field same then increment by +1

If iVerNumCurrentRecord = iVerNumPrevRecord Then 'Test if Ver# from Prev matches Current records' Ver#
iseqVer = iseqVer + 1 'If Prev & Curret Ver # field same then increment by +1

Else
iseqVer = 1 'Current Ver # <> Prev Ver # reset iseqVer to 1
End If

Else
iseqRT = 1 'Current RT # <> Prev RT # reset iseqRT to 1

End If

'*** Populate RT_Seq # and Ver_Seq fields with values from above IF statement
.Edit
![RT_Seq] = iseqRT
![Ver_Seq] = iseqVer
.Update

Loop

End With

MsgBox lRecordsInTbl
'Clear DB and RS values from memory
Set db = Nothing
Set rs = Nothing
Set tblDef = Nothing


End Sub

======================================

Thanks in advance

 

[VilaRestal]

I can't say I'm surprised that this crashes on a table with 650,000 records.

One question: why not have those three fields permanently in place (put there as part of the make table query)? Why have to modify the design at runtime if it's always going to be doing the same thing? Then it would be a lot less likely to crash. (I think it must be to do with trying to insert pages all the way up the table. Even if it doesn't crash you're going to end up with a horribly fragmented table.)

My other suggestion would be consider moving to SQL Express as the back end. Tables this big are getting to the limits of Access. Doing what you're trying to do here in Transact SQL is a piece of cake for that engine.

 

[spikepl]

I can't figure out at a glance what is going on other than:

1. It looks like brute-force O(N**2) type of thing - such stuff can be slow on an SQL-server too.
2. Sequencing and yet the recordset is created based on a table, and not a query (which means that there is no guarantee for how the recordset is sequenced)


When posting code use code brackets: Edit->Advanced -> select code -> press #

When you run an MTQ (Mate Table Query), you mate tables and thus produce tablets? :-D

 

[empire03]

LOL, gotta love those tablets!

I do not have access to any other tools than the MS Office Suite and a tool call Qlikview... which is actually where I will be pulling reports out once I can get the data churned and setup the way I need it in access. Anyhow bottom line I am limited on resources :banghead:and therefore must work with what I have which obviously as you can tell is not exactly the best solution...

The MTQ (MAKE Table Query) is designed to sort the data in the correct sequence and output into a table. I would of loved to have written a piece of query logic that could have provided me with the sequence numbers but after weeks of searching have come up empty handed and or always advised to do this via VBA... Which is where I am now.

Is there a way run the code that adds record numbers and sequence #'s. to the update fields in my script which will some how check the size of the DB and if the DB becomes bloated it will compact then resume. Not even sure this is the right way to proceed just getting sick and tired of running into walls for something that I could do easily in excel by breaking the file up in to multiple worksheets (Which I don't want to do). Do you feel my frustration!

Anyhow appriciate the suggestions so far, keep them coming!

 

[VilaRestal]

You can still include the three fields in the Mate Table Query without assigning values to them - to leave that to the VBA. Just try to get rid of the changing the design of the table in VBA, which is the thing that would cause it to crash I'm sure. Doing all the updates in VBA is sure to be slow but shouldn't cause it to crash.

If you make table query is something like:

SELECT [Field1], [Field2], [Field3] INTO [TmpTbl /RSC/T_RM_3D - Workflow - Ordered List] FROM Table1 ORDER BY [Field1]

Change it to

SELECT [Field1], [Field2], [Field3], 0 AS [Record], 0 AS [RT_Seq], 0 AS [Ver_Seq] INTO [TmpTbl /RSC/T_RM_3D - Workflow - Ordered List] FROM Table1 ORDER BY [Field1]

Thus adding the extra fields there and saving the code having to.

BTW, does Access SQL have the 'Rank' statement does anyone know? That would negate the need for the [Record] field.

 

[VilaRestal]

When you run an MTQ (Mate Table Query), you mate tables and thus produce tablets? :-D

good one

 

[empire03]

Vila
I have to admit I was skeptical about your suggestion as my Add Columns code was a seperate module that didn't seem to explode the file size by an signifigance when ran it. However when I did put the additional sequencing fields into my MTQ and then ran my code to populate the fields, it ran without any problems! I am very much content however I don't have a clue why the difference in outcome... not sure that I want to know either at this stage in the game, just happy it is working now.

Thanks everyone for your suggestions and inquires!

 

[VilaRestal]

Good to hear it worked. I expect (and must say I don't nearly fully understand the behind the scenes stuff in Jet) it is to do with fragmenting the table and/or it's indexes when the fields get added because it has to insert pages a hundred thousand times up the table. Even if it doesn't crash then, it's just waiting to when you come to edit records. (That's my theory anyway.)

So when spike says "such stuff can be slow on an SQL-server too", speed is not the issue, it's stability: SQL server is a **** of a lot more stable than Jet when it comes to doing that sort of thing. That's why I would still recommend you consider it if you have tables with hundreds of thousands of records.