adoDCTA_Rst is a recordset that refer to table DCTA (Data Collection Time
& Attendace), this table it contain the transaction from time and attendance
machine that record the : Employee Number, Date & Time transaction, Transaction
Type, Shift code etc.

routine CheckShift will retrieve the DCTA data per record (with some condition
i.e status = "U" for unposted etc...) and do checking at the Employee table
whether the shift code is in the system or not, if yes they will update the
shiftcode field at DCTA with the employee shift code if not ... shift code
= "?".

This routine sound very simple but I don't understand why access need a lot
of space since I didn't add any new field ???
and one thing it take at least 9-10 Mb to do this.....
My program will have a lot of checking and yesterday I do 30% of all my routine/procedure
and that 35 Mb for sample data only.

If adoDCTA_Rst.RecordCount > 0 Then
adoDCTA_Rst.MoveFirst
Do While Not adoDCTA_Rst.EOF
If adoDCTA_Rst!Status = "U" And (adoDCTA_Rst!Shift = "?" Or IsNull(adoDCTA_Rst!Shift))
Then
adoEMP_Rst.Seek (adoDCTA_Rst!EmpNum)
If Not (adoEMP_Rst.EOF Or adoEMP_Rst.BOF) Then
sShiftCode = adoEMP_Rst!Shift
adoDCTA_Rst!Shift = sShiftCode
adoDCTA_Rst.Update
Else
adoDCTA_Rst!Shift = "?"
adoDCTA_Rst!Status = "E"
adoDCTA_Rst!ErrCode = "ENS" 'Employee not in the system
adoDCTA_Rst.Update
End If
End If
adoDCTA_Rst.MoveNext
Loop
End If

End Sub

"Steve Abel" <steve.abel@zoom.co.uk> wrote:
>
>Can't really help without seeing the code. Please post or e-mail it and
I
>will have a look for you!
>
>Steve
>
>"devi" <devi@sidobangun.com> wrote:
>>
>>Dear all,
>>
>>I make a program to check a record from several table without adding a
new
>>record, but when I check the size of my database (.mdb) it increase directly.
>>The original data is 880Kb become 13 Mb and it makes my accessing become
>>slower.
>>Anybody can help me ?
>

07-02-2001, 02:29 PM

Paul Clement

Re: db size in access increase (the source code)

On 28 Jun 2001 19:00:48 -0700, "devi" <devi@sidobangun.com> wrote:

¤
¤ Dear Steve & Paul,
¤
¤ First of all I'll say thank you for your support.
¤
¤ This is the code ....
¤

I don't see anything out of the ordinary in your code. I think that it's just the amount of
temporary data that is being stored over time that is contributing to the size of the database. This
is normal behavior for Access.

Just do the periodic maintenance of compacting the database to keep it at a manageable size.

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>On 28 Jun 2001 19:00:48 -0700, "devi" <devi@sidobangun.com> wrote:
>
>¤
>¤ Dear Steve & Paul,
>¤
>¤ First of all I'll say thank you for your support.
>¤
>¤ This is the code ....
>¤
>
>I don't see anything out of the ordinary in your code. I think that it's
just the amount
>of
>temporary data that is being stored over time that is contributing to the
size of the
>database. This
>is normal behavior for Access.
>
>Just do the periodic maintenance of compacting the database to keep it at
a manageable
>size.
>
>

Thank you for the information, I will try to do periodic maintenance (compacting
& repairing).
One question, is there any source to explain how to do compacting by programming
in VB ???

¤
¤ Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
¤ >On 28 Jun 2001 19:00:48 -0700, "devi" <devi@sidobangun.com> wrote:
¤ >
¤ >¤
¤ >¤ Dear Steve & Paul,
¤ >¤
¤ >¤ First of all I'll say thank you for your support.
¤ >¤
¤ >¤ This is the code ....
¤ >¤
¤ >
¤ >I don't see anything out of the ordinary in your code. I think that it's
¤ just the amount
¤ >of
¤ >temporary data that is being stored over time that is contributing to the
¤ size of the
¤ >database. This
¤ >is normal behavior for Access.
¤ >
¤ >Just do the periodic maintenance of compacting the database to keep it at
¤ a manageable
¤ >size.
¤ >
¤ >
¤
¤ Thank you for the information, I will try to do periodic maintenance (compacting
¤ & repairing).
¤ One question, is there any source to explain how to do compacting by programming
¤ in VB ???
¤

The CompactDatabase function is supported by DAO or JRO (if you are using ADO instead of DAO). The
only issue is making certain your database is closed prior to using the function.