UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Close And Exit, Access 2016    
 
   
Leah
post Sep 25 2019, 11:33 AM
Post#1



Posts: 823
Joined: 22-February 00
From: New York, New York


I have a "backend" database that compacts on close.

I have an autoexec with the commands CloseDatabase and even added QuitAccess Options Exit.

When I open the database it compacts and the specific database file closes, however Access itself remains open.

How can I get Access itself to close?

I want to set up an bat file that opens this file in the early morning when it is not locked as it grows very large and can get to a point where it does not work if I am not able to catch it to compact it.

Thanks

Leah


--------------------
Leah A. Kopel
Go to the top of the page
 
RJD
post Sep 25 2019, 12:28 PM
Post#2


UtterAccess VIP
Posts: 10,131
Joined: 25-October 10
From: Gulf South USA


Hi Leah: Instead of using the Compact on Close or an Autoexec, have you considered just using a command line switch (/Compact). You could put the command line pointing to the file, plus the switch, in a bat file and schedule it with the Windows scheduler. That should open, compact and close the db.

I sometimes see complaints that the Compact on Close is not always satisfactory.

Just a thought.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Leah
post Sep 25 2019, 01:10 PM
Post#3



Posts: 823
Joined: 22-February 00
From: New York, New York


RJD

Thanks for replying.

As a first test, I removed the compact on close and did not add the compact command as I wanted to make sure that Access closes. When I open it, the file closes but still not Access itself so it does not seem to have to do with the compact.

An additional problem I am having is that the file I need to refer to has spaces in the file name.

Below at least opens the file:

C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" \\abc\Local_Tables_Test.accdb

This gives me an error message:

C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" \\abc\Local Tables Test.accdb

As a bunch of Access databases point to this Access local table file by the name with spaces, is there a way to get around that using bat?

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
isladogs
post Sep 25 2019, 01:23 PM
Post#4


UtterAccess VIP
Posts: 1,795
Joined: 4-June 18
From: Somerset, UK


Use Application.Quit.

I would recommend you don't compact automatically on closing.
Just do this on a periodic basis when the file size increases by a specified amount
Or if you really must compact, run a backup first...just in case

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
RJD
post Sep 25 2019, 03:51 PM
Post#5


UtterAccess VIP
Posts: 10,131
Joined: 25-October 10
From: Gulf South USA


Hi Leah: You could just try this in a .bat file ...

"\\abc\Local Tables Test.accdb" /Compact

See if this works. I tested this approach (but with a C: path) in a .bat file. When I executed the .bat file, it opened the file, compacted, then closed the file.

If it doesn't work for you (I did this in Win 10/Access 2010), let me know.

And I agree with Colin about making a backup first. A Copy command should work for that.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Leah
post Sep 25 2019, 07:12 PM
Post#6



Posts: 823
Joined: 22-February 00
From: New York, New York


Thank you Joe and Colin

The compact command in the bat works to compact and close.

An alternate way to close a database would have been to use set up a function with Application.quit, run that code from autoexec and compact in Access. It also worked. That may come in handy if I want to do some stuff prior to closing the database. I was being lazy not to do the code as I thought the macro command would be enough for this situation.

Anyway, I decided to use the code /Compact as that kills two birds with one stone as they say since I only wanted a simple open, compact and close.

I plan to schedule it to run once a week as this particular file with all its worker tables gets giant in no time and then often because it is locked I cannot fix it and then it reaches a size where it causes the daily tables update to fail. There are backups automatically done in the network just in case it totally dies. So far I have not had that problem with this file or a couple of others that automatically compact on closing.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
RJD
post Sep 25 2019, 09:54 PM
Post#7


UtterAccess VIP
Posts: 10,131
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Leah, from both of us.

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 10:22 PM