Developer Forum

Folks, I have vba code that processes a translation and transfer of fields
and data from one table to another. The table I am coming from has cryptic
field names and some data that needs to be evaluated and changed before
delivery/matching to the new table which has more descriptive field names and
the cleaned data. The process is something like this :
Loop for each of the records in the source table
Match source(fieldname) to translation table to retrieve desired
newfield name
'****Below is the problem.
Set rstTargetTbl1 = CurrentDb.OpenRecordset("GainLossDlab")
' rstTargetTbl1.Index = "PrimaryKey"
'If not present add a new row for this Item
rstTargetTbl1.AddNew
If IsNull(rstSource(rsFieldName.Name)) Then
rstTargetTbl1(rstKeyTbl("NewFieldName")) = rstSource
(rsFieldName.Name)
Else
rstTargetTbl1(rstKeyTbl("NewFieldName")) = strTemp &
rstSource(rsFieldName.Name)
End If
rstTargetTbl1("BBN") = rstSource("ASC")
rstTargetTbl1("SAS_DATA_SEPARATOR") = rstSource("FILE")
rstTargetTbl1.Update
Set rstTargetTbl1 = Nothing
'**** End of problem area
LOOP through all addtional fields in source table insert into target
tbl record

The problem is that when it goes through this process every time it adds a
new record the size of the database increases by about 920kb. I am trying to
processes about 267000 records(record is 138 fields for a total record size
of 552bytes). The source table is linked in but the size of the db with the
data is only 150MB or so. The code db with a partial complete of this process
is about 100M but it expands to the 2G limit after processing say 10k
records. I have done the process manually and with both the source and target
data tables as local the db is only 525MB.

Any thoughts on what is causing this and how to recode around it? I know of
some options but I done this operation in Access 97 and Access 2000( I am
using Access 2002/3 format and DAO).

Thanks,
CJ

I haven't examined your code very carefully but have you tried doing most, if
not all of the work in a temporary mdb and copy the results back to your
normal BackEnd when you are done?



--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.yqcomputer.com/

G, thanks for your reply.
Yes, I have tried the results in a temp/BE db with the same results. The
code db as I described is only 10M without the result data and is 100M when
compacted with the partial data. I am at a loss because I am destroying all
the code objects(recordsets) when no longer needed(I have also left them open
but outcome has been the same).
An alternative solution I have implemented is to programmically construct
the SQL and then perform updates to clean my data but my initial solution
should not produce this type of space leak.

I am really puzzled by the behavior of this code and hope someone can help.

Thanks, CJ

"ruralguy via AccessMonster.com" wrote:

ere is the complete code.

Public Sub fncFill_GainLossDlab_Table()
'This process is used to created a data tables with screened data with
meaningful field names.

Dim strRecSet As String, strUser2 As String
Dim strSQL2 As String, strTemp As String, rstUser2 As Recordset, RecUserType
As String
Dim intloop As Integer, intCntr As Single
Dim strFldPath As String, strFldName As String
Dim varData As Variant, idxRecord As Index

'VBA or VB Script
Dim strFileName() As String, strTempArr() As String
Dim blnChkdFile As Boolean
Dim rstSource As Recordset, rstTargetTbl1 As Recordset, rstDestTbl As
Recordset, rstKeyTbl As Recordset, rstTest As Recordset
Dim rsFieldName As Field, fldTemp As Field
Dim qryDef1 As QueryDef, tmpQryDef As QueryDef

blnChkdFile = False
intCntr = 0
Set rstKeyTbl = CurrentDb.OpenRecordset("Column_Descriptors")
'VariableX = cryptic field name
'Newfieldname = meaningful field
name

rstKeyTbl.Index = "DataFieldName" 'set on VariableX above

Set rstDestTbl = CurrentDb.OpenRecordset("Tables2Process")
If (rstDestTbl.BOF And rstDestTbl.EOF) Then
Call MsgBox("Table " & rstDestTbl.Name & " is empty. Leaving
Program; Can't work like this!", vbOKOnly, "Major Error")
Exit Sub
Else
rstDestTbl.MoveLast
rstDestTbl.MoveFirst
End If
Do Until rstDestTbl.EOF
Set rstSource = CurrentDb.OpenRecordset(rstDestTbl("TblName"))
If (rstSource.BOF And rstSource.EOF) Then
Call MsgBox("No Data in " & rstDestTbl("TblName") & ". Leaving
table!", vbOKOnly, "Major Error")
Exit Sub
Else
rstSource.MoveLast
rstSource.MoveFirst
End If

strSQL2 = ""

Do Until rstSource.EOF

'Check to see if this BBN code and File name are already present
in the destination table
strSQL2 = "SELECT * FROM GainLossDlab " & _
" WHERE (GainLossDlab.BBN = '" & rstSource("ASC") &
"')" & _
" AND (GainLossDlab.SAS_DATA_SEPARATOR = '" &
rstSource("File") & "');"
Set rstTest = CurrentDb.OpenRecordset(strSQL2)
If (rstTest.EOF And rstTest.BOF) Then
blnChkdFile = True
Else
blnChkdFile = False
End If

'Move each field in the source file to the destination target
after mapping
'with the field names table("Column_Descriptors")
For Each rsFieldName In rstSource.Fields

rstKeyTbl.Seek "=", rsFieldName.Name

If (rstKeyTbl.NoMatch) Or (Not blnChkdFile And Not
blnFirstTime) Then
'Skip This field something is wrong
intCntr = intCntr + 1
Exit For ' Keep moving
Else
'Update bad field data from source
If (rstKeyTbl("NewFieldName") Like "*YYMM*") And _
(Len(rstSource(rsFieldName.Name)) < 4) _
Then
'Add leading zeros for YYMM format stripped from
original source data
For intloop = 1 To (4 -
Len(rstSource(rsFieldName.Name)))
strTemp = strT

ust a thought, you have not specified the library in your Dim statements and
Access picks the first reference in the list. If you have both ADO and DAO
libraries specified in your references then you should disambiguate your Dim
statements to make sure Access is doing what you want. It looks like you are
using DAO so try:

Dim rstUser2 As DAO.Recordset

..and any other DAO objects you are using. Meanwhile, I'm examining the
code for anything else obvious.

CJ_DB wrote:

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200707/1

I also noticed you "Call MsgBox..."! Have you defined your own MsgBox?



--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.yqcomputer.com/

ut:
Option Compare Database
Option Explicit

..at the top of your module and try and compile again and post back if there
are any changes you want to make.

CJ_DB wrote:

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

G,
I have Compare and Explict options on. I have also /decompile and recompiled
db multiple times. I have imported into new database and compiled. I have
only DAO reference for this db. No change at this time. I have been running
this process for 2 days now to see how long it will take to finish. It will
only load about 10K records before it reaches max size. Wierd or what !!??

Thanks for letting me pick your brains. Keep 'em coming.

CJ

"ruralguy via AccessMonster.com" wrote:

Is blnFirstTime a public variable somewhere? It is not defined in the sub.



--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.yqcomputer.com/

CJ,
I've been looking over the code to try and see what it is doing and something
just struck me. Any chance you have coded in dBase or FoxPro or other xBase
styles? Just curious.



--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.yqcomputer.com/

I think rather than using a recordset to look up FieldNameChanges, I would
bring the Column_Descriptors table into an array. I believe it will use less
memory and run quite a bit faster.



--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.yqcomputer.com/

Rg,

Yes I have code in foxpro, db V, Rbase and others. Thanks for the array
suggestion however, my main issue is not speed but db size. I any of the code
you see and as I have described the records, what is causing this db to
balloon to 2 GB from 400MG?

Thanks,
CJ

I don't know all of the details but I'm certain that RecordSets consume space
in a db when they are in use. Try the array idea and see if the size issue
subsides.



--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.yqcomputer.com/

Rg,
No such luck. I used arrays for the intermediary table and nothing changed.
Thanks for sticking with me on this.

CJ

Any chance you could send me enough of the db so I could do a little
troubleshooting here? If it is too full of private data then that is not an
option. If you could send something then use Rural Guy at Wild Blue dot Net.



--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.yqcomputer.com/