SendKeys to compact

[gblack]

So I have a process that basically takes a bunch of data from one table and stratifies it to another based on a gazillion business rules. The issue is that the performance starts off decently fast then comes to a grinding halt. I have found that while the process is running the database grows very quickly.

Stoping the code before it completes the process and manually compacting the database speeds things up immensely. The difference between a 2 hour process and a 39 minute process.

So what I want to do is periodically stop the code and compact the database, without having to manually click through repair and compact every time.

I was able to use the SendKeys "%(FMC)", False command in a macro that runs a function... to compact the data. So the code will run... The problem is that I can't seem to get the compact to work from my process function. the ALT+F doesn't open the MS Icon in the VBA debugger... so I am assuming I need to somehow tell Access to be in the database window... I tried to use the sendKeys for alt+F11 i.e., "%{F11}", but that just locked up on me when I attempted to just run the SendKeys and not even put it in my biggger process function.

What I'd like to do is use an autoexec macro and have the code run every time the database opens... get to the point of the compact and then compact... when it opens again the autoexec macro will kick off and then I'll have a trigger in a table to kill the process when it's all done.

SO does anyone know how I can compact the database from within the a function I am running?

Thanks,
Gary

 

[spikepl]

How about simply setting the db to compact on close?

 

[speakers_86]

There was recently a post in the sample or the code repository on compacting databases, fe or be. Also you should exercise caution when you compact. Compacting has a chance of corruption.

Also, what are you doing that takes 39 minutes?? I would imagine a huge loop and/or moving a lot of records around. Are you sure that your db is set up correctly?

 

[gblack]

Setting compact on close is pointless. Yes, I understand the dangers of compacting, especially data that has been halted... but I think I am ok there.

Basically here's the cycle: I need to run the code partially... compact... run the code again.... compact.... run the code again... compact... then eventually the code will be complete.

As far as the database being set up correctly... It is what it is and I cannot change the way the US Navy does things. I am basically pulling every item the US Navy owns and trying to stratify it... this one report process is a Monster. The biggest thing I have done...to date.

My other issue is that the Navy also has security setting applied that always prompts for macro usage...Thusly, I cannot do anything that opens and closes the database(although compacting does not trigger the macros security warning, which is why I have hope that I can do this), because I would have to choose the allow macros, by hand, each time... which defeats the purpose of automating, entirely.

So... that said... By hand I am able to run the code, set the code to stop after running through a given interval of records, then click compact and repair, start the code up again, wait for it to stop, rinse repeat till code finished up.

So why can't I do this porgrammatically???

 

[spikepl]

What is pointless about compacting on close? Since you are halting the execution anyway, and have problems triggering compact from code? My mind-reading skills are fairly limited, so please do explain.

 

[speakers_86]

Maybe this will help.

As far as the trusted location goes, I am experiencing the same issue. If you talk to the person with admin rights (in the army this person is the IMO), maybe they can set up a trusted location for you.

Another problem I have is that I can not open a batch file with shell nor shell execute on a particular machine. Have you ran across this? Do you have a work around by chance?

 

[gblack]

Sorry... I guess I am not explaining the issue correctly... What is pointless about compacting on close is that it doesn't help what I am trying to do. The things is I can't close the database (which is why compact on close won't work for me). If I do close the database, then when I reopen it, I get prompted with the security warning disabling macros and then you have to manually click to allow them. So... If I have to stand by the process and wait for it to close... then reopen it and set the security every time the process shuts down to compact... I might as well do the compacts by hand. Thusly negating any automation.

Currently I am able to halt the process, compact the database (without getting the Macros security prompt) and then continue the code. This is a manual process, so there should be a way to program it... only I have exhausted just about every angle I can think of and nothing will work.

Believe it or not, the Army is FAR more accomodating than the Navy when it comes to IT. The Navy WILL NOT allow a trusted location without 6 months of meetings and paperwork. NMCI simply will not allow it otherwise.

I have to use what I have... which is why I am asking... but I guess it can't be done...

I actually have some code that compacts the database, but closes it to do so... once opened I can't compacted it again, or even close it... if I could close an outside database, from onother database, that might work... but I can't see how to do that.

The link was promissing, but it still doesn't show me how to progammatically close an external database. It will close itself... but that doesn't help me. I need to be able to close the backend database from the front end, in order to get this to work right. If I could just send a compand that closed then reopened an external database... Then... the compact on close would actually be helpful.

I think I will post that as a seperate question. See if anyone knows...

 

[speakers_86]

From the link I provided:

' This module will compact an external database OR the current database.
' If the current database is being compacted, it will close the database,
' create a batch file, and reopen.

This should answer your question. Though I suspect that if compacting is necessary, maybe you should consider a different backend, or perhaps even a different language altogether. Maybe someone more experienced then I could step in here though.

 

[gblack]

From the link I provided:



This should answer your question. Though I suspect that if compacting is necessary, maybe you should consider a different backend, or perhaps even a different language altogether. Maybe someone more experienced then I could step in here though.

Unfortunately that doesn't work for me. It only will close the database if you are currently running from said database. Here's the two things I am looking for that the link doesn't provide...EITHER:

A) Allow me to compact the current database WITHOUT closing it (as you can do manually)
OR
B) Allow me to compact and close and external database which is already open.


Your link: compacts and CLOSES the current database and it only compacts an external database if it is already closed, at which poin, it may even open the already closed database... but that doesn't help me.

I need something (or some code) that enables me to close the opened database (if it is external). As of yet I haven't seen any way to do this... but there must be.

 

[irish634]

Sorry for resurrecting this, but I just wanted to add a couple of things.

As far as

B) Allow me to compact and close and external database which is already open.

I am assuming you are talking about a backend database when someone other than yourself is in it. If so, there is code out there that will kick all users off the backend and close it (I have it at home somewhere.) and then you can run the external compact.

A) Allow me to compact the current database WITHOUT closing it (as you can do manually)

I use access 2010 and I had to do this to get send keys to work:
Every example I have found on the web shows this:
SendKeys "%(YC)", False

I had to remove the parentheses in order for it to work. I'm not sure if it's common knowledge but I didn't know it.
SendKeys "%YC", False

I am not sure if this will work for the OP's issue or if you found another workaround.