How to Compact a "Table"

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Prakash

    #1

    How to Compact a "Table"

    Does anyone know how to compact a table (only) ?

    I'm using some code o move rows up & down in a continuous form & it
    works fine, but after some usage the rows refuse to move up or down
    until I compact the database. Then with no modifications to the code it
    works well.

    Any ideas anyone ?

    Thx & Best Rgds,
    Prakash.

  • Bob Alston

    #2
    Re: How to Compact a "Table&quo t;

    Prakash wrote:[color=blue]
    > Does anyone know how to compact a table (only) ?
    >
    > I'm using some code o move rows up & down in a continuous form & it
    > works fine, but after some usage the rows refuse to move up or down
    > until I compact the database. Then with no modifications to the code it
    > works well.
    >
    > Any ideas anyone ?
    >
    > Thx & Best Rgds,
    > Prakash.
    >[/color]
    You cannot compact a table. It is an object in the database. You can
    only compact the entire database.

    Explain some more about what you are trying to accomplish (not what your
    technique is) and maybe someone can find a better way to help you do it.

    Bob

    Comment

    • alfred

      #3
      Re: How to Compact a "Table&quo t;


      "Prakash" <simran@omantel .net.om> wrote in message
      news:1122301867 .104951.154050@ g44g2000cwa.goo glegroups.com.. .[color=blue]
      > Does anyone know how to compact a table (only) ?
      >
      > I'm using some code o move rows up & down in a continuous form & it
      > works fine, but after some usage the rows refuse to move up or down
      > until I compact the database. Then with no modifications to the code it
      > works well.
      >
      > Any ideas anyone ?
      >
      > Thx & Best Rgds,
      > Prakash.[color=green]
      >>try the following code . It will compact the database[/color][/color]

      Dim File_path As String
      Dim Message As String
      Dim title As String
      Dim Default As String
      Dim aaGetalUsers As Integer

      ' make sure that all forms are closed !!!!!!
      ' make sure that all users are logged out
      On Error GoTo ErrJam


      Dim ConnectStr As String
      ConnectStr = CurrentDb.Table Defs("DbMaster1 ").Connect
      ConnectStr = Mid([ConnectStr], InStr([ConnectStr], "database=" ) + 9)


      Message = "Specify the Path and Database to compact. " ' Set prompt.
      title = "Compacting of backend database " ' Set title.
      Default = ConnectStr

      File_path = InputBox(Messag e, title, Default)

      Dim cNom1, cNom2, cNom3, cNom4, cNom5, cNom6, cNom7, cNomTotal, cNomDot As
      Integer
      Dim FileP, PrName As String
      cNom1 = 0: cNom2 = 0: cNom3 = 0: cNom4 = 0: cNom5 = 0:
      cNom6 = 0: cNom7 = 0
      cNom1 = InStr(1, File_path, "\") ' 'find location of first '-'
      If cNom1 <> 0 Then cNom2 = InStr(cNom1 + 1, File_path, "\") ' find the
      second '-'
      If cNom2 <> 0 Then cNom3 = InStr(cNom2 + 1, File_path, "\") ' find the
      second '-'
      If cNom3 <> 0 Then cNom4 = InStr(cNom3 + 1, File_path, "\") ' find the
      second '-'
      If cNom4 <> 0 Then cNom5 = InStr(cNom4 + 1, File_path, "\")
      If cNom5 <> 0 Then cNom6 = InStr(cNom5 + 1, File_path, "\")
      If cNom6 <> 0 Then cNom7 = InStr(cNom6 + 1, File_path, "\")

      If cNom1 <> 0 Then cNomTotal = cNom1
      If cNom2 <> 0 Then cNomTotal = cNom2
      If cNom3 <> 0 Then cNomTotal = cNom3
      If cNom4 <> 0 Then cNomTotal = cNom4
      If cNom5 <> 0 Then cNomTotal = cNom5
      If cNom6 <> 0 Then cNomTotal = cNom6
      If cNom7 <> 0 Then cNomTotal = cNom7

      cNomDot = InStr(1, File_path, ".")

      FileP = Left$(File_path , cNomTotal)
      ' MsgBox FileP
      PrName = Mid(File_path, cNomTotal + 1, cNomDot - cNomTotal - 1)
      ' MsgBox PrName



      ' Delete the previous backup file if it exists.
      If Dir(FileP & PrName & ".bak") <> vbNullString Then
      Kill FileP & PrName & ".bak"
      End If



      ' Compact the database to a temp file.

      DBEngine.Compac tDatabase FileP & PrName & ".mdb", FileP & PrName &
      "Temp.mdb", , , PWD=yourBackend Password"

      ' Rename the current database as backup and rename the temp file to
      ' the original file name.
      Name FileP & PrName & ".mdb" As FileP & PrName & ".bak"
      Name FileP & PrName & "Temp.mdb" As FileP & PrName & ".mdb"



      Exit_ErrJam:
      Exit Sub

      ErrJam:
      If Err.Number = 3044 Then
      MsgBox " Not a valid path. Make sure the path name is spelled
      correctly and that you are connected to the server on which the file
      reside."
      Resume Exit_ErrJam
      ElseIf Err.Number = 94 Then
      Resume Exit_ErrJam
      ElseIf Err.Number = 3356 Then
      MsgBox " Someone is still logged in. can't continue!"
      Resume Exit_ErrJam
      Else
      MsgBox "Error in RepairForm. " & _
      "Its number and description are " & _
      Err.Number & ": " & Err.Description , vbCritical, _
      "JAMTech email: support@jam.co. za"
      Resume Exit_ErrJam
      End If


      Comment

      • Tom van Stiphout

        #4
        Re: How to Compact a &quot;Table&quo t;

        On Mon, 25 Jul 2005 22:48:38 +0200, "alfred"
        <alfredminnaar@ hotmail.com> wrote:

        Did you write this code before you found out you can scan a string
        from the end?
        Something like:
        for i=len(strPath) to 1 step -1
        if mid$(strPath,i, 1) = "\" then
        'etc.

        Of course you should also handle the case where there is no \ in the
        path. or the user hit Cancel.

        But the larger issue is that having to compact is entirely
        unreasonable, and we would want to figure out why this is, rather than
        to offer a compact solution.

        -Tom.

        [color=blue]
        >
        >"Prakash" <simran@omantel .net.om> wrote in message
        >news:112230186 7.104951.154050 @g44g2000cwa.go oglegroups.com. ..[color=green]
        >> Does anyone know how to compact a table (only) ?
        >>
        >> I'm using some code o move rows up & down in a continuous form & it
        >> works fine, but after some usage the rows refuse to move up or down
        >> until I compact the database. Then with no modifications to the code it
        >> works well.
        >>
        >> Any ideas anyone ?
        >>
        >> Thx & Best Rgds,
        >> Prakash.[color=darkred]
        >>>try the following code . It will compact the database[/color][/color]
        >
        > Dim File_path As String
        > Dim Message As String
        > Dim title As String
        > Dim Default As String
        > Dim aaGetalUsers As Integer
        >
        > ' make sure that all forms are closed !!!!!!
        >' make sure that all users are logged out
        > On Error GoTo ErrJam
        >
        >
        > Dim ConnectStr As String
        > ConnectStr = CurrentDb.Table Defs("DbMaster1 ").Connect
        > ConnectStr = Mid([ConnectStr], InStr([ConnectStr], "database=" ) + 9)
        >
        >
        > Message = "Specify the Path and Database to compact. " ' Set prompt.
        > title = "Compacting of backend database " ' Set title.
        > Default = ConnectStr
        >
        > File_path = InputBox(Messag e, title, Default)
        >
        > Dim cNom1, cNom2, cNom3, cNom4, cNom5, cNom6, cNom7, cNomTotal, cNomDot As
        >Integer
        > Dim FileP, PrName As String
        > cNom1 = 0: cNom2 = 0: cNom3 = 0: cNom4 = 0: cNom5 = 0:
        > cNom6 = 0: cNom7 = 0
        > cNom1 = InStr(1, File_path, "\") ' 'find location of first '-'
        > If cNom1 <> 0 Then cNom2 = InStr(cNom1 + 1, File_path, "\") ' find the
        >second '-'
        > If cNom2 <> 0 Then cNom3 = InStr(cNom2 + 1, File_path, "\") ' find the
        >second '-'
        > If cNom3 <> 0 Then cNom4 = InStr(cNom3 + 1, File_path, "\") ' find the
        >second '-'
        > If cNom4 <> 0 Then cNom5 = InStr(cNom4 + 1, File_path, "\")
        > If cNom5 <> 0 Then cNom6 = InStr(cNom5 + 1, File_path, "\")
        > If cNom6 <> 0 Then cNom7 = InStr(cNom6 + 1, File_path, "\")
        >
        > If cNom1 <> 0 Then cNomTotal = cNom1
        > If cNom2 <> 0 Then cNomTotal = cNom2
        > If cNom3 <> 0 Then cNomTotal = cNom3
        > If cNom4 <> 0 Then cNomTotal = cNom4
        > If cNom5 <> 0 Then cNomTotal = cNom5
        > If cNom6 <> 0 Then cNomTotal = cNom6
        > If cNom7 <> 0 Then cNomTotal = cNom7
        >
        > cNomDot = InStr(1, File_path, ".")
        >
        > FileP = Left$(File_path , cNomTotal)
        >' MsgBox FileP
        > PrName = Mid(File_path, cNomTotal + 1, cNomDot - cNomTotal - 1)
        >' MsgBox PrName
        >
        >
        >
        > ' Delete the previous backup file if it exists.
        > If Dir(FileP & PrName & ".bak") <> vbNullString Then
        > Kill FileP & PrName & ".bak"
        > End If
        >
        >
        >
        > ' Compact the database to a temp file.
        >
        > DBEngine.Compac tDatabase FileP & PrName & ".mdb", FileP & PrName &
        >"Temp.mdb", , , PWD=yourBackend Password"
        >
        > ' Rename the current database as backup and rename the temp file to
        > ' the original file name.
        > Name FileP & PrName & ".mdb" As FileP & PrName & ".bak"
        > Name FileP & PrName & "Temp.mdb" As FileP & PrName & ".mdb"
        >
        >
        >
        >Exit_ErrJam:
        > Exit Sub
        >
        >ErrJam:
        > If Err.Number = 3044 Then
        > MsgBox " Not a valid path. Make sure the path name is spelled
        >correctly and that you are connected to the server on which the file
        >reside."
        > Resume Exit_ErrJam
        > ElseIf Err.Number = 94 Then
        > Resume Exit_ErrJam
        > ElseIf Err.Number = 3356 Then
        > MsgBox " Someone is still logged in. can't continue!"
        > Resume Exit_ErrJam
        > Else
        > MsgBox "Error in RepairForm. " & _
        > "Its number and description are " & _
        > Err.Number & ": " & Err.Description , vbCritical, _
        > "JAMTech email: support@jam.co. za"
        > Resume Exit_ErrJam
        > End If
        >[/color]

        Comment

        • Prakash

          #5
          Re: How to Compact a &quot;Table&quo t;

          thx alfred & thx tom too. I'll post my code here in a couple of days
          after a bit more studying & with some explanations as to the flow.

          I've been using the back end database for nigh on 6 months without a
          compact & no probs & now this small crazy bit.

          I'll get back with the code asap.

          Thx & Best Rgds,
          Prakash.

          Comment

          • Larry Linson

            #6
            Re: How to Compact a &quot;Table&quo t;

            It will be interesting to see how much it has grown over several months
            without any compacting. I'd infer from your description that there is
            considerable add-delete activity if you are moving records in a form...

            Larry Linson
            Microsoft Access MVP

            "Prakash" <simran@omantel .net.om> wrote in message
            news:1122348064 .827260.197240@ o13g2000cwo.goo glegroups.com.. .[color=blue]
            > thx alfred & thx tom too. I'll post my code here in a couple of days
            > after a bit more studying & with some explanations as to the flow.
            >
            > I've been using the back end database for nigh on 6 months without a
            > compact & no probs & now this small crazy bit.
            >
            > I'll get back with the code asap.
            >
            > Thx & Best Rgds,
            > Prakash.
            >[/color]


            Comment

            Working...