Compact & Repair using SendKeys 4

[EVE734]

Hi - I wanted to set up a command button that would perform a "compact and repair" upon click. I found an old thread - thread 181-73087 - that had a simple suggestion.

The "compact" macro (sendkeys %TDC) seems to work great, but the "repair" macro (using sendkeys %TDR) doesn't seem to work. The original thread was pertaining to Access97 and I assume that is the problem as I am in Access2000.

With Access 2000, do the sendkeys %TDC perform both the compact and repair?

Can anyone help?
Thanks,
Evelyn

 

[JerryKlmns]

In A2000 and above repair is the spouse of compact. So the walk hand by hand and happen one exactly after the other. sendkeys %TDR does not exist any more...

 

[mp9]

Just as a thought, why not use a scheduler to compact/repair without at definable intervals, without having to remember to manually click a button?

Take a look at the Handy Access Launcher as an example:

http://www22.brinkster.com/accessory/resources/001.shtml

 

[Ed2020]

I'd split the database into a frontend and a backend. Assuming the frontend contains no tables and you only want to compact/repair the backend the following code can be run to do what you want:

Sub CompactDB()
On Error GoTo CompactDB_Err
Const conFilePath = <put your directory path here>

DBEngine.CompactDatabase conFilePath & "<put your filename here>", conFilePath & "<put temporary filename here>"

If Dir(conFilePath & "<filename without extension>.bak") <> "" Then
Kill conFilePath & "<filename without extension>.bak"
End If

Name conFilePath& "<put your filename here>" As conFilePath &"<filename without extension>.bak"
Name conFilePath & "<put temporary filename here>" As conFilePath & "<put your filename here>"

Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox Err.Description
Resume Exit_CompactDB

End Sub

Please do not feed the trolls.....

 

[EVE734]

Thanks all!

Jerry - thanks for the confirmation. This is great to know.

mp9 - Started toying with that idea too. I will check out the link you supplied.

Ed2020 - It already is split - I was thinking that the macro I set up would open the back-end and then perform the compact and repair...but I am having trouble with that (can the back-end not be opened if you are in the front end? My macro is using the RunApp action...) I will try out your code as well.

THANKS AGAIN!

 

[EVE734]

I just wanted to mention another link for info on automatic scheduling of compact and repairs - it walks you through an example step by step:

http://www.datapigtechnologies.com/flashfiles/schedulecompact.html

 

[Ed2020]

EVE,

The code I have posted is opening the backend and compacting it, so yes it is possible.

Avoid SendKeys whereever possible - it can prove problematic.

Ed Metcalfe.

Please do not feed the trolls.....

 

[EVE734]

Ed - Thanks.

I am trying out your code, but what is the "temporary" file name you are referring to?

Also - I've heard SendKeys can be problematic, but why is that? It seems so much easier!

 

[PHV]

I've heard SendKeys can be problematic, but why is that?
The main reason is that you're never sure which app has the focus when the keystrokes are sent.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EVE734]

Makes sense - Thanks PH.

 

[Ed2020]

EVE,

The code I have posted compacts your main database to a temporary file. It then takes a backup of your current database (xxx.bak - in case anything goes pear shaped) and then renames the temporary file to match the original master database.

SendKeys can also be problematic if the user presses any kets whilst the code is running, although this is less likely than the example PHV posted.

Ed Metcalfe.

Please do not feed the trolls.....

 

[EVE734]

Ed,

I really appreciate your help and time on this. I tried out the code, but am getting a message that I "attempted to open a database that is already opened exclusively (by user "Admin", which is me)" and "to try again when the database is available".

Could you give me any further help?

Thanks in advance.

 

[Ed2020]

Eve,

Make sure you're opening your front end for shared access, rather than opening it exclusively.

It would also be a good idea to ensure you don't have any bound forms open when you run the compaction code. I'd put a command button on an unbound form and have no other forms open.

Ed Metcalfe.

Please do not feed the trolls.....

 

[EVE734]

Ed,

I have checked both the front end and back end for shared access, and it does not appear to be set to open exclusively (checked under Tools - Options - Advanced - which shows the default to be shared access).

Is there something else I need to check?

The command button is currently on an unbound form - thanks.

I think we're close - please bear with me!

Evelyn

 

[PHV]

The code must be in the FE for compacting the BE, and nobody should use the BE during the compact.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EVE734]

Thanks PH - but the code is in the FE and nobody else is in the BE, as I am just testing this out in a copy of the actual database(s).

?

 

[PHV]

And you run the DBEngine.CompactDatabase against the BE ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EVE734]

PH - I'm not at all well-versed in VB code, but yes, I did put in the file name for the BE database in the code Ed2020 has written above.

See below for my path names - maybe I screwed up something:
(The "Live data 6_7_be" is the back end dbase)

On Error GoTo CompactDB_Err
Const conFilePath = "H:\Cpp\Evelyn\Screening\Archive\"
DBEngine.CompactDatabase conFilePath & "Live data 6_7_be.mdb", conFilePath & "Live data 6_7_be.bak"

If Dir(conFilePath & "Live data 6_7_be.bak") <> "" Then
Kill conFilePath & "Live data 6_7_be.bak"
End If

Name conFilePath & "Live data 6_7_be.mdb" As conFilePath & "Live data 6_7_be.bak"
Name conFilePath & "Live data 6_7_be.bak" As conFilePath & "Live data 6_7_be.mdb"

Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox Err.Description
Resume Exit_CompactDB


End Sub

THANKS AGAIN!!!!!

 

[PHV]

Just saw the obvious:
DBEngine.CompactDatabase conFilePath & "Live data 6_7_be.mdb", conFilePath & "Live data 6_7_be.[!]tmp[/!]"

If Dir(conFilePath & "Live data 6_7_be.bak") <> "" Then
Kill conFilePath & "Live data 6_7_be.bak"
End If

Name conFilePath & "Live data 6_7_be.mdb" As conFilePath & "Live data 6_7_be.bak"
Name conFilePath & "Live data 6_7_be.[!]tmp[/!]" As conFilePath & "Live data 6_7_be.mdb"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EVE734]

AHA - well that WAS bad. However, I made the change and it is STILL telling me I have it opened exclusively...

I will understand if you need to give up on me now, but if you have any other thoughts, that would be great.