Over flow error when runing a loop

  • Thread starter Thread starter David McKnight
  • Start date Start date
D

David McKnight

I have a form that uses temp databases in a loop calculation.

If I use the statement " Set rs = CurrentDb.OpenRecordset(strTableName2,
dbOpenDynaset, dbAppendOnly)" I get a type mismatch error.

if I rem that statement out I get a overflow error (I believe my tep
database use up 1 gig) after about 1050 Loops. I want to Loop +2048 times.

The temp database are creaded by the following code:

'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)

strTableName = "143 Quality Scores Union Query DB"
strTableName1 = "153 Quality Scores Union Query DB"
strTableName2 = "163 Quality Scores Union Query DB"

'strBracketedTableName = "[" & strTableName & "]"
' Delete the link to the temp table if it exists

If TableExists(strTableName) Then
CurrentDb.TableDefs.Delete strTableName
End If
If TableExists(strTableName1) Then
CurrentDb.TableDefs.Delete strTableName1
End If
If TableExists(strTableName2) Then
CurrentDb.TableDefs.Delete strTableName2
End If


' Create the temp table

Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)

dbsTemp.TableDefs.Append tdfNew
End With

Set tdfNew1 = dbsTemp.CreateTableDef(strTableName1)
With tdfNew1
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)

dbsTemp.TableDefs.Append tdfNew1
End With

Set tdfNew2 = dbsTemp.CreateTableDef(strTableName2)
With tdfNew2
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)

dbsTemp.TableDefs.Append tdfNew2
End With




dbsTemp.TableDefs.Refresh

Dim tdfLinked As TableDef

' Link to the Import tables in the temp MDB
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked

CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow

'Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset,
dbAppendOnly)

Dim tdfLinked1 As TableDef

' Link to the Import tables in the temp MDB
Set tdfLinked1 = CurrentDb.CreateTableDef(strTableName1)
tdfLinked1.Connect = ";DATABASE=" & strTempDatabase
tdfLinked1.SourceTableName = strTableName1
CurrentDb.TableDefs.Append tdfLinked1

CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow

'Set rs = CurrentDb.OpenRecordset(strTableName1, dbOpenDynaset,
dbAppendOnly)

Dim tdfLinked2 As TableDef

'Link to the Import tables in the temp MDB
Set tdfLinked2 = CurrentDb.CreateTableDef(strTableName2)
tdfLinked2.Connect = ";DATABASE=" & strTempDatabase
tdfLinked2.SourceTableName = strTableName2
CurrentDb.TableDefs.Append tdfLinked2

CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow

'Set rs = CurrentDb.OpenRecordset(strTableName2, dbOpenDynaset,
dbAppendOnly)
 
I found the problem - file was growing but overflow problem was because of a
math error, the loop was calculating bigger and bigger values through each
loop - it would overflow just after the last loop had values equal to
#.###^306. I fixed my math and loops worked fine. As I said my temp file
still grows but I didn't reach any limit after fix - I assume there is a
limit to file size Access can work with.


--
David McKnight


David McKnight said:
I have a form that uses temp databases in a loop calculation.

If I use the statement " Set rs = CurrentDb.OpenRecordset(strTableName2,
dbOpenDynaset, dbAppendOnly)" I get a type mismatch error.

if I rem that statement out I get a overflow error (I believe my tep
database use up 1 gig) after about 1050 Loops. I want to Loop +2048 times.

The temp database are creaded by the following code:

'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)

strTableName = "143 Quality Scores Union Query DB"
strTableName1 = "153 Quality Scores Union Query DB"
strTableName2 = "163 Quality Scores Union Query DB"

'strBracketedTableName = "[" & strTableName & "]"
' Delete the link to the temp table if it exists

If TableExists(strTableName) Then
CurrentDb.TableDefs.Delete strTableName
End If
If TableExists(strTableName1) Then
CurrentDb.TableDefs.Delete strTableName1
End If
If TableExists(strTableName2) Then
CurrentDb.TableDefs.Delete strTableName2
End If


' Create the temp table

Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)

dbsTemp.TableDefs.Append tdfNew
End With

Set tdfNew1 = dbsTemp.CreateTableDef(strTableName1)
With tdfNew1
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)

dbsTemp.TableDefs.Append tdfNew1
End With

Set tdfNew2 = dbsTemp.CreateTableDef(strTableName2)
With tdfNew2
.Fields.Append .CreateField("Season", dbInteger)
.Fields.Append .CreateField("Home", dbText)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score",
dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Score
Win-Loss", dbDouble)
.Fields.Append .CreateField("Avg Of Avg Of Home Modified Log Score",
dbDouble)

dbsTemp.TableDefs.Append tdfNew2
End With




dbsTemp.TableDefs.Refresh

Dim tdfLinked As TableDef

' Link to the Import tables in the temp MDB
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked

CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow

'Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset,
dbAppendOnly)

Dim tdfLinked1 As TableDef

' Link to the Import tables in the temp MDB
Set tdfLinked1 = CurrentDb.CreateTableDef(strTableName1)
tdfLinked1.Connect = ";DATABASE=" & strTempDatabase
tdfLinked1.SourceTableName = strTableName1
CurrentDb.TableDefs.Append tdfLinked1

CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow

'Set rs = CurrentDb.OpenRecordset(strTableName1, dbOpenDynaset,
dbAppendOnly)

Dim tdfLinked2 As TableDef

'Link to the Import tables in the temp MDB
Set tdfLinked2 = CurrentDb.CreateTableDef(strTableName2)
tdfLinked2.Connect = ";DATABASE=" & strTempDatabase
tdfLinked2.SourceTableName = strTableName2
CurrentDb.TableDefs.Append tdfLinked2

CurrentDb.TableDefs.Refresh

RefreshDatabaseWindow

'Set rs = CurrentDb.OpenRecordset(strTableName2, dbOpenDynaset,
dbAppendOnly)
Click to expand...
 
David said:
I found the problem - file was growing but overflow problem was because of a
math error, the loop was calculating bigger and bigger values through each
loop - it would overflow just after the last loop had values equal to
#.###^306. I fixed my math and loops worked fine. As I said my temp file
still grows but I didn't reach any limit after fix - I assume there is a
limit to file size Access can work with.
Click to expand...


Since around A2002, an mdb file is limited to 2GB. In A97
and earlier, it was 1GB. This kind of info can be found in
VBA Help - Specifications.
 
Back
Top