Prev: Speeding up access programs with linked tables
Next: cannot send or receive email using windows XP
From: Maj_USA_Ret on 23 Mar 2010 11:16
Request how to add Compact and Repair action to Macro.
Trying to create Macro that deletes many old and large tables, then runs
Compact and Repair, then imports new tables through ODBC.
Currently:
Step 1: Delete old tables.
Step 2: Run the compact and repair from the tools menu
Step 3: Import.
Intent is to combine all three steps above into single button, one click
macro.
Thankx

"Wayne-I-M" wrote:

> Hi Rodrigo
>
> You don't need a macro.
>
> Use
> Tools
> Options
> General
> Compact on Close.
>
> It would be pointless compacting on open in this case - as the DB has
> already been compacted last time it was closed.
>
> --
> Wayne
> Manchester, England.
>
>
>
> "Rodrigo Borges" wrote:
>
> > I have a DB tht loads a form when opened. I want to run a compact DB every
> > time I open and close the DB.
> >
> > Is there a away to accomplis that using macro?
> >
From: Paul Shapiro on 23 Mar 2010 18:29
This VBA function compacts a db:
Public Function pjsCompactDatabase( _
dbDataBase As DAO.Database _
) As Boolean
'Comments : Closes the database and compacts it
Dim strDBFileName As String, strDBFileNameTemp As String

'Get current database name and create name to compact into
strDBFileName = dbDataBase.Name
strDBFileNameTemp = strDBFileName & ".tempCompactNewData.mdb"

'Close the existing database and flush all writes to disk
dbDataBase.Close
Set dbDataBase = Nothing
DBEngine.Idle dbRefreshCache
DoEvents

'Compact the database to a new, temporary file name.
DBEngine.CompactDatabase srcName:=strDBFileName,
dstName:=strDBFileNameTemp
DBEngine.Idle dbRefreshCache
DoEvents

'Delete the existing db and rename the temp file to the existing name.
Kill strDBFileName
Name strDBFileNameTemp As strDBFileName

'Indicate success
pjsCompactDatabase = True
End Function

"Maj_USA_Ret" <MajUSARet(a)discussions.microsoft.com> wrote in message
news:ED49A749-6E37-4825-A0C9-E001682F113E(a)microsoft.com...
> Request how to add Compact and Repair action to Macro.
> Trying to create Macro that deletes many old and large tables, then runs
> Compact and Repair, then imports new tables through ODBC.
> Currently:
> Step 1: Delete old tables.
> Step 2: Run the compact and repair from the tools menu
> Step 3: Import.
> Intent is to combine all three steps above into single button, one click
> macro.
> Thankx
>
> "Wayne-I-M" wrote:
>
>> Hi Rodrigo
>>
>> You don't need a macro.
>>
>> Use
>> Tools
>> Options
>> General
>> Compact on Close.
>>
>> It would be pointless compacting on open in this case - as the DB has
>> already been compacted last time it was closed.
>>
>> --
>> Wayne
>> Manchester, England.
>>
>>
>>
>> "Rodrigo Borges" wrote:
>>
>> > I have a DB tht loads a form when opened. I want to run a compact DB
>> > every
>> > time I open and close the DB.
>> >
>> > Is there a away to accomplis that using macro?

 | 
Pages: 1
Prev: Speeding up access programs with linked tables
Next: cannot send or receive email using windows XP