Access 2010 VBA Lost Memory

Question

I am trying to track down where I may be throwing a pointer (Lost/misdirected) while trying to concatenate several different tables. Keep in mind that the section of code below is only a small part of 16,000 lines of code but stands completely alone.

Two sections of code are below.

How the code should work:

Basically what the code should do is as follows... When I press a button (First Section of code), several table names are passed to a sub one at a time (Second section of code).

More clearly, A table name is passed (First section), then the sub runs with that table name (Second section) and places in into a table called "CymeImportTable", then table two is passed (First section) then the sub runs again (Second section) and appends table two in "CymeImportTable". I know I can put these into an array but maybe later on. I'm just trying to get everything to work first then optimize. Okay so the above works fine... sometimes.

The results:

The code runs and places each table record into one MEMO style cell. For the below code and my table sizes, I have about 4000 records with several fields in my tables that get placed into a CymeImportFile table of 4000 records of only one field. This works great sometimes and seemed to work at the beginning all the time, maybe before my tables began to get larger??

Issue:

The issue is this... The CymeImportFile table will sometimes get populated but other times will get populated with what seems to be nothing. The same number of records appear in both instances but sometimes has text (What I want), and other times does not have anything (Not what I want). Just blank records with a blank field.

What I know:

I know MEMO is not recommended, may be my issue, but I will have to largely modify my code to avoid the memo format and it suits my needs as a memo.
My tables always of contents in them and this issue is not a result of my program pulling in nothing.
This issue occurs is several other sections of my code (not shown) but this was the simplest/shortest portion. I cant post the other sections because all in all its about 16,000 lines of code.
I know we may not be able to locate the error with only the code I provided but I would like to see what you guys think to see if there is something obvious that I don't know about. I have only been programming VBA for about a month.

First section of code below: I do not believe the issue lies here but I wanted to give as much information as possible. This section below just passes the table name to the sub called "Concatenate" when a button is pressed. Keep in mind, this code is probably not optimized.

Private Sub buttonConcatenate_Click()
DoCmd.SetWarnings False
DoCmd.CopyObject , "CymeImportFile", acTable, "Template_CymeImportFile"
'DoCmd.RunSQL "DELETE * FROM CymeImportFile"
DoCmd.SetWarnings True

Dim Table As String
'[GENERAL]
Table = "GENERAL"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckGeneral.Value = 0
'[IMPERIAL]
Table = "IMPERIAL"
Concatenate.Concatenate Table
' [HEADNODES]
Table = "HEADNODES"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckHeadnodes.Value = 0
' [SOURCE]
Table = "SOURCE"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckSource.Value = 0
' [NODE]
Table = "NODE"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckNode.Value = 0
' [LINE_CONFIGURATION]
Table = "LINE_CONFIGURATION"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckLine_Configuration.Value = 0
' [SECTION]
Table = "SECTION"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckSection.Value = 0
' [SWITCH SETTING]
Table = "SWITCH_SETTING"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckSwitchSetting.Value = 0
' [FUSE SETTING]
Table = "FUSE_SETTING"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckFuseSetting.Value = 0
' [RECLOSER SETTING]
Table = "RECLOSER_SETTING"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckRecloserSetting.Value = 0
' [TRANSFORMER SETTING]
Table = "TRANSFORMER_SETTING"
'Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckTransformerSetting.Value = 0
' [SECTIONALIZER SETTING]
Table = "SECTIONALIZER_SETTING"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckSectionalizerSetting.Value = 0
' [REGULATOR SETTING]
Table = "REGULATOR_BYPHASE_SETTING"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckRegulatorSetting.Value = 0
' [SHUNT CAPACITOR SETTING]
Table = "SHUNT_CAPACITOR_SETTING"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckShuntCapacitorSetting.Value = 0
' [INTERMEDIATE NODES]
Table = "INTERMEDIATE_NODES"
Concatenate.Concatenate Table
Form_Cyme_Model_Update.CheckIntermediateNodes.Value = 0
' [RELAY SETTING]
'Table = "RELAY_SETTING"
'Concatenate.Concatenate Table
'Form_Cyme_Model_Update.CheckRelaySetting.Value = 0
Form_Cyme_Model_Update.CheckConcatenate.Value = -1
''Message Box
    MsgBox "Cyme imput has been concatenated into CymeImportFile"

End Sub

This next section is where the variable (Table name) is passed and used to place the passed table name's contents into the table "CymeInputFile".

Option Compare Database

Sub Concatenate(Table As String)
  ' Set the database
    Set dbsCyme_Model_Update = CurrentDb
    Dim Field As String

'Count records and fields
    RecordCount = CurrentDb.TableDefs(Table).RecordCount
    fieldCount = CurrentDb.TableDefs(Table).Fields.Count
    RecordCounter = 0
    ReDim fieldArray(0) As String
    ReDim recordArray(0) As String
    ReDim fieldArray(fieldCount - 1) As String
    ReDim recordArray(RecordCount - 1) As String
    Set rst = dbsCyme_Model_Update.OpenRecordset(Table)
    rst.MoveFirst
    Do Until rst.EOF
      'Field data
        fieldCounter = 0
        While fieldCounter < fieldCount - 1
        'Set Recordset
            fieldCounter = fieldCounter + 1
            Field = "Field" & fieldCounter
        'rstGENERAL
            fieldString = rst.Fields(Field)
            fieldArray(fieldCounter - 1) = fieldString
        Wend
        printCounter = 0
        For Each element In fieldArray
            If Not fieldArray(printCounter) = "N/A" Then
               holder = recordArray(RecordCounter)
                If fieldArray(printCounter + 1) = "N/A" Then
                    recordArray(RecordCounter) = holder +         fieldArray(printCounter)
                Else
                recordArray(RecordCounter) = holder + fieldArray(printCounter) + ","
            End If
                printCounter = printCounter + 1
            End If
        Next element
        RecordCounter = RecordCounter + 1
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Dim temp As String

'Print to the CymeImportFile
    Set rst = dbsCyme_Model_Update.OpenRecordset("CymeImportFile")
    'rst.MoveFirst
    RecordCounter = 0
        If rst.BOF Then
            rst.AddNew
            rst.Update
            rst.MovePrevious
        Else
            rst.MoveLast
            rst.MoveNext
            rst.AddNew
            rst.Update
            rst.MovePrevious
        End If

    For Each element In recordArray
        rst.Edit
        temp = recordArray(RecordCounter)
        rst!Field1 = temp
        rst.Update
        rst.MoveNext
        If rst.EOF Then
            rst.AddNew
            rst.Update
            rst.MovePrevious
        End If
        RecordCounter = RecordCounter + 1
    Next element
rst.Close
Set rst = Nothing


End Sub

Just tested something.... I added "Debug.Print recordArray(RecordCounter)" in section two of the code above right after line... "For Each element In recordArray" and it printed out the contents using the debug.print but did not print them in my file "CymeImportFile". — Spartakus
Why are you adding a new record, updating and then moving back to the previous one when the Recordset is not BOF? In the section after 'Print to the CymeImportFile... — Martin
You should be calling rst.AddNew before you edit each new row. The way you are doing it is confusing, because you are adding rows that you may not even use. — Martin
The spaces are on purpose to separate the tables. Basically just for formatting. I actually export CymeImportFile as a .txt file when all is said and done. — Spartakus

Spartakus Spartakus · Accepted Answer · 2015-07-10T11:20:46

I figured it out. All those differant adds and updates confused everything. I simplified it down to this... Not quite what martin Parkin was saying but your comment lead me to play around with my printing process in this section and all the other sections that were creating my tables.

New code...

'Print to the CymeImportFile
Dim bob As String
bob = 0
Set rst = dbsCyme_Model_Update.OpenRecordset("CymeImportFile")
RecordCounter = 0
    rst.MoveLast
    rst.AddNew
For Each element In recordArray

    'rst.Edit

    rst.AddNew
    temp = recordArray(RecordCounter)
    rst!Field1 = temp
    rst.Update
    RecordCounter = RecordCounter + 1
Next element
 rst.Close
 Set rst = Nothing
 ReDim fieldArray(0) As String
 ReDim  recordArray(0) As String

 Debug.Print Table
End Sub

Access 2010 VBA Lost Memory

1 Answers