programmiclly adding records hits db size limit

[CJ_DB]

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

 

[ruralguy via AccessMonster.com]

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?

 

[CJ_DB]

RG, 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

 

[CJ_DB]

Here 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 = strTemp & "0"
Next intloop
intloop = 0
End If

If blnChkdFile Then
Set rstTargetTbl1 =
CurrentDb.OpenRecordset("GainLossDlab")
' rstTargetTbl1.Index = "PrimaryKey" 'just a reminder
blnChkdFile = False
'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("SSN") = rstSource("ASC")
rstTargetTbl1("SAS_DATA_SEPARATOR") =
rstSource("FILE")
rstTargetTbl1.Update
Set rstTargetTbl1 = Nothing
Set rstTest = CurrentDb.OpenRecordset(strSQL2)
Else
'If present update the pertinent information if ther
is a change
rstTest.Edit
If IsNull(rstSource(rsFieldName.Name)) Then
rstTest(rstKeyTbl("NewFieldName")) =
rstSource(rsFieldName.Name)
Else
rstTest(rstKeyTbl("NewFieldName")) = strTemp &
rstSource(rsFieldName.Name)
End If
rstTest.Update
End If
End If 'Skip if not Item
strTemp = ""
Next 'Through all fields in a given source table

Set rstTest = Nothing

blnChkdFile = False

intloop = 0
strSQL2 = ""
strTemp = ""

rstSource.MoveNext

Loop 'Through a given source table

rstDestTbl.MoveNext

Loop 'Through all source tables

Set rstTargetTbl1 = Nothing
Set rstKeyTbl = Nothing
Set rstSource = Nothing
End Sub

 

[ruralguy via AccessMonster.com]

Just 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.

Here 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 = strTemp & "0"
Next intloop
intloop = 0
End If

If blnChkdFile Then
Set rstTargetTbl1 =
CurrentDb.OpenRecordset("GainLossDlab")
' rstTargetTbl1.Index = "PrimaryKey" 'just a reminder
blnChkdFile = False
'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("SSN") = rstSource("ASC")
rstTargetTbl1("SAS_DATA_SEPARATOR") =
rstSource("FILE")
rstTargetTbl1.Update
Set rstTargetTbl1 = Nothing
Set rstTest = CurrentDb.OpenRecordset(strSQL2)
Else
'If present update the pertinent information if ther
is a change
rstTest.Edit
If IsNull(rstSource(rsFieldName.Name)) Then
rstTest(rstKeyTbl("NewFieldName")) =
rstSource(rsFieldName.Name)
Else
rstTest(rstKeyTbl("NewFieldName")) = strTemp &
rstSource(rsFieldName.Name)
End If
rstTest.Update
End If
End If 'Skip if not Item
strTemp = ""
Next 'Through all fields in a given source table

Set rstTest = Nothing

blnChkdFile = False

intloop = 0
strSQL2 = ""
strTemp = ""

rstSource.MoveNext

Loop 'Through a given source table

rstDestTbl.MoveNext

Loop 'Through all source tables

Set rstTargetTbl1 = Nothing
Set rstKeyTbl = Nothing
Set rstSource = Nothing
End Sub

RG, thanks for your reply.
Yes, I have tried the results in a temp/BE db with the same results. The

[quoted text clipped - 57 lines]

 

[ruralguy via AccessMonster.com]

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

Just 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.

Here is the complete code.

[quoted text clipped - 153 lines]

 

[ruralguy via AccessMonster.com]

Put:
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.

Here 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 = strTemp & "0"
Next intloop
intloop = 0
End If

If blnChkdFile Then
Set rstTargetTbl1 =
CurrentDb.OpenRecordset("GainLossDlab")
' rstTargetTbl1.Index = "PrimaryKey" 'just a reminder
blnChkdFile = False
'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("SSN") = rstSource("ASC")
rstTargetTbl1("SAS_DATA_SEPARATOR") =
rstSource("FILE")
rstTargetTbl1.Update
Set rstTargetTbl1 = Nothing
Set rstTest = CurrentDb.OpenRecordset(strSQL2)
Else
'If present update the pertinent information if ther
is a change
rstTest.Edit
If IsNull(rstSource(rsFieldName.Name)) Then
rstTest(rstKeyTbl("NewFieldName")) =
rstSource(rsFieldName.Name)
Else
rstTest(rstKeyTbl("NewFieldName")) = strTemp &
rstSource(rsFieldName.Name)
End If
rstTest.Update
End If
End If 'Skip if not Item
strTemp = ""
Next 'Through all fields in a given source table

Set rstTest = Nothing

blnChkdFile = False

intloop = 0
strSQL2 = ""
strTemp = ""

rstSource.MoveNext

Loop 'Through a given source table

rstDestTbl.MoveNext

Loop 'Through all source tables

Set rstTargetTbl1 = Nothing
Set rstKeyTbl = Nothing
Set rstSource = Nothing
End Sub

RG, thanks for your reply.
Yes, I have tried the results in a temp/BE db with the same results. The

[quoted text clipped - 57 lines]

 

[CJ_DB]

RG,
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

Put:
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.

Here 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 = strTemp & "0"
Next intloop
intloop = 0
End If

If blnChkdFile Then
Set rstTargetTbl1 =
CurrentDb.OpenRecordset("GainLossDlab")
' rstTargetTbl1.Index = "PrimaryKey" 'just a reminder
blnChkdFile = False
'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("SSN") = rstSource("ASC")
rstTargetTbl1("SAS_DATA_SEPARATOR") =
rstSource("FILE")
rstTargetTbl1.Update
Set rstTargetTbl1 = Nothing
Set rstTest = CurrentDb.OpenRecordset(strSQL2)
Else
'If present update the pertinent information if ther
is a change
rstTest.Edit
If IsNull(rstSource(rsFieldName.Name)) Then
rstTest(rstKeyTbl("NewFieldName")) =
rstSource(rsFieldName.Name)
Else
rstTest(rstKeyTbl("NewFieldName")) = strTemp &
rstSource(rsFieldName.Name)
End If
rstTest.Update
End If
End If 'Skip if not Item
strTemp = ""
Next 'Through all fields in a given source table

Set rstTest = Nothing

blnChkdFile = False

intloop = 0
strSQL2 = ""
strTemp = ""

rstSource.MoveNext

Loop 'Through a given source table

rstDestTbl.MoveNext

Loop 'Through all source tables

Set rstTargetTbl1 = Nothing
Set rstKeyTbl = Nothing
Set rstSource = Nothing
End Sub

RG, thanks for your reply.
Yes, I have tried the results in a temp/BE db with the same results. The

[quoted text clipped - 57 lines]

Thanks,
CJ

 

[ruralguy via AccessMonster.com]

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

RG,
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

Put:
Option Compare Database

[quoted text clipped - 160 lines]

 

[ruralguy via AccessMonster.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.

RG,
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

Put:
Option Compare Database

[quoted text clipped - 160 lines]

 

[ruralguy via AccessMonster.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.

RG,
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

Put:
Option Compare Database

[quoted text clipped - 160 lines]

 

[CJ_DB]

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 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.

RG,
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

Put:
Option Compare Database

[quoted text clipped - 160 lines]

Thanks,
CJ

 

[ruralguy via AccessMonster.com]

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.

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 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

[quoted text clipped - 16 lines]

 

[CJ_DB]

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

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.

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 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

[quoted text clipped - 16 lines]

Thanks,
CJ

 

[ruralguy via AccessMonster.com]

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.

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

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

[quoted text clipped - 15 lines]

 

[CJ_DB]

Rg,

Hope you got that email. Let me know what you find out would you?

Thanks,

CJ_DB

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.

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

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

[quoted text clipped - 15 lines]

Thanks,
CJ

 

[ruralguy via AccessMonster.com]

I got the email. I'll let you know what I find out.

Rg,

Hope you got that email. Let me know what you find out would you?

Thanks,

CJ_DB

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

[quoted text clipped - 11 lines]