Over flow error when runing a loop

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

David McKnight

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

Marshall Barton

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type mismatch error after converting to Access 2007 2
Having trouble with temp table 1
Having trouble with temp table 2
temp db 1
Linked Tables - How to Close External Database? 11
Zero length field 1
Assigning Permissions to Temporary Database Tables 8

Top