How to Compact a "Table"

[Prakash]

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]

Prakash wrote:

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.

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

[alfred]

"Prakash" <si****@omantel.net.om> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

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.

try the following code . It will compact the database

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.TableDefs("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(Message, 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.CompactDatabase FileP & PrName & ".mdb", FileP & PrName &
"Temp.mdb", , , PWD=yourBackendPassword"

' 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: su*****@jam.co.za"
Resume Exit_ErrJam
End If

[Tom van Stiphout]

On Mon, 25 Jul 2005 22:48:38 +0200, "alfred"
<al***********@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.

"Prakash" <si****@omantel.net.om> wrote in message
news:11**********************@g44g2000cwa.googleg roups.com...

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.

try the following code . It will compact the database

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.TableDefs("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(Message, 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.CompactDatabase FileP & PrName & ".mdb", FileP & PrName &
"Temp.mdb", , , PWD=yourBackendPassword"

' 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: su*****@jam.co.za"
Resume Exit_ErrJam
End If

[Prakash]

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.

[Larry Linson]

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" <si****@omantel.net.om> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

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.