Compessing an Access database

Hi:

I have read a number of threads on the issue of compressing (compacting) Access data bases
programmatically, and have been left confused. We are using Access 2000, and I need code
that will programmatically compress an open database. (called "InvoiceManagement.be).

Can someone give me a piece of code that will do this, or a pointer to one, please?

Thanks

John Baker

"John Baker" <johnhbaker@comcast.net> wrote in message
news:cvid12doc8ulkcs37hbtmege113as2neqt@4ax.com...[color=blue]
> Hi:
>
> I have read a number of threads on the issue of compressing (compacting)
> Access data bases
> programmatically, and have been left confused. We are using Access 2000,
> and I need code
> that will programmatically compress an open database. (called
> "InvoiceManagement.be).
>
> Can someone give me a piece of code that will do this, or a pointer to
> one, please?
>
> Thanks
>
> John Baker[/color]

You should not be trying to compact a database which others might be using.

I don't believe there is one standard solution to this because it depends on
a number of factors:
Is the database split front/back end?
Is there user-level security?
When should this action happen - by user click or scheduled automatically?

Your code should
establish that no-one is using the database to be compacted ('original.mdb')
rename it to 'temp.mdb' (makes sure no-one is using it, or will be able to
during compact routine)
take a copy called 'copy1.mdb'
compact this to a new database 'copy2.mdb'
if all goes well, rename 'copy2.mdb' 'original.mdb'
delete 'copy1.mdb' and 'temp.mdb'

Anthony:

I am a bit of a neophyte with this aspect of access.

The db is single user right now, so no problem there. However, I am not certain of the
code to use to accomplish what I want.

I stumbled on this code in the Access Web site:

Public Sub CompactDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Sub

I have installed it, and can run it from a button called directlt, but would like to
combine it with some other actions, and don't appear to be able to set up as "code" call
in a macro that will execute it.

The actual code does the job when i execute it through the debugger, but it wont execute
when I try and set it up using a macro. I would like to make it into an nice easy button
press to remove a lot of excess data (old historical data that is unnecessary) and
compress the files, but if the code wont execute in a macro this is hard to accomplish,
since Im not skilled in Basic code.

Best

John

"Anthony England" <aengland@oops.co.uk> wrote:
[color=blue]
>"John Baker" <johnhbaker@comcast.net> wrote in message
>news:cvid12doc8ulkcs37hbtmege113as2neqt@4ax.com.. .[color=green]
>> Hi:
>>
>> I have read a number of threads on the issue of compressing (compacting)
>> Access data bases
>> programmatically, and have been left confused. We are using Access 2000,
>> and I need code
>> that will programmatically compress an open database. (called
>> "InvoiceManagement.be).
>>
>> Can someone give me a piece of code that will do this, or a pointer to
>> one, please?
>>
>> Thanks
>>
>> John Baker[/color]
>
>
>You should not be trying to compact a database which others might be using.
>
>I don't believe there is one standard solution to this because it depends on
>a number of factors:
>Is the database split front/back end?
>Is there user-level security?
>When should this action happen - by user click or scheduled automatically?
>
>Your code should
>establish that no-one is using the database to be compacted ('original.mdb')
>rename it to 'temp.mdb' (makes sure no-one is using it, or will be able to
>during compact routine)
>take a copy called 'copy1.mdb'
>compact this to a new database 'copy2.mdb'
>if all goes well, rename 'copy2.mdb' 'original.mdb'
>delete 'copy1.mdb' and 'temp.mdb'
>[/color]

"John Baker" <johnhbaker@comcast.net> wrote in message
news:u0ld12hb02p424ep8d758rf1rlsvg5bi04@4ax.com...[color=blue]
> Anthony:
>
> I am a bit of a neophyte with this aspect of access.
>
> The db is single user right now, so no problem there. However, I am not
> certain of the
> code to use to accomplish what I want.
>
> I stumbled on this code in the Access Web site:
>
> Public Sub CompactDB()
>
> CommandBars("Menu Bar"). _
> Controls("Tools"). _
> Controls("Database utilities"). _
> Controls("Compact and repair database..."). _
> accDoDefaultAction
>
> End Sub
>
> I have installed it, and can run it from a button called directlt, but
> would like to
> combine it with some other actions, and don't appear to be able to set up
> as "code" call
> in a macro that will execute it.
>
> The actual code does the job when i execute it through the debugger, but
> it wont execute
> when I try and set it up using a macro. I would like to make it into an
> nice easy button
> press to remove a lot of excess data (old historical data that is
> unnecessary) and
> compress the files, but if the code wont execute in a macro this is hard
> to accomplish,
> since Im not skilled in Basic code.
>
> Best
>
> John
>
> "Anthony England" <aengland@oops.co.uk> wrote:
>[color=green]
>>"John Baker" <johnhbaker@comcast.net> wrote in message
>>news:cvid12doc8ulkcs37hbtmege113as2neqt@4ax.com. ..[color=darkred]
>>> Hi:
>>>
>>> I have read a number of threads on the issue of compressing (compacting)
>>> Access data bases
>>> programmatically, and have been left confused. We are using Access 2000,
>>> and I need code
>>> that will programmatically compress an open database. (called
>>> "InvoiceManagement.be).
>>>
>>> Can someone give me a piece of code that will do this, or a pointer to
>>> one, please?
>>>
>>> Thanks
>>>
>>> John Baker[/color]
>>
>>
>>You should not be trying to compact a database which others might be
>>using.
>>
>>I don't believe there is one standard solution to this because it depends
>>on
>>a number of factors:
>>Is the database split front/back end?
>>Is there user-level security?
>>When should this action happen - by user click or scheduled automatically?
>>
>>Your code should
>>establish that no-one is using the database to be compacted
>>('original.mdb')
>>rename it to 'temp.mdb' (makes sure no-one is using it, or will be able to
>>during compact routine)
>>take a copy called 'copy1.mdb'
>>compact this to a new database 'copy2.mdb'
>>if all goes well, rename 'copy2.mdb' 'original.mdb'
>>delete 'copy1.mdb' and 'temp.mdb'[/color][/color]

You won't find much use of macros in this newsgroup as they only offer
limited functionality - most work is done with vba. But anyway, I take it
from your reply that the database is not split (so you have one mdb file
with tables and forms in it), you have no security implemented and there are
no multi-user issues.
In that case you can just modify the code you found so that it is a function
, rather than a sub:

Public Function CompactDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Function

Then you can use this in a macro by having a step of RunCode and fill in the
function name:

=CompactDB()

Note that this needs to be the final step of any macro as Access needs to
re-start the database during this step and a macro cannot continue during
this.

Note that you could also just switch on the CompactOnClose option, so the
database is automatically compcted.

Anthony:

Thanks for your comments, I appreciate them. Unfortunately the DB and the programs are
split, so that there is a back end and a front end. I don't know if this impacts what you
advised, but I thought I should at least mention it,.

By the way, what is the "CompactOnClose" option, and how do I activate it?

Thanks again

John Baker

"Anthony England" <aengland@oops.co.uk> wrote:
[color=blue]
>
>"John Baker" <johnhbaker@comcast.net> wrote in message
>news:u0ld12hb02p424ep8d758rf1rlsvg5bi04@4ax.com.. .[color=green]
>> Anthony:
>>
>> I am a bit of a neophyte with this aspect of access.
>>
>> The db is single user right now, so no problem there. However, I am not
>> certain of the
>> code to use to accomplish what I want.
>>
>> I stumbled on this code in the Access Web site:
>>
>> Public Sub CompactDB()
>>
>> CommandBars("Menu Bar"). _
>> Controls("Tools"). _
>> Controls("Database utilities"). _
>> Controls("Compact and repair database..."). _
>> accDoDefaultAction
>>
>> End Sub
>>
>> I have installed it, and can run it from a button called directlt, but
>> would like to
>> combine it with some other actions, and don't appear to be able to set up
>> as "code" call
>> in a macro that will execute it.
>>
>> The actual code does the job when i execute it through the debugger, but
>> it wont execute
>> when I try and set it up using a macro. I would like to make it into an
>> nice easy button
>> press to remove a lot of excess data (old historical data that is
>> unnecessary) and
>> compress the files, but if the code wont execute in a macro this is hard
>> to accomplish,
>> since Im not skilled in Basic code.
>>
>> Best
>>
>> John
>>
>> "Anthony England" <aengland@oops.co.uk> wrote:
>>[color=darkred]
>>>"John Baker" <johnhbaker@comcast.net> wrote in message
>>>news:cvid12doc8ulkcs37hbtmege113as2neqt@4ax.com ...
>>>> Hi:
>>>>
>>>> I have read a number of threads on the issue of compressing (compacting)
>>>> Access data bases
>>>> programmatically, and have been left confused. We are using Access 2000,
>>>> and I need code
>>>> that will programmatically compress an open database. (called
>>>> "InvoiceManagement.be).
>>>>
>>>> Can someone give me a piece of code that will do this, or a pointer to
>>>> one, please?
>>>>
>>>> Thanks
>>>>
>>>> John Baker
>>>
>>>
>>>You should not be trying to compact a database which others might be
>>>using.
>>>
>>>I don't believe there is one standard solution to this because it depends
>>>on
>>>a number of factors:
>>>Is the database split front/back end?
>>>Is there user-level security?
>>>When should this action happen - by user click or scheduled automatically?
>>>
>>>Your code should
>>>establish that no-one is using the database to be compacted
>>>('original.mdb')
>>>rename it to 'temp.mdb' (makes sure no-one is using it, or will be able to
>>>during compact routine)
>>>take a copy called 'copy1.mdb'
>>>compact this to a new database 'copy2.mdb'
>>>if all goes well, rename 'copy2.mdb' 'original.mdb'
>>>delete 'copy1.mdb' and 'temp.mdb'[/color][/color]
>
>
>
>You won't find much use of macros in this newsgroup as they only offer
>limited functionality - most work is done with vba. But anyway, I take it
>from your reply that the database is not split (so you have one mdb file
>with tables and forms in it), you have no security implemented and there are
>no multi-user issues.
>In that case you can just modify the code you found so that it is a function
>, rather than a sub:
>
>Public Function CompactDB()
>
> CommandBars("Menu Bar"). _
> Controls("Tools"). _
> Controls("Database utilities"). _
> Controls("Compact and repair database..."). _
> accDoDefaultAction
>
>End Function
>
>
>Then you can use this in a macro by having a step of RunCode and fill in the
>function name:
>
>=CompactDB()
>
>Note that this needs to be the final step of any macro as Access needs to
>re-start the database during this step and a macro cannot continue during
>this.
>
>Note that you could also just switch on the CompactOnClose option, so the
>database is automatically compcted.
>
>
>
>
>
>
>
>
>
>[/color]

"John Baker" <johnhbaker@comcast.net> wrote in message
news:6gse12pp5h0l5us3uol93c7h3og97d2471@4ax.com...[color=blue]
> Anthony:
>
> Thanks for your comments, I appreciate them. Unfortunately the DB and the
> programs are
> split, so that there is a back end and a front end. I don't know if this
> impacts what you
> advised, but I thought I should at least mention it,.
>
> By the way, what is the "CompactOnClose" option, and how do I activate it?
>
>
> Thanks again
>
> John Baker[/color]

Hi John
Yes it does make a difference - in fact it was one of the first questions I
asked. The difference it makes is that you could be compacting either the
front or the back end. Normally, it is the back end which needs compacting
as this is what holds the data and which has the additions and deletions
that cause the bloat (the space is not automatically re-claimed after
records are deleted)
So, what you need to do is compact the back end from the front end - this is
something the compact on close cannot do for you. This, by the way, is
under Tools>Options>General tab and simply compacts the database
automatically when you close it.
However, in your case, you should create a new module named "modCompact" and
cut and paste the following code into it. You need to alter one line where
you write in the name of any front-end linked table instead of:
"ReplaceThisWithYourTableName"
Make sure it compiles and then save the database. You can now have a macro
with a step of RunCode where you write in the name of the function
=CompactBackEnd()

I have tested this code, but I can't guarantee it, so test cautiously.

' *** Code Starts ***
Option Compare Database
Option Explicit

Private Const TABLE_NAME As String = "ReplaceThisWithYourTableName"
'
'

Public Function CompactBackEnd() As Boolean

On Error GoTo Err_Handler

Dim strBackEndPath As String
Dim strBackEndDir As String
Dim strBackEndFile As String
Dim strTempPath As String
Dim strCopyPath1 As String
Dim strCopyPath2 As String
Dim strStartSize As String
Dim strStopSize As String
Dim strWarning As String

strBackEndPath = BackEndPath(TABLE_NAME)

If Len(strBackEndPath) > 0 Then
strBackEndFile = Dir(strBackEndPath)
Else
MsgBox "Cannot locate datasource", vbCritical, _
"Compact and Repair Routine"
Exit Function
End If

If Len(strBackEndFile) > 0 Then
strBackEndDir = Left$(strBackEndPath, _
Len(strBackEndPath) - Len(strBackEndFile))
Else
MsgBox "Cannot locate datasource", vbCritical, _
"Compact and Repair Routine"
Exit Function
End If

strTempPath = strBackEndDir & "tmp" & strBackEndFile

strCopyPath1 = strBackEndDir & "tmpCopyDb1.mdb"

strCopyPath2 = strBackEndDir & "tmpCopyDb2.mdb"

strWarning = "A temporary file has been detected." & vbCrLf & _
"This may have come from a previous attempt " & _
"to compact and repair the database." & vbCrLf & _
"If you are sure this holds no useful data " & _
"then it may be deleted." & vbCrLf & "Otherwise rename " &
_
"or move the file to another location." & vbCrLf & vbCrLf &
_
"The current path is:" & vbCrLf

If Len(Dir(strTempPath)) > 0 Then
MsgBox strWarning & strTempPath, vbCritical, _
"Compact and Repair Routine"
Exit Function
End If

If Len(Dir(strCopyPath1)) > 0 Then
MsgBox strWarning & strCopyPath1, vbCritical, _
"Compact and Repair Routine"
Exit Function
End If

If Len(Dir(strCopyPath2)) > 0 Then
MsgBox strWarning & strCopyPath2, vbCritical, _
"Compact and Repair Routine"
Exit Function
End If

Name strBackEndPath As strTempPath

strStartSize = GetFileSize(strTempPath)

FileCopy strTempPath, strCopyPath1

DBEngine.CompactDatabase strCopyPath1, strCopyPath2

strStopSize = GetFileSize(strCopyPath2)

Name strCopyPath2 As strBackEndPath

Kill strCopyPath1

Kill strTempPath

MsgBox "Backup complete for:" & strBackEndPath & vbCrLf & _
"Compacted from: " & strStartSize & _
" to " & strStopSize, vbInformation, _
"Compact and Repair Routine"

CompactBackEnd = True

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Function BackEndPath(strTableName As String) As String

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strPath As String

Set dbs = CurrentDb

Set tdf = dbs.TableDefs(strTableName)

If Len(tdf.Connect) > 0 Then
strPath = Mid$(tdf.Connect, 11)
End If

BackEndPath = strPath

Exit_Handler:

If Not tdf Is Nothing Then
Set tdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
'No error message just return a zero-length string
Resume Exit_Handler

End Function

Private Function GetFileSize(strPath As String) As String

On Error GoTo Err_Handler

Dim intFile As Integer
Dim lngSize As Long
Dim sngSize As Single
Dim Digit As Byte
Dim strReturn As String

intFile = FreeFile

Open strPath For Binary Access Read As #intFile

lngSize = LOF(intFile)

Close #intFile

Select Case lngSize

Case Is > 1073741824
sngSize = lngSize / 1073741824
strReturn = "GB " & Format(sngSize, "###,###.000")

Case Is > 1048576
sngSize = lngSize / 1048576
strReturn = "MB " & Format(sngSize, "###,###.000")

Case Else
sngSize = lngSize / 1024
strReturn = "KB " & Format(sngSize, "###,###.000")

End Select

Digit = CByte(Right$(strReturn, 1))

If Digit > 4 Then
Digit = Digit + 1
End If

strReturn = Left$(strReturn, Len(strReturn) - 2) & CStr(Digit)

If Right$(strReturn, 3) = ".00" Then
strReturn = Left$(strReturn, Len(strReturn) - 3)
Else
If Right$(strReturn, 1) = "0" Then
strReturn = Left$(strReturn, Len(strReturn) - 1)
End If
End If

strReturn = Mid$(strReturn, 4) & " " & Left$(strReturn, 2)

GetFileSize = strReturn

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
' *** Code Ends ***

"Anthony England" <aengland@oops.co.uk> wrote in
news:dv93ka$q43$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
[color=blue]
> So, what you need to do is compact the back end from the front
> end. . . .[/color]

Keep in mind that you won't be able to compact as long as any tables
in the back end are open by any users. This includes the user
initiating the compact from the front end -- if there is any bound
form open, it will prevent the back end from being compacted.
[color=blue]
> . . . - this is
> something the compact on close cannot do for you.[/color]

I think compact on close is a useless and dangerous feature. Given
that it can potentially cause you to lose information/data, I don't
think it's good to have it happen without being requested by the
user.

Secondly, a well-designed front end should never need to be
compacted, and no well-designed application will ever be opening
anything other than the front end. Thus, it's a useless feature.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9787984842035f99a49ed1d0c49c5bbb2@127.0.0. 1...[color=blue]
> "Anthony England" <aengland@oops.co.uk> wrote in
> news:dv93ka$q43$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>[color=green]
>> So, what you need to do is compact the back end from the front
>> end. . . .[/color]
>
> Keep in mind that you won't be able to compact as long as any tables
> in the back end are open by any users. This includes the user
> initiating the compact from the front end -- if there is any bound
> form open, it will prevent the back end from being compacted.[/color]

Indeed - so my routine temporarily renames the file which:
a) ensures that (if succesful) no-one else had been using it
b) stops people normally connecting to it while the code is running

[color=blue][color=green]
>> . . . - this is
>> something the compact on close cannot do for you.[/color]
>
> I think compact on close is a useless and dangerous feature. Given
> that it can potentially cause you to lose information/data, I don't
> think it's good to have it happen without being requested by the
> user.[/color]

I agree.

[color=blue]
> Secondly, a well-designed front end should never need to be
> compacted, and no well-designed application will ever be opening
> anything other than the front end. Thus, it's a useless feature.[/color]

I pretty much agree, athough Access is frequently used by people who have an
all-in-one mdb file which is used exclusively by them on a single-user
machine. Nevertheless, unless you had reliable backup I would be cautious
of having it done automatically.
I never take any steps to try and compact front ends which I have
distributed and are being used. Some of these have been working for many
years without any need to compact. While I am working on them, however, I
compact regularly.

[color=blue]
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/[/color]

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9787984842035f99a49ed1d0c49c5bbb2@127.0.0. 1...[color=blue]
> "Anthony England" <aengland@oops.co.uk> wrote in
> news:dv93ka$q43$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>[color=green]
>> So, what you need to do is compact the back end from the front
>> end. . . .[/color]
>
> Keep in mind that you won't be able to compact as long as any tables
> in the back end are open by any users. This includes the user
> initiating the compact from the front end -- if there is any bound
> form open, it will prevent the back end from being compacted.[/color]

Indeed - so my routine temporarily renames the file which:
a) ensures that (if succesful) no-one else had been using it
b) stops people normally connecting to it while the code is running

[color=blue][color=green]
>> . . . - this is
>> something the compact on close cannot do for you.[/color]
>
> I think compact on close is a useless and dangerous feature. Given
> that it can potentially cause you to lose information/data, I don't
> think it's good to have it happen without being requested by the
> user.[/color]

I agree.

[color=blue]
> Secondly, a well-designed front end should never need to be
> compacted, and no well-designed application will ever be opening
> anything other than the front end. Thus, it's a useless feature.[/color]

I pretty much agree, athough Access is frequently used by people who have an
all-in-one mdb file which is used exclusively by them on a single-user
machine. Nevertheless, unless you had reliable backup I would be cautious
of having it done automatically.
I never take any steps to try and compact front ends which I have
distributed and are being used. Some of these have been working for many
years without any need to compact. While I am working on them, however, I
compact regularly.

[color=blue]
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/[/color]

"Anthony England" <aengland@oops.co.uk> wrote in
news:dva0fg$ip2$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:
[color=blue]
>
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
> news:Xns9787984842035f99a49ed1d0c49c5bbb2@127.0.0. 1...[color=green]
>> "Anthony England" <aengland@oops.co.uk> wrote in
>> news:dv93ka$q43$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>>[color=darkred]
>>> So, what you need to do is compact the back end from the front
>>> end. . . .[/color]
>>
>> Keep in mind that you won't be able to compact as long as any
>> tables in the back end are open by any users. This includes the
>> user initiating the compact from the front end -- if there is any
>> bound form open, it will prevent the back end from being
>> compacted.[/color]
>
> Indeed - so my routine temporarily renames the file which:[/color]

Er, you can't rename a file that's open, so it's really no different
than a compact, which exclusively locks the MDB while it's working.
[color=blue]
> a) ensures that (if succesful) no-one else had been using it
> b) stops people normally connecting to it while the code is
> running[/color]

A regular compact does both of those things, and you couldn't rename
it in any circumstance where you also couldn't compact it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9787B586CECEf99a49ed1d0c49c5bbb2@127.0.0.1 ...[color=blue]
> "Anthony England" <aengland@oops.co.uk> wrote in
> news:dva0fg$ip2$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:
>[color=green]
>>
>> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
>> news:Xns9787984842035f99a49ed1d0c49c5bbb2@127.0.0. 1...[color=darkred]
>>> "Anthony England" <aengland@oops.co.uk> wrote in
>>> news:dv93ka$q43$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>>>
>>>> So, what you need to do is compact the back end from the front
>>>> end. . . .
>>>
>>> Keep in mind that you won't be able to compact as long as any
>>> tables in the back end are open by any users. This includes the
>>> user initiating the compact from the front end -- if there is any
>>> bound form open, it will prevent the back end from being
>>> compacted.[/color]
>>
>> Indeed - so my routine temporarily renames the file which:[/color]
>
> Er, you can't rename a file that's open, so it's really no different
> than a compact, which exclusively locks the MDB while it's working.
>[color=green]
>> a) ensures that (if succesful) no-one else had been using it
>> b) stops people normally connecting to it while the code is
>> running[/color]
>
> A regular compact does both of those things, and you couldn't rename
> it in any circumstance where you also couldn't compact it.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/[/color]

Do you really think I don't know this, or are you trying to wind me up?
The code was written in response to someone asking how to compact the back
end from with a function he could use in a macro. If the code is able to
re-name the file, I know that no-one had been in the file at the time and it
is my green light to proceed with the compact. The OP specifically says the
database is single-user, so there is no error handling to deal with the file
being open - it will simply stop if the file can't be re-named. However,
this could easily be modified to take some other action.
If you have a better solution, please feel free to let the OP know.

"Anthony England" <aengland@oops.co.uk> wrote in
news:dva94o$39l$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
[color=blue]
>
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
> news:Xns9787B586CECEf99a49ed1d0c49c5bbb2@127.0.0.1 ...[color=green]
>> "Anthony England" <aengland@oops.co.uk> wrote in
>> news:dva0fg$ip2$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:
>>[color=darkred]
>>>
>>> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in
>>> message news:Xns9787984842035f99a49ed1d0c49c5bbb2@127.0.0. 1...
>>>> "Anthony England" <aengland@oops.co.uk> wrote in
>>>> news:dv93ka$q43$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>>>>
>>>>> So, what you need to do is compact the back end from the front
>>>>> end. . . .
>>>>
>>>> Keep in mind that you won't be able to compact as long as any
>>>> tables in the back end are open by any users. This includes the
>>>> user initiating the compact from the front end -- if there is
>>>> any bound form open, it will prevent the back end from being
>>>> compacted.
>>>
>>> Indeed - so my routine temporarily renames the file which:[/color]
>>
>> Er, you can't rename a file that's open, so it's really no
>> different than a compact, which exclusively locks the MDB while
>> it's working.
>>[color=darkred]
>>> a) ensures that (if succesful) no-one else had been using it
>>> b) stops people normally connecting to it while the code is
>>> running[/color]
>>
>> A regular compact does both of those things, and you couldn't
>> rename it in any circumstance where you also couldn't compact it.[/color]
>
> Do you really think I don't know this, or are you trying to wind
> me up? The code was written in response to someone asking how to
> compact the back end from with a function he could use in a macro.
> If the code is able to re-name the file, I know that no-one had
> been in the file at the time and it is my green light to proceed
> with the compact. The OP specifically says the database is
> single-user, so there is no error handling to deal with the file
> being open - it will simply stop if the file can't be re-named.
> However, this could easily be modified to take some other action.
> If you have a better solution, please feel free to let the OP
> know.[/color]

If my comments about renaming the file are irrelevant to the OP's
question, then my comments about compacting were irrelevant as well.
You didn't see fit to criticize those, so I'm not sure why you're
changing your tune now.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9787CC39D5ED2f99a49ed1d0c49c5bbb2@127.0.0. 1...[color=blue]
> "Anthony England" <aengland@oops.co.uk> wrote in
> news:dva94o$39l$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>[color=green]
>>
>> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
>> news:Xns9787B586CECEf99a49ed1d0c49c5bbb2@127.0.0.1 ...[color=darkred]
>>> "Anthony England" <aengland@oops.co.uk> wrote in
>>> news:dva0fg$ip2$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:
>>>
>>>>
>>>> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in
>>>> message news:Xns9787984842035f99a49ed1d0c49c5bbb2@127.0.0. 1...
>>>>> "Anthony England" <aengland@oops.co.uk> wrote in
>>>>> news:dv93ka$q43$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>>>>>
>>>>>> So, what you need to do is compact the back end from the front
>>>>>> end. . . .
>>>>>
>>>>> Keep in mind that you won't be able to compact as long as any
>>>>> tables in the back end are open by any users. This includes the
>>>>> user initiating the compact from the front end -- if there is
>>>>> any bound form open, it will prevent the back end from being
>>>>> compacted.
>>>>
>>>> Indeed - so my routine temporarily renames the file which:
>>>
>>> Er, you can't rename a file that's open, so it's really no
>>> different than a compact, which exclusively locks the MDB while
>>> it's working.
>>>
>>>> a) ensures that (if succesful) no-one else had been using it
>>>> b) stops people normally connecting to it while the code is
>>>> running
>>>
>>> A regular compact does both of those things, and you couldn't
>>> rename it in any circumstance where you also couldn't compact it.[/color]
>>
>> Do you really think I don't know this, or are you trying to wind
>> me up? The code was written in response to someone asking how to
>> compact the back end from with a function he could use in a macro.
>> If the code is able to re-name the file, I know that no-one had
>> been in the file at the time and it is my green light to proceed
>> with the compact. The OP specifically says the database is
>> single-user, so there is no error handling to deal with the file
>> being open - it will simply stop if the file can't be re-named.
>> However, this could easily be modified to take some other action.
>> If you have a better solution, please feel free to let the OP
>> know.[/color]
>
> If my comments about renaming the file are irrelevant to the OP's
> question, then my comments about compacting were irrelevant as well.
> You didn't see fit to criticize those, so I'm not sure why you're
> changing your tune now.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/[/color]

Because I suddenly got irritated.
Because I suddenly wondered whether the OP would ever read the code
....let alone try it - and here I am debating whether an open file can be
re-named or not with someone who has no interest in using the code.
I sometimes wonder what I am doing here at all.