MS Access database grow issue

[HaBe1963]

Hi,

I have created below piece of code in order to amend some data in an Access table:

    Dim Ways As DAO.Recordset
    Dim Keys As DAO.Recordset
    Dim Recordcount As Double
    Dim Records As Double
    Dim ID_Old As String
    Dim ID_New As String
    Dim STArray() As String
    Dim SaveTime As String
    
    Set Ways = CurrentDb.OpenRecordset("Ways_Sorted")
    
    Recordcount = 1
    Records = 3724755
    
    
    Ways.MoveFirst
    
    Dim word As Variant
    
    While Not Ways.EOF
        DoCmd.SetWarnings (False)
        DoCmd.OpenQuery "KeyFind:DEL"
        DoCmd.SetWarnings (True)
    
        Set Keys = CurrentDb.OpenRecordset("KeyFind")
    
        STArray = Split(Ways!Veld4, ";")
        For Each word In STArray
            If Len(word) > 0 Then
                Keys.AddNew
                Keys!IDOld = CDbl(word)
                Keys!IDNew = DLookup("[New ID]", "ID Keys", "[Old ID]=" & CDbl(word))
                Keys.Update
            End If
        Next
    
        Keys.MoveFirst
        While Not Keys.EOF
            ID_Old = " " + Trim(Str$(Keys!IDOld))
            ID_New = " " + Trim(Str$(Keys!IDNew))
            Ways.Edit
            Ways!Veld4 = Replace(Ways!Veld4, ID_Old, ID_New)
            Keys.MoveNext
        Wend
        Keys.Close
        
        Me.Tekst1 = Recordcount
        Me.Tekst3 = Records - Recordcount
        Me.Tekst5 = FileLen(Application.CurrentProject.Path & "\Map_Convert_2.mdb")
        If FileLen(Application.CurrentProject.Path & "\Map_Convert_2.mdb") > 1977142784 Then
   'Exit Sub
End If
        DoEvents
        Ways!Done = True
        Ways.Update
        Ways.MoveNext
        Recordcount = Recordcount + 1
        'CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and repair database...").accDoDefaultAction
    'Stop
    Wend
    DoCmd.SetWarnings (False)
    DoCmd.OpenQuery "Ways_Amend ID"
    DoCmd.SetWarnings (True)
    
    MsgBox "New Map created"

Actually what the code is doing is replacing the data in field "Veld4" in table "Ways_Sorte d". This field holds a string with ID's, which is splitted with

STArray = Split(Ways!Veld4, ";")

into an array.
This array is stored in a table called "KeysFound" .
Another table in my database is containing the old ID and the new ID.
As said the rest of the code will replace the old id in "Veld4"with the new ID.
It is looping through 3.7 million records this way.

My problem is that after 250 loops or so my database has grown with 1mB, meaning that my database will be above the 2gB way before the code has finished.

I can not explain why the growth is happening and how I can stop this or at least reduce the growth

[zmbd]

Line 18 - although you can do this; however, the VBA compiler allocates the memory at runtime no matter where the DIM statement is; thus, most of us will declare all of the variables at the beginning of the code, it's just easier to maintain that way.
You can prove this to yourself
- step into the code
- before you get to line 18
>Menu>View>Loca ls
You'll see that the variable is already in the list. Of Course, you do have to declare the variable in the code BEFORE any statements that use that variable, silly I know given that the memory is already allocated, yet that's the way it works :)

Line 52 - hanging "end if" ?

Lines 28 thru 34 - temp table? Temp tables will bloat your code beyond belief especially if you create and delete them over and over again within the database! Instance a secondary data file to hold your temp table and delete it when done.

Line 44 - closing and reopening the recordset over and over and over again might result in issues such as bloating.

+ If you are using Access2007 or newer, you really should consider upgrading the file format to the ACCDB version. The only reason not to is if you are using the depreciated user-security model - be advised, that is depreciated. MS has already removed support for the ACC97 format and I suspect will remove support for all of the MDB formats in future releases.

+++ Normalization (more details here)!
I suspect you have:

[pk_KeysFound][foreign key to other record][Veld4  ]
[     1      ][              2            ][a;b;c;d]

What you really should have is

[PK_profile][FK_RelatedTable][Veld4]
[    1     ][       2       ][ a   ]
[    2     ][       2       ][ b   ]
[    3     ][       2       ][ c   ]
[    4     ][       2       ][ d   ]

with the correct relationships to your tables etc...
I have a lot of "profile" type tables like this, IE tables that show the allowed parts for a given instrument system.

In anycase, with this "profile" type table, you would only need to update the one record when changing a key word. A generic from would be:

strSQL = "UPDATE pk_KeysFound" & _
   " SET Veld4 = '" & [new value] & "' & _
   " WHERE ((FK_RelatedTable = " (value) & ")" & _
      " AND (Veld4= '" & [oldvalue] & "');"
dbs.Execute strSQL, dbFailOnError

Note the use of the Execute method here, it allows for error trapping, effected record counts, and doesn't need you to turn off the warnings which can create a headache for you and the user if there's a crash or unhandled error condition.

[HaBe1963]

Hi,

Thx for your reply. I did build the database as an .accdb first, but had the same issue. I tried to include a piece of code to compile and repair the database, but did get errors. As I did this with a previous mdb database I tried this format, but had the same issues.

The Line 52 - hanging "end if" is a part in which I checked the database size in order to prevent a corrupted DB if it exceeded 2G

Actually the format of "Veld4" has to be this way. It is part of a way node, which I use in another application. Splitting this field would result in a node table of more than 50 million records, which would cause the application not or at least working too slow.

I will have a closer look at the normalization part.

[jforbes]

The line:

Keys.AddNew

creates a new record. I think from looking at your code, it is creating a new record for each combination of records in "KeyFind" and "KeyFind:DE L". That could be a lot of records.

zmbd mentions that it could be a temp table. If that is the case, I would switch to using an Array, or the Array that is already present. Regardless, if you are wondering why your database is growing, it's most likely the .AddNew line.

[zmbd]

- you cannot use "compact and repair" the currently open database from VBA. HOWEVER, I do this on back-ends to make my "backup" copies once all of the active forms are closed (actually, in my splash forms I often handle both the connections and back-ups to/for the backend). Therefore, if you use a frontend to this database you could conceivably "compact and repair"

- MDB format isn't going to help with the bloat and in fact may be worse under the Jet-Engine vs. the ACE-Engine

- that would be a terrible amount of nodes. Afraid I am not familiar with what you are doing here with the nodes. However, appears that you have that 1% requirement for a flat-data table.

Therefore, here's my next thought:
You have a table with the old and new key values.
You Step thru the current table and then with the [Vild4] in the current record, loop thru the table that has your new values and replace the old value using the replace()(syntax)
No need to split the field
No need for the temporary table

[t_keyupdates]
[pk_newkey][OldKeyName][NewKeyName]
[ 1 ][ old_1 ][New_1 ]
[ 2 ][ old_2 ][New_2 ]
[...]
[ # ][ old_# ][New_# ]

Pseudo logic
Open RS_CurrentTable
Open RS_KeyUpDate

movefirst RS_CurrentTable
movefirst RS_KeyUpDate

< Loop1 RS_CurrentTable until RS.EOF

   < Loop2 RS_KeyUpDate until RS.EOF   
      Replace([RS_CurrentTable]![Veld4], _
         [RS_KeyUpDate]![OldKeyName], _
         [RS_KeyUpDate]![NewKeyName]) 
   />

   At RS_KeyUpDate.eof RS_KeyUpDate.movefirst
   RS_CurrentTable.movenext

/>
Clean-up

[HaBe1963]

Thx both,

I skipped adding the ID's to the Keys table and instead of that I used the array directly for looking up the New ID's.
Also instead of

Ways!Veld4 = Replace(Ways!Veld4, ID_Old, ID_New)

I first build up Veld4 with the new ID's and then set Veld4 to this new Veld4 instead.
Although the code is somewhat slower there is hardly no size growth now.

Also I'm looking into a Directory object to store the Old and New ID's in to see if I can avoid a DLookup for each Old ID, but I'm still working on that.

For now I can work with this

Thx again

[zmbd]

why not do the double loop and replace with the new map table
You have to step thru the old records somehow, and unless you have thousands of records in t_KeyUpDates

Sub poc()
  Dim zdb As DAO.Database
  Dim zRS1 As DAO.Recordset
  Dim zrs2 As DAO.Recordset
  Dim zTmp As String
  Dim zsql As String
  Set zdb = CurrentDb
  Set zRS1 = zdb.OpenRecordset("t_KeyUpDates", dbOpenDynaset)
  Set zrs2 = zdb.OpenRecordset("t_Ways_Sorted", dbOpenDynaset)
  zRS1.MoveFirst
  zrs2.MoveFirst
  Do
      Do
          With zrs2
              zTmp = ![Veld4]
              zTmp = Replace(zTmp, zRS1![ID_old], zRS1![ID_New])
              .Edit
              ![Veld4] = zTmp
              .Update
          End With
          zRS1.MoveNext
      Loop Until zRS1.EOF
      zRS1.MoveFirst
      zrs2.MoveNext
  Loop Until zrs2.EOF
  zRS1.Close
  zrs2.Close
  Set zRS1 = Nothing
  Set zrs2 = Nothing
  Set zdb = Nothing
End Sub

30 lines of code, no arrays, no look-ups persay...
This is extremely fast on a local table, 100 records updated in under a second, shouldn't take very long to run over a network for a few thousand...
Line 16 and Line 18 could be condensed to one line; however, I tend to pull to string as it's easier for debugging - personal preference here I think.

[NeoPa]

Have you considered doing the update in SQL?

It would be so much faster and work more efficiently without the need for temporary tables to bump the size of the db.

[zmbd]

Neopa, as in from a stored update-action-query?

I had thought about building the UAQ string from a VBA standpoint; however, I couldn't resolve how to use a cross-reference table (i.e. [t_keyupdates] in post#5) directly without stepping thru each record to get the pairs with something like:

Sub poc2()
    Dim zdb As DAO.Database
    Dim zRS1 As DAO.Recordset
    Dim zSQL As String
    '
    Set zdb = CurrentDb
    Set zRS1 = zdb.OpenRecordset("t_newmap", dbOpenDynaset)
    '
    zRS1.MoveFirst
    Do
        zSQL = "UPDATE [t_currentmap] " & _
            " SET [t_currentmap]![KEY] = Replace([t_currentmap]![KEY],'" & _
                zRS1![oldkey] & "','" & zRS1![newkey] & "')"
        zdb.Execute Query:=zSQL, Options:=dbFailOnError
        zRS1.MoveNext
    Loop Until zRS1.EOF
    zRS1.Close
    Set zRS1 = Nothing
    Set zdb = Nothing
End Sub

Of course, there should be some error trapping here :)

[HaBe1963]

zmbd,

It looks like I was a bit too enthusiastic. All looked OK, but after 450,000 loops or so the database suddenly exploded, although at first there was no significant growth.
I think I leave MS Access as it is and write some conversion code in C++
Looks like Access isn't really helpfull in this.

Thx for all your advices

[zmbd]

If you will post the version of the code you are using we can take another look if you are still using any temp-tables then this will occur.

Try adapting either version of code that I've posted or see if NeoPa has the answer to my last question.

[HaBe1963]

zmbd,

this was the last version I used

Dim Ways As DAO.Recordset
Dim ID_Old As String
Dim ID_New As String
Dim STArray() As String
Dim SaveTime As String

Set Ways = CurrentDb.OpenRecordset("Ways_Sorted")

Ways.MoveFirst

Dim word As Variant

    While Not Ways.EOF
     
        STArray = Split(Ways!Veld4, ";")
        NewVeld = "; "
        For Each word In STArray
            If Len(word) > 0 Then
                ID_Old = " " + Trim(word)
                ID_New = " " + Trim(DLookup("[New ID]", "ID Keys", "[Old ID]=" & CDbl(word)))
                NewVeld = NewVeld + ID_New + "; "
            End If
        Next

        If FileLen(Application.CurrentProject.Path & "\Map_Convert.accdb") > 1620117760# Then
            SaveTime = Format(Str$(Hour(Now)), "00") + Format(Str$(Minute(Now)), "00") + Format(Str$(Second(Now)), "00")
            Exit Sub
        End If
        
        Ways.Edit
        Ways!Veld4 = NewVeld
        Ways!Done = True
        Ways.Update
        Ways.MoveNext
        Recordcount = Recordcount + 1
    Wend


MsgBox "New Map created"

As said, it looked fine, but after 450,000 loops the database exploded to 2GB and went corrupt

[zmbd]

+ Personally, I would move the format back to the ACCDB format

then

+ Pending Neopa's reply, please attempt either the version in
Post#7 - double-loop
or
Post#9 - Update-Execute
You should really only need to alter the code to match your table/field names.

I'm partial to the double loop; however, Neopa is usually spot-on and has a lot more experience with things like this than I do :) .


Also, I noticed that the last posted code is a sub-section of the remaining code... if there is any other manipulation going on with the tables, such as a make or delete table query, in the un-posted code then you may have issues in that code.

[HaBe1963]

zmbd,

the code as last posted is what it is. Actually the part

        DoCmd.SetWarnings (False)
        DoCmd.OpenQuery "KeyFind:DEL"
        DoCmd.SetWarnings (True)
 
        Set Keys = CurrentDb.OpenRecordset("KeyFind")
 
        STArray = Split(Ways!Veld4, ";")
        For Each word In STArray
            If Len(word) > 0 Then
                Keys.AddNew
                Keys!IDOld = CDbl(word)
                Keys!IDNew = DLookup("[New ID]", "ID Keys", "[Old ID]=" & CDbl(word))
                Keys.Update
            End If
        Next

has been replaced by:

        STArray = Split(Ways!Veld4, ";")
        NewVeld = "; "
        For Each word In STArray
            If Len(word) > 0 Then
                ID_Old = " " + Trim(word)
                ID_New = " " + Trim(DLookup("[New ID]", "ID Keys", "[Old ID]=" & CDbl(word)))
                NewVeld = NewVeld + ID_New + "; "
            End If
        Next

With this I do not create the (temp) table "Keys" which were originally holding the Old and New key, but I'm using the generated array "STArray" to create a new Veld4 ("NewVeld").
I then update Veld4 in line 31. With this I avoid creating the temp table "Keys" on and on. So no deletion or creation of any tables anymore.
Unfortunately although it looked as if that did the trick it appeared that it didn't.

Meanwhile I have created a C# piece of code which is running at the moment. Although a bit slow, it is doing his job and not complaining of any size ;-)

[zmbd]

you will not need either the array nor the DLookup using wither block of code I've posted.