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

[devi]

Dear Steve & Paul,

First of all I'll say thank you for your support.

This is the code ....


for your info :

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.

adoEMP_Rst is a recordset that refer to table Employee that contain : Employee
Number, Name, 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.



Global adoDCTA_Rst As ADODB.Recordset
Global adoEMP_Rst As ADODB.Recordset

Public Sub OpenDCTA_Tbl()
Set adoDCTA_Rst = New ADODB.Recordset

adoDCTA_Rst.CursorLocation = adUseServer
adoDCTA_Rst.Open "DCTA", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SidoTA\DBData\TA.MDB;" & _
"User ID=; Password=;", _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
End Sub

Public Sub OpenEMP_Tbl()
Set adoEMP_Rst = New ADODB.Recordset

adoEMP_Rst.CursorLocation = adUseServer
adoEMP_Rst.Open "Employee", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\SidoTA\DBData\TA.MDB;" & _
"User ID=; Password=;", _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
End Sub

Public Sub CloseDCTA_Tbl()
adoDCTA_Rst.Close
End Sub

Public Sub CloseEMP_Tbl()
adoEMP_Rst.Close
End Sub


I open the tabel when the form is load and close it when the form is unload


Private Sub CheckShift() 'this routine will check the shift code from table
Employee

Dim sShiftCode As String

adoEMP_Rst.Index = "EmpNum"

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 ?
>

[Paul Clement]

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 ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[devi]

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 ???

Thank you
>Paul ~~~ pclement@ameritech.net
>Microsoft MVP (Visual Basic)

[Paul Clement]

On 3 Jul 2001 18:35:54 -0700, "devi" <devi@sidobangun.com> wrote:

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


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)