System resource exceeded

  • Thread starter Thread starter DavidG
  • Start date Start date
D

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.
 
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.
Click to expand...

For posterity, I found the problem and the solution.
Access wouldn't even let me update the back end directly because of
the number of records. I needed to increase the number of record
locks.

Here is site where I got my info from:
http://bytes.com/topic/access/answers/883771-vba-change-maximum-number-locks-allowed-recordset

Next, I had some data type conflicts. (Although a FK column will allow
you type in a name using text, updating the table requires you know
and enter the FK.

I added code to lookup the PK's for the new fields, then inserted
their values into any associated new records.

The following code ran without a hitch:
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
Dim intPlateKey As Integer
Dim intTestTypeKey As Integer
Dim intTestKey As Integer

MOD1: 'Update 03/2011
'test for installed changes
strBEPath = fFindRemoteConnection(Forms("frmSplash"))
strBEPath = Right(strBEPath, Len(strBEPath) - 10)
DAO.DBEngine.SetOption dbMaxLocksPerFile, 200000
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
'get key value for new plate
Set rs = db.OpenRecordset("SELECT * FROM tblPlate WHERE
(((tblPlate.[tblPlateName])='SDA'));")
intPlateKey = rs![tblPlateID]
Set rs = Nothing
db.Execute "INSERT INTO tblTestType
(tblTestTypeName,tblTestTypeDescription,tblTestTypeObsolete) " & _
"VALUES ('Map2','Mapping for 2011',0);", dbFailOnError
Set rs = db.OpenRecordset("SELECT * FROM tblTestType WHERE
(((tblTestType.tblTestTypeName)='Map2'));")
intTestTypeKey = rs![tblTestTypeID]
Set rs = Nothing
'Change 1 Add Map2 Test to tblTest
db.Execute "INSERT INTO tblTest
(tblTestName,tblTestDescription,tblTestTypeID," & _

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

"tblTestPlate1,tblTestPlate2,tblTestPlate3,tblTestPlate4,tblTestPlate5,"
& _
"tblTestPlate6,tblReportNoteID) VALUES ('Mapping
2','Map 2'," & intTestTypeKey & ",2,5,10,0,0," & _
"7," & intPlateKey & ",0,0,0,0,0);", dbFailOnError
' get new primary key number for new test and update
formname
Set rs = db.OpenRecordset("SELECT * FROM tblTest WHERE
(((tblTest.[tblTestName])='Mapping 2'));")
intTestKey = rs![tblTestID]
Set rs = Nothing
DoCmd.Rename "sfrmTestType" & CStr(intTestKey), acForm,
"sfrmTestType39"

'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!
David G.
 
Back
Top