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 ?

>