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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

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

Hybrid View

  1. #1
    devi Guest

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


    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 ?

    >



  2. #2
    Paul Clement Guest

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

  3. #3
    devi Guest

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


    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)



  4. #4
    Paul Clement Guest

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

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center