Link to home
Start Free TrialLog in
Avatar of AliciaVee
AliciaVee

asked on

Use Shortcut - Compact and then open database

Experts,

I've had a heck of a time figuring out a solution to force users to compact, and maybe using a shortcut is best.

This app will be rolled out officially very very soon (supposed to be this week -- but I don't know ???) and I will need to instruct users on how to save their file to thier PC, what folder, etc.

I am using Access 97, and do have the Developer's kit.  I'm not a developer, but have learned a great amount in a short time (6 months).  So, I've tried making a self extracting file, through the MOD, and was not successful sharing it with other users.

So, maybe I can instruct them manually.  This is where I need your help.

I have the following code in my shortcut:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\3TS\3TSReplicaVer3.mdb"/compact

I need users to launch the database via this shortcut, and before doing so, compact.  This compacts the database, but does not keep it open.  I believe it is easier to compact on open, as opposed to compact on close.

What am I missing?  Any other suggestions?

Thanks in advance,
AliciaVee

Avatar of jadedata
jadedata
Flag of United States of America image

Hey! AliciaVee,

You are unfortunately not going to be able to conduct both ops at once.

regards
Jack
The way I handle this is through a module of functions that can be called from within the application.
Some for the FrontEnd itself and others for handling back end files.
][BRB
Alicia,

<<I believe it is easier to compact on open, as opposed to compact on close.>>

  I don't know about that.  Based on the size of the MDB, a compact can take a considerable amount of time.  If you compact on open, the user is going to set there waiting.

  I give a compact shortcut to a single poweruser and instruct them to compact the MDB as needed rather then making it part of any open / close procedure (I'm not a big fan of compact on close either).

Jim.
Code for back end compact and backup from front side.
This is from an Access97 version but with a few tweaks you could use this in 2K/XP

  Dim FinalMsg as String
  Dim dbLocationOld, dbName, dbLocationNew

  Name dbLocationOld & dbName As dbLocationOld & "CFDATA" & Format(Now, "yymmddhhnn") & ".mdb"
  FinalMsg = FinalMsg & dbLocationOld & dbName & " renamed to " & dbLocationOld & "CFDATA" & Format(Now, "yymmddhhnn") & ".mdb" & vbCrLf
 
  DBEngine.CompactDatabase dbLocationNew & dbName, dbLocationOld & dbName, dbLangGeneral
  FinalMsg = FinalMsg & dbLocationNew & dbName & " compacted to " & dbLocationOld & dbName & vbCrLf
  FinalMsg = FinalMsg & "File Size (final): " & FileLen(dbLocationOld & dbName) & " bytes" & vbCrLf

  AttachmentsClear '<Function to clear links to tables with a non-zero length "Connect" property

  Call AttachmentsSet(dbLocationOld & dbName)  '<call to function that uses a table of tables to link required files
 
  FinalMsg = FinalMsg & "ReAttached to " & dbLocationOld & dbName & vbCrLf
  FinalMsg = FinalMsg & String(90, "-") & vbCrLf
 
  If FileExists(dbLocationNew & dbName) Then Kill dbLocationNew & dbName
  FinalMsg = FinalMsg & "Removed temporary copy: " & dbLocationNew & dbName & vbCrLf
 
  If Len(Dir$(dbLocationNew)) > 0 Then RmDir dbLocationNew
  FinalMsg = FinalMsg & "Removed folder: " & dbLocationNew & vbCrLf
  FinalMsg = FinalMsg & String(90, "-") & vbCrLf

  msgbox FinalMsg
This is a compact frontend on demand option:

This is a case statement from the HandleButtonClick routine of the My stock Switchboard,...
I have created a new constant (conCmdCompactMeNow) to provide the trigger and it is activated on demand by the user from the Utilities switchboard.

Also on the Switchboard is a textbox with the following control source:
 = "Current Client MDB Size: " & Format(FileLen(CurrentDb.Name), "#,##0") & " bytes"

      Case conCmdCompactMeNow
        If Right(Application.CurrentDb.Name, 3) = "mdb" Then
          DoCmd.SelectObject acTable, , True
          On Error GoTo HandleButtonClick_Err
          DoCmd.Maximize
          xSendKeys "%TDC"
        Else
          MsgBox "Option not available at this time.", vbOKOnly + vbInformation, AppName
        End If
Avatar of 1William
1William

Alicia, you may want to consider creating a batch file.  You can then run sequential commands that way.  Your shortcut will point to teh batch file.  When you create the install using the Access developer, you can easily have it include this batch file, and set the shortcut to point to it.

Also, I am suprised you had issues with the  compact after 7.  If you had gone the table route, you would not of had any problems with the value  set as a property.  So what if the first time it happens after a number of openings different than 7?  
Avatar of AliciaVee

ASKER

William,

Yes, I did have problems with the 'message to compact after 7 openings'.  And....believe or not, I then thought maybe a table was the way to go, thus your solution.

My only question is, by adding a new table, wouldn't that affect my replication?  Wouldn't the values in a users table, be replicated and shared amongst all?  I'm confused on this.

Can you clarify?  Maybe this is the way to go as I'm am totally exhausted by this issue.

Thanks,
AliciaVee
An example.  you have a design master and a coupl replicas, all containing the table.  The design master has a value of 0, one replica has a value of 3, the other, 5.  When replicated, depending one the order they are done, the table may end up with a 3 or a 5.  Does it really matter?  So the first 'go around' maybe the compacting happens in only two more cycles, perhaps four.  After that, it will occur every seven.

I had a feeling storing the value as a db parameter might be a problem.
To really cleanup a replicated mdb you should compact 2 times.

We could move the counter outside of Access, put it in a text file so there will be no new tables and no replication issues.

Any interest?

Steve
Maybe I am oversimplifying things again (AS I DO HAVE A HABBIT OF DOING, just ask Jack) but you can compact the database on open with the following command..

C:\Program Files\Microsoft Office\Office10\msaccess.exe /compact c:\YourDatabase\db1.mdb

If that does not work I will eat my hat.  /Atropa
Hey all,

Ok, I didn't read all of that above word-for-word (sue me :) but I DO have an "Auto" compact function. You can either compact each time it's opened, or use a count somewhere and start the compact when the count is hit.

-------
Const csTmpFile As String = "Compact.bat"
Const csTmpScript As String = "Compact.vbs"

Function CompactCurrentDB()
Dim strApp$, strPath$

'GET APP NAME & PATH
strApp = Dir(CurrentDb.Name)
strPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(strApp))

'REMOVE ANY LEFT OVER TMP FILES
On Error Resume Next
Kill strPath & csTmpFile
Kill strPath & csTmpScript
On Error GoTo 0

'MAKE BATCH FILE & SCRIPT
Call CreateBatchFile(strPath, strApp)
Call CreateVBScript(strPath, strApp)

'RUN SCRIPT
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.Run strPath & csTmpScript

End Function

Sub CreateBatchFile(strPath$, strApp$)
Dim sTmp$
sTmp = strPath & strApp

Open strPath & csTmpFile For Append As #1
   Print #1, "CD " & Application.SysCmd(acSysCmdAccessDir)
   Print #1, "START /W MSAccess.exe " & sTmp & " /compact"
   Print #1, "MSAccess.exe " & strPath & strApp
Close #1

End Sub

Sub CreateVBScript(strPath$, strApp$)
Dim sTmp
sTmp = strPath & strApp

Open strPath & csTmpScript For Append As #1
   Print #1, "Dim objApp"
   Print #1, "Set objApp = GetObject(""" & sTmp & """)"
   Print #1, "objApp.Quit"
   Print #1, "Set objApp = Nothing"
   Print #1, ""
   Print #1, "Dim wsh"
   Print #1, "Set wsh = CreateObject(""WScript.Shell"")"
   Print #1, "wsh.Run """ & strPath & csTmpFile & """, 0, True"
Close #1

End Sub
--------------
Basically what this does is:

- Grab the DB path & app names
- Create a MSDOS batch file to do the compacting
- Create a VBScript file to close the current db, then run the batch file

I use it with a counter myself, works great.

Cheers,
Mike
Mike, I think that seems overkill.  She wants to compact prior to opening the database, therefore she needs to just use the /Compact switch prior to opening access.  The command line would be as follows.

C:\Program Files\Microsoft Office\Office10\msaccess.exe /compact c:\YourDatabase\db1.mdb


IF you want to compact it on close, just go to tools > Options > General Tab and confirm that Compact on Close is checked.

/Atropa
Works great if you want to compact based on parameters - size/count/etc

:)

Mike
I fully agree with you.  I can't see why someone would want to compact prior to opening a database unless they were having problems with it.  Maybe this is so they can set up a Scheduled Task that will compact it on a schedule for them.  If that is the case, I would use your Function Mike and have pass it a parameter to open to a macro that calls that function.  That way you can control it better.  Just my thought behind it though.  /Atropa
Atropa,

I first tried your solution, because it seemed the simplest.  But....the path code did not work.  I could be wrong, but shouldn't the /compact command come 'after' your file name?  I tried it before, as you display, and the shortcut properties will not accept it.  I tried it after, as I had it, and it compacts, but never opens my database.

What am I missing?

AliciaVee
William,

Hmmm...maybe you have a point about :
"When replicated, depending one the order they are done, the table may end up with a 3 or a 5.  Does it really matter?  "

It probably doesn't matter.  Maybe I will try your solution with the added table?  Can you post, since you were the first one to mention this solution.

Thanks,
AliciaVee
jadedata -- your solution is similar to 1William (I think) but I do not have a front/back end on this database.  I am using the replication features.  
stevebe,

Yes, ideally, the replicated database should be done 2x in successtion.  I would be interested in your text solution.  Please post.


All,
It seems I need to increase the points, as it appears that my requirements are more difficult that originally thought.

Alicia,

For what it's worth - My code may seem complicated but all you need to do is cut & paste into a new module, save it, then add an "Autoexec" macro in your db (if there isn't one already) with a line that calls the CompactCurrentDB() function. It will compact & reopen the db, everytime a user opens it. Or, like previously mentioned, you can set up a count until compact type of method.

Everything else is taken care of by the procedures.

Cheers,
Mike
Alicia, it is real simple.  Create a table, one field, call it 'NumTimesOpened', type number.  In your startup form, open a recordset, get the value of 'NumTimesOpened' and add one to it. update the record with this value, close the recordset.  I'll assume this form stays open for the entire Access session.  Then in the on close event of this form, open a recordset against the table, check the value.  If it is 7 or greater, update the value to 0, close the recordset, then call the compact routine, else do nothing.  You could call the compact routine twice if you want.
Alicia - It must go before.  Here is an exerpt from the help file that tells you how to use the /compact switch..

/compact target database or target Access project -  Compacts and repairs the Access database, or compacts the Access project that was specified before the /compact option, and then closes Access. If you omit a target file name following the /compact option, the file is compacted to the original name and folder. To compact to a different name, specify a target file. If you don't include a path in target database or target Access project, the target file is created in your My Documents folder by default.
In an Access project, this option compacts the Access project (.adp) file but not the Microsoft SQL Server database.
 
I have read it and re read after you comment and realize it was my fault (English is second language).  Here is how your shortcut target should look..

"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" c:\FolderWithDatabase\db1.mdb /compact

Now.  This will open compact your database, but it will not open your database.  What you want to do is maybe create a batch file that will run this command line then open up the db.  Try this batch file..

ECHO OFF
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" c:\SRCTemp\db1.mdb /compact
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" c:\SRCTemp\db1.mdb
EXIT

Then just set a shortcut to that batch file.  Good luck and sorry for confusion.  /Atropa
Mike,

I didn't realize your solution was that simple (I tried reading through it...but half of it went over my head).

So, I did as you said -- pasted into a new module, used AutoExec to run the code and although it seemed to work compacting, opening....it continued to do so in a loop.  Why did this happen?  Can you adjust/check the code?

Does it matter what version of Office, hence, visual basic is required?  The users for my database will all have Access 97 on their PC, however, our company is sllllooooooowwwwllllyyyy upgrading to XP. Some users have Office 2000 as well.  Are there any issues with administrative rights when you run a vb script?

Please let me know.  I'll continue to work through the other solutions, but if yours can work, I'd rather not make another table, unless it is best to do so.

AliciaVee
:) Woops! Don't know what I was thinking... Of course if you put it in the autoexec it will loop over and over!

BRB
woops is about right GM!!  I hate it when that happens.

:) Jack
ASKER CERTIFIED SOLUTION
Avatar of GringoMike
GringoMike
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Don't worry about how long it is, or what it does, just cut&paste then call it from your autoexec. Remember to set the csCnt counter. If you want it to compact everytime it's opened then set it to 1.

Jack - That's one for the books eh? A database looping through a compact cycle!! haha stupid me :)

Cheers!
For the developer who needs to make sure that database is as TIGHT as you can get it....
I laugh at myself, BUT, I just did it again!!!

<If you want it to compact everytime it's opened then set it to 1>

Make that 2 Alicia, if it's set to 1 then it will start the loop again!!!

Geez... I'm calling it a day :)
Coffee always helps me through those rough spells...
Alicia:  hurry up and test and award before Mike hurts himself...
LOL! I needed that...
A few of those in a day can give a programmer Cerebral Tunnel Syndrome  :)
Ha ha ha...I'm rolling on the floor here.  You guys are nuts.

Okay -- I want to test this...but I'm being pulled into a meeting.  Am glad to see there is an option for me to try.  I'll give Mike some more time (more coffee) just in case he spots something else.  Not!  Just kidding.

Stay tuned...be back tomorrow,

AliciaVee
Mike,

I pasted the code into a test database (not my application design master -- yet).  First, it works great.  Very cool.  But, to ensure I won't have anything blow up on folks PCs, can you give me input on the following:  

1. I noticed 2 new files (I created a folder for this database, which when rolled out, will also be required on users PC).  One was a MS DOS file and the other a VBscript.  Are you aware of any issues with VBscript files being stored on corporate PCs? (I am not part of IT, just a lowly worker who got volunteered to build a database because I showed Access skills -- go figure?)

2. Are there any disk space requirements?  I first ran it on my defalut network drive (all users have this setup) and got an error 'Not enough Disk Space'.  This may not be a problem, because I will require the users to save this on their hard drive (due to active directory, and IT using an auto backup to c:/mydocuments -- this causes issues with copying replica IDs)

3. Is there a way to edit the code, so that when it compacts, it does it 2x.  Ideally, replicated databases need to be compacted 2x in succession.

4. When I open the database for the first time, it opens Access full screen, and the database minimized.  However, when it hits "2", after opening, the compacting utilitie runs, and then reopens Access in a half screen, and also the database on half screen.  It may not affect my app since I have the switchboard opening in center screen, but could I prevent this if this causes a problem?

Thanks so much -- I think this is going to work!

AliciaVee

Hey Alicia,

"I am not part of IT, just a lowly worker who got volunteered to build a database because I showed Access skills -- go figure?)"

THAT'S HOW IT ALL STARTS! You'll be an expert on here in no time :)

1 - The two files are created to do the compacting. The VBScript file is used to close the current database and activate the batch file. The batch file compacts the database and reopens it. I am not aware of any issue with "VBscript files being stored on corporate PCs".

2 - The disk space requirements are the same as if you were compacting a db manually. MS Access creates a new compacted db (from the original), then deletes the old one once it finishes. To answer your question - Yes, there needs to be enough temp space to create the new db before deleteing the old one. That is not due to the coding I provided.

3 - "Ideally, replicated databases need to be compacted 2x in succession." How come? I'm not aware of any reason why a replicated db needs to be compacted 2x in a row, but I don't use them much. If you want the procedure to do this then I will have to change the code.

4 - I've never experienced that myself, but... To prevent that from happening you could add a line in your Autoexec macro to "Maximize" the db when it opens, or in your switch board.

Glad it's working! Let me know about the 2x, although I don't know why you would need to.

Cheers,
Mike
Mike: I think they call that being the Alpha-Geek...

:) J
Mike,

Got it.  Thanks for the suggestions.

Yes, can you please provide the code to do this 2x, but only if you feel it will not affect the users PC, or temp memory or anything 'geeky' like that :)

I couldn't find the reference to compacting replicated databases, but since I own about 10 Access books (no lie) and several whitepapers, I know this to be true as I have read it many times.

I will up the points for the adjustment in code -- but will have to do a separate question since I can't seem to up this one (is 500 the max?)

Thanks,
AliciaVee
Hey, for more points? We do anything for more points on here!!

Yea 500 is the max :( If you decide to open a new Q, post the link here. It's up to you.

I'll be back...
Ok it's pretty simple, switch the function below with the one in your db. So you know, the only difference between this one and the old one is the duplicate line in the middle.  Easy eh!

--------
Sub CreateBatchFile(strDB$)

Open strPath & csTmpFile For Append As #1
    Print #1, "CD " & Application.SysCmd(acSysCmdAccessDir)
    Print #1, "START /W MSAccess.exe " & strDB & " /compact"
    Print #1, "START /W MSAccess.exe " & strDB & " /compact"
    Print #1, "MSAccess.exe " & strDB
Close #1

End Sub
-------

Give'er a go...

Mike
Mike,

Sorry I have not yet given you these points (or the extra for the additional solution).  Got pulled into another project, but should be done sometime today.  Then, I will test your newest code.

Just an update,
AliciaVee
No prob... It'll be easy, all you have to do is duplicate one line :)

Mike
Mike...

Worked great.  Thanks very much!

Here are the additional points for part two of this request (adjusting code to compact 2x)


http://www-tcsn.experts-exchange.com/questions/20741275/Points-for-GringoMike.html

Thanks again!

AliciaVee
Glad to help!
Mike!

Help.  I need your assistance to fix the wonderful code you gave me.

Here is what happened.

I had to create a new replica for my users, and redistributed this replica as the final during the rollout.

But, in my haste (makes waste, right?) I used replication manager and let it name my file as the default:

Replica of 3TS

Some of my users are getting errors when the auto compact code runs, and it didn't happen until now.  Previously, the file name was "3TSReplica" and the compact code worked like a charm.

In reading your code, I noticed this:

'GET APP NAME & PATH
strApp = Dir(CurrentDb.Name)
strPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(strApp))

and, I think what is happening is that it is reading the file name as:
Replica.mdb

because I was in the process of showing someone how the auto compact code works, and I saw the errors, and one (of like 6 that popped up) said something like:
"Cannot find file Replica.mdb"

So, if this is correct, is there a way to hard code the file name in?  Since all users are now using the correct file "Replica of 3TS" then if I can do this, it would work (I am hoping).

Or, is there a way to say, IF, Then, to check the name, and if it is "Replica of 3TS" then hard code if, if not then use the LEN code...

Points await you in another emai...so please respond if you can, as I'll need to post this ASAP in hopes that someone can help.

Thanks in advance,
AliciaVee
Nice code GringoMike, but when I use it I get the following error: -2147024894 (80070002) Method 'Run' of object  'IWshShell3' failed, what is the problem/error

Br
Niels