K
Kevin B
I'm having a problem with Access mdb files ballooning to some absurd size
after generating some records by collecting value from a form and writing
them back to a table with some VBA code.
The database has 4 modules, 1 form, 1 table with a max of 100 records. It
starts out around 330 kb in size, and before I know it the size of the db is
30 mb in size.
Running a compact and repair will reduce the size of the db to 20 mb's in
size. Even though there is no data in the lone data table.
After removing each element one at a time and doing a compact and repair
after each removal I've narrowed down the culprit to the lone form in the mdb
file. But I can't for the life of me figure out why. The form is unbound,
has 12 text boxes, one ActiveX spinner control and 2 command buttons. When I
remove the form the db gets back down to about 500 K, which is a heck of a
lot better than 22 mb's. this is the 3rd database this has happened to, and
before I call the help desk I would like to have as much information as I
possible can. I'll post the code below, although I'm fairly certain that
it's not the issue.
Has anyone encountered anything like this? Would a uninstall/reinstall of
MS Office be in order here. Any thoughts would help. Thanks...
Function GenerateJobs(frm As Form)
'=====================================================
'
' Purpose: Generate multiple copies of the cycle set up form
' incrementing the job name's number by 1 for each
' new record.
'
' Capture static data from entry form, use a UDF to
' capture the embedded numeric value in the job
' name so the number can be incremented and generate
' a 2-D array for each record.
'
' After capturing data to the array, open recordset
' and write records back to temp table for review.
'
' Input: Nothing
'
' Output: Nothing
'=====================================================
'Variable for the temp table
Dim rst As DAO.Recordset
'Form data, either default values or user entered
Dim strJobName As String, strRequestor_Name As String, _
dtmDate As Date, strJob_Frequency As String, _
strMaximum_Return_Code As String, _
strAbend_Resolution_Procedure As String, _
strPrimary_Contact As String, strPrimary_Phone As _
String, strSecondary_Contact As String, _
strSecondary_Phone As String, strPredecessors As _
String, strTriggers As String, _
strAdditional_Details As String
'IntIncrement is the value set by the spinner AX control
Dim intIncrement As Integer
'intSeedNumber is the number that was extracted from the
'initial report name
Dim intSeedNumber As Integer
'strprefix is the first character in of the job name and
'strsuffix is all the character to the right of the job
'number
Dim strPrefix As String, strSuffix As String
'Variant array for storing the records to be written
Dim varRecords() As Variant
'FOR loop counter variables
Dim i As Integer, intFld As Integer
On Error GoTo Err_GenerateJobs
'------------------------------------------------------------
' Capture job name, the number of forms to generate, using
' the UDF named ExtractNumbers extract the job number from
' the initial job name. Capture the first character of
' the job name as the prefix (the second character is
' always the first number of the job's sequential number),
' and all the characters to the right of the job number to
' the suffix variable. And while you at it grab the
' requestor name too.
'------------------------------------------------------------
strJobName = frm.txtJobKickOffValue.Value
intIncrement = frm.txtIncrement.Value
intSeedNumber = ExtractNumbers(strJobName)
strPrefix = Left$(strJobName, 1)
i = Len(intSeedNumber) + 1
strSuffix = Right$(strJobName, Len(strJobName) - i)
strRequestor_Name = Nz(frm.txtRequestor_Name.Value, "")
'------------------------------------------------------------
' If the date is MIA, substitute the system date
'------------------------------------------------------------
If IsDate(frm.txtRequest_Effective_Date.Value) Then
dtmDate = CDate(frm.txtRequest_Effective_Date.Value)
Else
dtmDate = Date
End If
'------------------------------------------------------------
' Capture the remaining string values on the form
'------------------------------------------------------------
strJob_Frequency = Nz(frm.txtJob_Frequency.Value, "")
strMaximum_Return_Code = Nz(frm. _
txtMaximum_Return_Code.Value, "")
strAbend_Resolution_Procedure = Nz(frm. _
txtAbend_Resolution_Procedure.Value, "")
strPrimary_Contact = Nz(frm.txtPrimary_Contact.Value, "")
strPrimary_Phone = Nz(frm.txtPrimary_Phone.Value, "")
strSecondary_Contact = Nz(frm.txtSecondary_Contact.Value, "")
strSecondary_Phone = Nz(frm.txtSecondary_Phone.Value, "")
strPredecessors = Nz(frm.txtPredecessors.Value, "")
strTriggers = Nz(frm.txtTriggers.Value, "")
strAdditional_Details = Nz(frm. _
txtAdditional_Details.Value, "")
'------------------------------------------------------------
' Redimension the 2-D array and then generate the record
' set
'------------------------------------------------------------
ReDim varRecords(intIncrement - 1, 12)
For i = 0 To intIncrement - 1
'Assign the data for each data record to its
'correpsonding array value
varRecords(i, 0) = strJobName
varRecords(i, 1) = strRequestor_Name
varRecords(i, 2) = dtmDate
varRecords(i, 3) = strJob_Frequency
varRecords(i, 4) = strMaximum_Return_Code
varRecords(i, 5) = strAbend_Resolution_Procedure
varRecords(i, 6) = strPrimary_Contact
varRecords(i, 7) = strPrimary_Phone
varRecords(i, 8) = strSecondary_Contact
varRecords(i, 9) = strSecondary_Phone
varRecords(i, 10) = strPredecessors
varRecords(i, 11) = strTriggers
varRecords(i, 12) = strAdditional_Details
'After producing the records, increment the seed
'number and produce the next job name.
intSeedNumber = intSeedNumber + 1
strJobName = strPrefix & intSeedNumber & strSuffix
'loop
Next i
'Open the target recordset
Set rst = CurrentDb().OpenRecordset(conTbl)
With rst
'Loop through each row, creating a correpsonding
'records
For i = 0 To intIncrement - 1
.AddNew
For intFld = 0 To 12
'Loop through each field value and populate
'the corresponding field in the recordset
.Fields(intFld).Value = varRecords(i, intFld)
Next intFld
'UPdate the record and loop
.Update
Next i
'After all records have been inserted, close the recordset
.Close
End With
'Clear certain fields, reset a field and disable a
'command button
With frm
.txtJobKickOffValue.Value = Null
.txtIncrement.Value = 0
.txtRequestor_Name.Value = Null
.txtRequestor_Name.SetFocus
.cmdGenerate.Enabled = False
End With
Exit_GenerateJobs:
'Release object variables and bail out
Set rst = Nothing
Exit Function
Err_GenerateJobs:
'Generic error trap routine w/exit
ErrTrap Err.Number, conMod, "GenerateJobs", True
Err.Clear
Resume Exit_GenerateJobs
End Function
after generating some records by collecting value from a form and writing
them back to a table with some VBA code.
The database has 4 modules, 1 form, 1 table with a max of 100 records. It
starts out around 330 kb in size, and before I know it the size of the db is
30 mb in size.
Running a compact and repair will reduce the size of the db to 20 mb's in
size. Even though there is no data in the lone data table.
After removing each element one at a time and doing a compact and repair
after each removal I've narrowed down the culprit to the lone form in the mdb
file. But I can't for the life of me figure out why. The form is unbound,
has 12 text boxes, one ActiveX spinner control and 2 command buttons. When I
remove the form the db gets back down to about 500 K, which is a heck of a
lot better than 22 mb's. this is the 3rd database this has happened to, and
before I call the help desk I would like to have as much information as I
possible can. I'll post the code below, although I'm fairly certain that
it's not the issue.
Has anyone encountered anything like this? Would a uninstall/reinstall of
MS Office be in order here. Any thoughts would help. Thanks...
Function GenerateJobs(frm As Form)
'=====================================================
'
' Purpose: Generate multiple copies of the cycle set up form
' incrementing the job name's number by 1 for each
' new record.
'
' Capture static data from entry form, use a UDF to
' capture the embedded numeric value in the job
' name so the number can be incremented and generate
' a 2-D array for each record.
'
' After capturing data to the array, open recordset
' and write records back to temp table for review.
'
' Input: Nothing
'
' Output: Nothing
'=====================================================
'Variable for the temp table
Dim rst As DAO.Recordset
'Form data, either default values or user entered
Dim strJobName As String, strRequestor_Name As String, _
dtmDate As Date, strJob_Frequency As String, _
strMaximum_Return_Code As String, _
strAbend_Resolution_Procedure As String, _
strPrimary_Contact As String, strPrimary_Phone As _
String, strSecondary_Contact As String, _
strSecondary_Phone As String, strPredecessors As _
String, strTriggers As String, _
strAdditional_Details As String
'IntIncrement is the value set by the spinner AX control
Dim intIncrement As Integer
'intSeedNumber is the number that was extracted from the
'initial report name
Dim intSeedNumber As Integer
'strprefix is the first character in of the job name and
'strsuffix is all the character to the right of the job
'number
Dim strPrefix As String, strSuffix As String
'Variant array for storing the records to be written
Dim varRecords() As Variant
'FOR loop counter variables
Dim i As Integer, intFld As Integer
On Error GoTo Err_GenerateJobs
'------------------------------------------------------------
' Capture job name, the number of forms to generate, using
' the UDF named ExtractNumbers extract the job number from
' the initial job name. Capture the first character of
' the job name as the prefix (the second character is
' always the first number of the job's sequential number),
' and all the characters to the right of the job number to
' the suffix variable. And while you at it grab the
' requestor name too.
'------------------------------------------------------------
strJobName = frm.txtJobKickOffValue.Value
intIncrement = frm.txtIncrement.Value
intSeedNumber = ExtractNumbers(strJobName)
strPrefix = Left$(strJobName, 1)
i = Len(intSeedNumber) + 1
strSuffix = Right$(strJobName, Len(strJobName) - i)
strRequestor_Name = Nz(frm.txtRequestor_Name.Value, "")
'------------------------------------------------------------
' If the date is MIA, substitute the system date
'------------------------------------------------------------
If IsDate(frm.txtRequest_Effective_Date.Value) Then
dtmDate = CDate(frm.txtRequest_Effective_Date.Value)
Else
dtmDate = Date
End If
'------------------------------------------------------------
' Capture the remaining string values on the form
'------------------------------------------------------------
strJob_Frequency = Nz(frm.txtJob_Frequency.Value, "")
strMaximum_Return_Code = Nz(frm. _
txtMaximum_Return_Code.Value, "")
strAbend_Resolution_Procedure = Nz(frm. _
txtAbend_Resolution_Procedure.Value, "")
strPrimary_Contact = Nz(frm.txtPrimary_Contact.Value, "")
strPrimary_Phone = Nz(frm.txtPrimary_Phone.Value, "")
strSecondary_Contact = Nz(frm.txtSecondary_Contact.Value, "")
strSecondary_Phone = Nz(frm.txtSecondary_Phone.Value, "")
strPredecessors = Nz(frm.txtPredecessors.Value, "")
strTriggers = Nz(frm.txtTriggers.Value, "")
strAdditional_Details = Nz(frm. _
txtAdditional_Details.Value, "")
'------------------------------------------------------------
' Redimension the 2-D array and then generate the record
' set
'------------------------------------------------------------
ReDim varRecords(intIncrement - 1, 12)
For i = 0 To intIncrement - 1
'Assign the data for each data record to its
'correpsonding array value
varRecords(i, 0) = strJobName
varRecords(i, 1) = strRequestor_Name
varRecords(i, 2) = dtmDate
varRecords(i, 3) = strJob_Frequency
varRecords(i, 4) = strMaximum_Return_Code
varRecords(i, 5) = strAbend_Resolution_Procedure
varRecords(i, 6) = strPrimary_Contact
varRecords(i, 7) = strPrimary_Phone
varRecords(i, 8) = strSecondary_Contact
varRecords(i, 9) = strSecondary_Phone
varRecords(i, 10) = strPredecessors
varRecords(i, 11) = strTriggers
varRecords(i, 12) = strAdditional_Details
'After producing the records, increment the seed
'number and produce the next job name.
intSeedNumber = intSeedNumber + 1
strJobName = strPrefix & intSeedNumber & strSuffix
'loop
Next i
'Open the target recordset
Set rst = CurrentDb().OpenRecordset(conTbl)
With rst
'Loop through each row, creating a correpsonding
'records
For i = 0 To intIncrement - 1
.AddNew
For intFld = 0 To 12
'Loop through each field value and populate
'the corresponding field in the recordset
.Fields(intFld).Value = varRecords(i, intFld)
Next intFld
'UPdate the record and loop
.Update
Next i
'After all records have been inserted, close the recordset
.Close
End With
'Clear certain fields, reset a field and disable a
'command button
With frm
.txtJobKickOffValue.Value = Null
.txtIncrement.Value = 0
.txtRequestor_Name.Value = Null
.txtRequestor_Name.SetFocus
.cmdGenerate.Enabled = False
End With
Exit_GenerateJobs:
'Release object variables and bail out
Set rst = Nothing
Exit Function
Err_GenerateJobs:
'Generic error trap routine w/exit
ErrTrap Err.Number, conMod, "GenerateJobs", True
Err.Clear
Resume Exit_GenerateJobs
End Function