Access 97 vs Access 2003 File Sizes - Getting Urgent!

[EdwardA]

Hi all,

My company has just converted from Access 97 to Access 2003. After years of largely trouble free 97 databases , I am now experiences a wierd issue with 2003.

My VBA using DAO 3.6, simply runs through each record of a table, and makes an edit to them all using standard .Edit and .Update methods. In 97 the database (fairly large - 150,000 records in the table which is edited) would grow from 30MB to 48MB, making a compact advisable, but not causing any problems.

The exact same code, running on a 2003 format Database causes the file to jump from approx 30MB to 2GB (yes 2GB) then crash as the max file size is reached.

Converting the DB to 97 from the 2003 engine and then running in 2003 is fine. Converting the DB to 2000 in the 2003 engine causes the same problems.

Bottom line -- is there a something wrong with Access 2003 - why should the file bloat so seriously?

Can anyone help? The file is set up in the standard Front End App / Back End Data config, and is single user - very straight forward system.

Cheers.

Ed.

 

[mp9]

It may help general bloating and performance to turn off AutoCorrect.

Tools, Options, General tab - uncheck everything under "Name AutoCorrect".

Also consider a regular compact on the db as a whole.

 

[EdwardA]

Hi thanks for the advice. Unfortunately, the option is already set as you describe and, whilst compacting would shrink the file, the problem is the routine bloats it so much (from 30MB to 2GB in under 60 seconds!), you don't even get a chance to do this.

Cheers anyway.

 

[JimmyBlogs]

Too many recordsets been created??
Too many temporary tables been created ??

But to do it in under 60sec is pretty fast. Do you have some redundant loops in your code. I have worked with large DB's (large for Access anyway), 2-3mil records and haven't come across that sort of "bloating" in 2002 or 2003.

 

[EdwardA]

Hi - just one recordset - working through all the records in one table - making an edit to each one.

If, instead of editing each record in the recordset, I set set the code to write the changed data to a new table (.addnew .update - DAO), the problem disappears. So it is something to do with editing records (using the .edit method in DAO) in particular.

This may be a workaround I will have to use, but I am still thoroughly confused as to why this major bloating happens and still on the look out for advice! Has no one else seems to have encountered it? (I have searched the formum and MS KB extensively).

Thanks again.... especially for the fast replies!

 

[JimmyBlogs]

It could be in your Edit Loop.
Are you using .Update after after every edit?

It sounds like you are opening to much memory while executing your Edit code. That might be stating the obvious here. Look for closing down as many open methods as you can after each edit and update.

 

[EdwardA]

JimmyBlogs,

Yes, I think it is in the Edit Loop. I am using .update after every .edit, but this has worked fine in Access 97 for a number of years. What alternative could you suggest? Due to the way the edited data is compiled, it would be difficult (but not impossible) to convert to a SQL based edit.... but if there was a simpler way to loop through the recordset in a more efficient way....

I am already closing the recordset at the end of the Edit loop.

Cheers.

 

[abaldwin]

As an alternative I would suggest you do one update using a sql statement with an ado connection.

ie. instead of

opening a recordset and looping through each record and doing basically
if this then
field1 = that
.update
.movenext
I would do something like this

stsql = update table set field1 = that where criteria is true
connection.execute stsql

I agree with the others that you are opening too many records. Why it just started I do not know but try this instead.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[EdwardA]

Andy Baldwin,

Thanks - I'll consider that route. I want to stick to DAO if I can though, as once I go down the route of ADO I will have a lot of work to convert... and I am happy in principal with DAO (but that's another thread!)...

Cheers.

 

[GFullington]

Just an idea: try increasing the ODBC buffer size to 32768.
I ran into a crashing situation in the past with large databases during a data conversion project. We had not upgraded from 97 to 2003.

 

[johntlarson]

Hi,

Has anyone resovled this issue? I have encountered it also and have been very frustrated. My table has about 500,000 rows that are being updated, one at a time using the .edit .update methods.

I tried using transactions to limit the number of changed records before committing the data but it didn't help. The DB goes from 300MB to 2+GB pretty quick and then throws an error.

Thanks,
John

 

[JoanneM]

Ok
so I've read and pondered and a far distant memory from my microsoft days came to mind

My first thought was is V2003 cloning the recordsets as it edited because of the translation of the jet 3.5 to 4 commands - that dog didn't hunt - but if it was holding each edit to write the whole shabang in one swipe that could explain so I checked out the record locking - below is what I found but that is not clearly the answer

On the Access 2000 machines, turn off Record Level Locking: Tools | Options | Advanced. It is not supported by the Access 97 back end, and can cause problems.
Expect the later versions to run a little slower, due to the JET 4 calls being converted to Jet 3.5.


Still not happy and interest now piqued I delved further and got what I think is your answer though not oneyou want to hear
from

http://www.granite.ab.ca/access/performancefaq.htm

I know we are dealing with 03 but you had to go thru 2000
to get there -....

Access 2000 development does experience a performance decrease (and a related increase of the database size) as compared to Access 97.

This is caused by the new way Access 2000 stores project items.

Project items consist of Forms, Reports, Macros and Modules. In previous versions, each object had its own record in the system table.

If a change was made to an object only that one record in the system table was updated.

With the move to include the Visual Basic Editor interface, Access now stores all project items as one blob within approximately one record in the system table.

If there are lots of code, forms and reports, then making a change to 1 object causes us to rewrite the majority of the blob that consists of all the project items. As a result, more is being written to disk then was done in the past.

Some changes to the database cause Access to make a copy of the project items instead of replacing the old project which can cause an increase in database size. If we have a large project and we end up copying it then we double the size of the project within the database.

For example, lets say we have 10 MB project and perform an action that causes us to make a copy of the project instead of replacing it, the database will grow by 10 MBs. Compacting the database at this point should recover the project no longer being used and should reclaim some space (if not all 10 MBs).

So now armed with the why answered your next question is how to stop it.

I have 3 jobs here to do then you will have my undivided attention for over an hour - I love these 'microsoft features'

jo

 

[johntlarson]

Jo,

Thank you for the information, but I don't think it directly relates to the problem I described. I am using the .update command on data rows, not project items.

I am using a transaction and have a commit after every 10000 updates to try and keep the level of system resources (lock manager) to something that is reasonable.

Also, even if every data record was copied and then replaced, the DB would double in size, which is unfortunate, but wouldn't have caused me the problems it does. My DB goes from about 180MB to over 2GB, for a 1000%+ increse. I have put counters in the code and the file size increases about 400MB per 100000 records updated.

I used the workaround presented above of inserting the "changed" data to a new table, deleting the data from the original table, and copying the new table back over to the original table. It caused the DB to grow by the amount to house the new table, which was expected.

I would prefer to do the .update in place since it is the "most correct" way to process the records, but I need a solution that works!

Any other ideas?

Thanks again!
John

 

[johntlarson]

Jo,

I just saw your second post...

Sorry, I can't send you the data. It has SSN's for about 3500 employees in it!

I checked the table and there are some numeric fields, but they are all "long". There are a number of date fields also. The remainder of the fields are mostly text with a couple "yes/no" fields thrown in for good measure.

Thanks again,
John

 

[PHV]

since it is the "most correct" way to process the records
You can't use an update query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johntlarson]

PHV,

You are correct in that an update query is the normal way to update a recordset... However, in this case there are a number if then/else statements that are setting some values and doing some calculations so it would end up being several update querries and then I would. I didn't write this, but am maintaining it. I said that I can't provide the data, which I can't, but I can provide the code. You can see the "before" and "after" version:

Function updateCompletedDate()
On Error GoTo HandleErr
Dim strSQL As String
Dim intRecordsProcessed As Long
Dim Db As DAO.Database
' Dim Rs As DAO.Recordset
Dim ws As DAO.Workspace
Dim rs As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set Db = ws.Databases(0)

Dim dtToday As Date
Dim dtDateYellow As Date
Dim strCompletedStatus As String
Dim strExam1Status As String
Dim strExam2Status As String
Dim record_count As Long
Dim ssn_prefix As Integer

dtToday = Format(Now, "mm/dd/yyyy")

'Changed from a table open to a recordset so that I can use transactions to commit the changes - J. Larson 2006-09-06
'Set Db = CurrentDb
'Set Rs = Db.OpenRecordset("tblEmployeescourses", dbOpenDynaset, dbConsistent) 'Added the dbConsistent option - J. Larson 2006-09-01

strSQL = "Select * from tblEmployeescourses"

Set rs = Db.OpenRecordset(strSQL, dbOpenDynaset)
Set rs_add = Db.OpenRecordset("tmpEmployeescourses", dbOpenDynaset)

Me!txtMessages = Format(Now, "hh:mm ampm") & " Updating Completed Date Color Codes..." & vbNewLine & Me!txtMessages

ws.BeginTrans 'Added 2006-09-05 - J. Larson

While Not rs.EOF

With rs

record_count = record_count + 1
'Debug.Print "record_count = " & record_count & " ssn = " & !SSN & "courseid = " & !CourseID

If !Expires > 0 Then
If IsNothing(!Completed) Then
strCompletedStatus = "RED"
Else

If IsNothing(!Refreshed) Then 'If the course hasn't been refreshed, use the completion date.
dtDate = !Completed + (!Expires * 365)
Else
'Added if and else condition - J. Larson - 2006-09-12
'The expiration date is whichever date is greater plus the expiration years
If (!Refreshed > !Completed) Then
dtDate = !Refreshed + (!Expires * 365)
Else
dtDate = !Completed + (!Expires * 365)
End If
End If

If dtToday <= dtDate And dtToday >= dtDate - 90 Then
strCompletedStatus = "YELLOW"
ElseIf dtDate < dtToday Then
strCompletedStatus = "RED"
Else
strCompletedStatus = "GREEN"
End If

End If
Else
If !AfterRequiredDate = True Then
If IsNothing(!Completed) Then
dtDate = Null
Else
'Original code - revised J. Larson
'If IsNothing(!Refreshed) Then
' dtDate = !Completed
'Else
' dtDate = !Refreshed
'End If

'Revised Code - J. Larson - 20061113
If IsNothing(!Refreshed) Then
dtDate = !Completed
Else 'Both the completed and refreshed dates are present
If (!Refreshed > !Completed) Then
dtDate = !Refreshed
Else
dtDate = !Completed
End If
End If
End If

If IsNothing(dtDate) Then
strCompletedStatus = "RED"
Else
If dtDate >= !RequiredDate Then
strCompletedStatus = "GREEN"
Else
strCompletedStatus = "RED"
End If
End If
Else
If IsNothing(!Completed) Then
dtDate = Null
Else

'Original code - Revised J. Larson - 20061012 - Take the more current date.
'If IsNothing(!Refreshed) Then
' dtDate = !Completed
'Else
' dtDate = !Refreshed
'End If

If IsNothing(!Refreshed) Then
dtDate = !Completed
Else 'Both the completed and refreshed dates are present
If (!Refreshed > !Completed) Then
dtDate = !Refreshed
Else
dtDate = !Completed
End If
End If
End If

'Debug.Print dtDate
If IsNothing(dtDate) Then
If dtToday <= !RequiredDate And dtToday >= !RequiredDate - 90 Then
strCompletedStatus = "YELLOW"
Else
strCompletedStatus = "RED"
End If
Else
strCompletedStatus = "GREEN"
End If
End If

End If

strExam1Status = Nz(!ExamCode1Status, "NR")
strExam2Status = Nz(!ExamCode2Status, "NR")
If !ColorCode = "NR" And IsNothing(!Completed) Then
strStatus = "NR"

Else
strStatus = "GREEN"
If !ExamCode1Status = "YELLOW" Or !ExamCode2Status = "YELLOW" Or !PrerequisiteStatus = "YELLOW" Or strCompletedStatus = "YELLOW" Then
strStatus = "YELLOW"
End If
If !ExamCode1Status = "RED" Or !ExamCode2Status = "RED" Or !PrerequisiteStatus = "RED" Or strCompletedStatus = "RED" Then
strStatus = "RED"
End If
If IsNothing(strStatus) Then
strStatus = "***"
End If
End If

'Original code - modified 20061017 - J. Larson
'.Edit
'!CompletedStatus = strCompletedStatus
'!ColorCode = strStatus
'!ExamCode1Status = strExam1Status
'!ExamCode2Status = strExam2Status
'.Update

'New Code - added 20061017 - J. Larson
rs_add.AddNew
rs_add!EmployeeCourseID = !EmployeeCourseID
rs_add!SSN = !SSN
rs_add!courseID = !courseID
rs_add!Department = !Department
rs_add!CourseName = !CourseName
rs_add!EmpType = !EmpType
rs_add!JobCode = !JobCode
rs_add!Completed = !Completed
rs_add!Refreshed = !Refreshed
'rs_add!CompletedStatus = !CompletedStatus
'rs_add!ColorCode = !ColorCode
rs_add!Required = !Required
rs_add!RequiredDate = !RequiredDate
rs_add!AfterRequiredDate = !AfterRequiredDate
rs_add!Expires = !Expires
rs_add!PrerequisiteStatus = !PrerequisiteStatus
rs_add!ExamCode1 = !ExamCode1
rs_add!ExamCode1Date = !ExamCode1Date
rs_add!ExamCode1Expires = !ExamCode1Expires
rs_add!ExamCode1Results = !ExamCode1Results
'rs_add!ExamCode1Status = !ExamCode1Status
rs_add!ExamCode2 = !ExamCode2
rs_add!ExamCode2Date = !ExamCode2Date
rs_add!ExamCode2Expires = !ExamCode2Expires
rs_add!ExamCode2Results = !ExamCode2Results
'rs_add!ExamCode2Status = !ExamCode2Status

'update the fields set by this routine
rs_add!CompletedStatus = strCompletedStatus
rs_add!ColorCode = strStatus
rs_add!ExamCode1Status = strExam1Status
rs_add!ExamCode2Status = strExam2Status
rs_add.Update
.MoveNext
End With

'Added J. Larson - 2006-09-05
If record_count Mod 1000 = 0 Then
ws.CommitTrans 'Added J. Larson - 2006-09-05
ws.BeginTrans 'Since the last transaction was committed, open another one.
'Debug.Print ("*** Commit *** - record_count = " & record_count)
End If

Wend

ws.CommitTrans 'Commit the final transaction - Added 2006/10/02 J. Larson
'Debug.Print "AFTER LOOP"


'Code added by J. Larson to copy the tmpEmployeescourses table to the tblEmployeescourses table.
DoCmd.RunSQL "delete from tblEmployeescourses"
DoCmd.RunSQL "insert into tblEmployeescourses select * from tmpEmployeescourses"
DoCmd.RunSQL "delete from tmpEmployeescourses"

ExitHere:
On Error Resume Next
rs.Close
Set rs = Nothing
Db.Close
Set Db = Nothing
Exit Function
HandleErr:
Select Case Err.Number
Case 2467
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_F004-CreateEmployeeCourseTable.updateCompletedDate" 'ErrorHandler:$$N=Form_F004-CreateEmployeeCourseTable.updateCompletedDate
End Select
Resume ExitHere
Resume Next
End Function

As I said, I didn't write it!

Thanks,
John

 

[IanAble]

Hi John, I've been following this thread with interest cos soon I'll be upgrading a db from 97 to 2003.

Looking at your code I think it might just be practical to use an update query with one function to evaluate and output multiple field values e.g.

Function CalcValue(RECORDID As Variant, COLUMNINDEX As Long, Optional COLUMN1 As Variant ,COLUMN2 As Variant,...) As Variant
Static ColumnValue(X) As Variant
Static ID As Variant

'Evaluation once per record
If Not RECORDID = ID Then
...Evaluate
ColumnValue(0)=Whatever
ColumnValue(1)=Whatever
And so on...
End If
CalcValue=ColumnValue(COLUMNINDEX)

End Function



A bit Heath-Robinson, but it might get you out of a hole and you'll have RollBack with the Query.