Jim Lawrence
accessd at shaw.ca
Wed Dec 19 17:00:17 CST 2007
Whoops, I should read more carefully. Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, December 19, 2007 12:56 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compact and Repair Database
Hi Jim,
No, you misunderstand.
I am saying that typing "AT /?" does work on my laptop (displays the
options)
Dell Laptop WinXP Pro.
Could be useful. Stored in my goodies-bag.
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, December 19, 2007 8:54 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compact and Repair Database
Hi Max:
That depends on the OS. Just go to the command prompt and enter "AT /?" and
will either display a list of options and configuration samples or an error.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, December 19, 2007 3:43 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compact and Repair Database
AT /? works on my laptop.
Max
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, December 19, 2007 11:07 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compact and Repair Database
...or if you can run this process using the AT command syntax. This is setup
at the command prompt and will work like a CRON job. I am not sure whether
this command is available on a desktop machine but it has been in servers
since NT. To view the syntax, go to the command prompt and keyin: AT /?
HTH
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Tuesday, December 18, 2007 1:32 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compact and Repair Database
You can just schedule a batch job to run a 2 AM...
<Batch File Text>
@Echo off
if exist "X:\SomePath\SomeFile.ldb" del "X:\SomePath\SomeFile.ldb"
if exist "X:\SomePath\SomeFile.ldb" goto inuse
:compact
if not exist "X:\SomePath\SomeFile.mdb" goto NO_MDB "C:\Program
Files\Microsoft Office\Office10\MSACCESS.EXE"
"X:\SomePath\SomeFile.mdb" /compact
Goto end
:inuse
echo SomeFile.mdb was in use. No Compact performed. >>
"Y:\PathToLogFile\Compact.log"
Goto end
:NO_MDB
echo X:\SomePath\SomeFile.mdb not found >> "Y:\PathToLogFile\Compact.log"
Goto end
:end
Exit
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Tuesday, December 18, 2007 3:57 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Compact and Repair Database
I am looking for some ideas on how to compact and repair nightly (2:00 AM) a
database that is closed. The compacted database needs to have the same name
as the original one. I had thought about using windows scheduler somehow to
do this. I can across the following code but access stops and must exit when
it gets to the line.
DBEngine.CompactDatabase FilePath & OriginalFile, FilePath & _
FileWithoutExtention & "Temp.mdb"
Here is all the code:
Public Function cmdCompact()
On Error GoTo Err_cmdCompact
Dim DateUnderscore As String
Dim ExecutionMacro As String
Set DB = CurrentDb
DateUnderscore = Month(Date) & "_" & Day(Date) & "_" & Year(Date)
FilePath = Mid(DB.Name, 1, Len(DB.Name) - Len(Dir(DB.Name)))
OriginalFile = "Sacroc.mdb"
FileWithoutExtention = Left(OriginalFile, InStr(OriginalFile, ".") - 1)
DoCmd.Hourglass True
TryAgain:
'Compact the Back-End database to a temp file.
DBEngine.CompactDatabase FilePath & OriginalFile, FilePath & _
FileWithoutExtention & "Temp.mdb"
'Delete the previous backup file if it exists.
If Dir(FilePath & FileWithoutExtention & ".bak") <> "" Then
Kill FilePath & FileWithoutExtention & ".bak"
End If
'Rename the current database as backup and rename the temp file to
'the original file name.
Name FilePath & OriginalFile As FilePath & FileWithoutExtention & ".bak"
Name FilePath & FileWithoutExtention & "Temp.mdb" As FilePath & _
OriginalFile
DoCmd.Hourglass False
Exit_cmdCompact:
Exit Function
Err_cmdCompact:
If Err.Number = 3356 Then
Resume TryAgain
ElseIf Err.Number = 3045 Then
Resume TryAgain
Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_cmdCompact
End If
Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799
No trees were killed in the sending of this message. However a large number
of electrons were terribly inconvenienced.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com