System resource exceeded

[David G.]

Trying to execute the following code to update a back end database.
Everything runs fine until line:

db.Execute "ALTER TABLE tblMainData " _
& "ALTER COLUMN tblMainDataProdCode TEXT(20)",
dbFailOnError

Then I get error 3035 System Resource exceeded.

Here is the entire sub:
Public Sub sInstallUpdate()
On Error GoTo sInstallUpdate_Error
Dim db As DAO.Database 'backend database
Dim db2 As DAO.Database 'front end
Dim rs As DAO.Recordset
Dim lngYear As Long
Dim strBEPath As String

MOD1: 'Update 03/2011
'test for installed changes
strBEPath = fFindRemoteConnection(Forms("frmSplash"))
strBEPath = Right(strBEPath, Len(strBEPath) - 10)
Set db = DBEngine.Workspaces(0).OpenDatabase(strBEPath)
Set rs = db.OpenRecordset("SELECT * FROM tblTest")
rs.MoveFirst
rs.FindFirst "tblTestName = " & """Mapping 2"""
If rs.NoMatch = True Then 'update not installed
rs.Close
'change 2 Change Product Code field length
db.Execute "ALTER TABLE tblMainData " _
& "ALTER COLUMN tblMainDataProdCode TEXT(20)",
dbFailOnError
'Add SDA Plate to tblPlate
db.Execute "INSERT INTO tblPlate(tblPlateName) VALUES
('SDA')", dbFailOnError
'add Map2 to tblTestType
db.Execute "INSERT INTO tblTestType
(tblTestTypeName,tblTestTypeDescription,tblTestTypeObsolete) " & _
"VALUES ('Map2','Mapping for 2011')", dbFailOnError
'Change 1 Add Map2 Test to tblTest
db.Execute "INSERT INTO tblTest
(tblTestID,tblTestName,tblTestDescription,tblTestTypeID," & _

"tblTestDays1,tblTestDays2,tblTestTVCdil,tblTestSSCdil,tblTestObsolete,"
& _

"tblTestPlate1,tblTestPlate2,tblTestPlate3,tblTestPlate4,tblTestPlate5,"
& _
"tblTestPlate6,tblReportNoteID) VALUES (39,'Mapping
2','Map 2','Map2',2,5,10,0,'False'," & _
"'SMA','SDA',0,0,0,0,0)", dbFailOnError
'Change 3 add years to tblYear
Set db2 = CurrentDb
Set rs = db2.OpenRecordset("Select * FROM tblYear")
With rs
.MoveFirst
.MoveLast
lngYear = rs![tblYear]
While lngYear < 2020
lngYear = lngYear + 1
db2.Execute "INSERT INTO tblYear (tblYear) VALUES
(" & lngYear & ")", dbFailOnError
Wend
End With
'
Else:
GoTo MOD2 'already installed goto to next mod
End If

MOD2:
'TBD

GoTo sInstallUpdate_Exit

sInstallUpdate_Error:
MsgBox "The Following Error Occurred :" & Err.Description &
Err.Number, vbCritical, "Update Installation Information"

sInstallUpdate_Exit:
Set db = Nothing
Set rs = Nothing
Set db2 = Nothing
End Sub

Thanks in advance for any help.
David G.
THANKS!
David G.

 

[DavidG]

Trying to execute the following code to update a back end database.
Everything runs fine until line:

db.Execute "ALTER TABLE tblMainData " _
                & "ALTER COLUMN tblMainDataProdCode TEXT(20)",
dbFailOnError

Then I get error 3035 System Resource exceeded.

Here is the entire sub:
Public Sub sInstallUpdate()
    On Error GoTo sInstallUpdate_Error
    Dim db As DAO.Database  'backend database
    Dim db2 As DAO.Database     'front end
    Dim rs As DAO.Recordset
    Dim lngYear As Long
    Dim strBEPath As String

MOD1:    'Update 03/2011
    'test for installed changes
    strBEPath = fFindRemoteConnection(Forms("frmSplash"))
    strBEPath = Right(strBEPath, Len(strBEPath) - 10)
    Set db = DBEngine.Workspaces(0).OpenDatabase(strBEPath)
    Set rs = db.OpenRecordset("SELECT * FROM tblTest")
        rs.MoveFirst
        rs.FindFirst "tblTestName = " & """Mapping 2"""
        If rs.NoMatch = True Then 'update not installed
            rs.Close
                    'change 2 Change Product Code field length
            db.Execute "ALTER TABLE tblMainData " _
                & "ALTER COLUMN tblMainDataProdCode TEXT(20)",
dbFailOnError
                    'Add SDA Plate to tblPlate
            db.Execute "INSERT INTO tblPlate(tblPlateName) VALUES
('SDA')", dbFailOnError
                    'add Map2 to tblTestType
            db.Execute "INSERT INTO tblTestType
(tblTestTypeName,tblTestTypeDescription,tblTestTypeObsolete) " & _
                "VALUES ('Map2','Mapping for 2011')", dbFailOnError
                    'Change 1 Add Map2 Test to tblTest
            db.Execute "INSERT INTO tblTest
(tblTestID,tblTestName,tblTestDescription,tblTestTypeID," & _

"tblTestDays1,tblTestDays2,tblTestTVCdil,tblTestSSCdil,tblTestObsolete,"
& _

"tblTestPlate1,tblTestPlate2,tblTestPlate3,tblTestPlate4,tblTestPlate5,"
& _
                "tblTestPlate6,tblReportNoteID) VALUES (39,'Mapping
2','Map 2','Map2',2,5,10,0,'False'," & _
                "'SMA','SDA',0,0,0,0,0)", dbFailOnError
                    'Change 3 add years to tblYear
            Set db2 = CurrentDb
            Set rs = db2.OpenRecordset("Select * FROM tblYear")
            With rs
                .MoveFirst
                .MoveLast
                lngYear = rs![tblYear]
                While lngYear < 2020
                    lngYear = lngYear + 1
                    db2.Execute "INSERT INTO tblYear (tblYear) VALUES
(" & lngYear & ")", dbFailOnError
                Wend
            End With
                    '
        Else:
            GoTo MOD2    'already installed goto to next mod
        End If

MOD2:
    'TBD

    GoTo sInstallUpdate_Exit

sInstallUpdate_Error:
    MsgBox "The Following Error Occurred :" & Err.Description &
Err.Number, vbCritical, "Update Installation Information"

sInstallUpdate_Exit:
    Set db = Nothing
    Set rs = Nothing
    Set db2 = Nothing
End Sub

Thanks in advance for any help.
David G.
THANKS!
David G.

Sorry fro the triple post. My news reader said the posts were not
sent.
David G.

 

[John W. Vinson]

Trying to execute the following code to update a back end database.
Everything runs fine until line:

db.Execute "ALTER TABLE tblMainData " _
& "ALTER COLUMN tblMainDataProdCode TEXT(20)",
dbFailOnError

Then I get error 3035 System Resource exceeded.

Alter Table queries can fail because the way Access does them involves having
two complete copies of the table in memory at the same time. Try an
alternative approach: create a new, empty table with the desired field sizes
(and field types, if you're changing datatype), and run an Append query to
migrate your data into the new table. It's a hassle, since you will need to
drop relationships to the old table, reestablish them to the new, rename the
table, etc. but it does get around this problem.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com