• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

In Code, close one database and open another

I have a very large database, which I want to compact on close SOMETIMES.

I know that there is the "Compact on Close" option in the tools of the database, but was hopping that I could do this through code so that I could prompt the user to be sure that they want to compact now.

In trying to solve this problem, I have found that I can compact a closed database through code. I therefore created a second very small database, which I was able to code to prompt the user and (if answered yes) compact the first database.

So my problem now is how do I close the first large database, and open the second smaller database in code?

In asking fellow developers, it was suggested that I create a batch file to open the first database and then the second.

I believe that there is a cleaner way to do this inside of Access.

Please help, knowing the total story above if anyone could suggest a totally different approach, I would be interested in that as well.

Thank you,

Michael
0
MADamiano
Asked:
MADamiano
  • 2
  • 2
1 Solution
 
mgrattanCommented:
You might want to try an add-in from Trigeminal Software.  It's free, and it has the ability to open other databases, compact them, and then re-open them or do some other process.

http://www.trigeminal.com/utilities/TsiSoon90.zip
0
 
LambertHeenanCommented:
To close the current db and open another in code you can do this...

Const strCompactorMDB = "X:\SomePath\SomeDatabase.mdb"
Dim strCommandLine
Dim dbl_D as Double

  strCommandLine = Quote(SysCmd(acSysCmdAccessDir) & _
     "MSACCESS.EXE") & " " & Quote(strCompactorMDB) & _
     " /cmd " & Quote(CurrentDb.Name)
   dbl_D = Shell(strCommandLine, vbNormalFocus)
   Application.Quit acQuitSaveNone ' close the current database

... Note the line contiumation characters in the above code

Helper function...

Function Quote(aString) As String
    Quote = """" & aString & """"
End Function

The /cmd switch for Access allows you to pass an arbirary parameter on the command line. In this case we are passing the name of the current database.

Then your compacting database (named in strCompactorMDB) can retrieve the name of the database to compact using the command() function. That way the user does not need to enter tne name of the database to compact.
0
 
MADamianoAuthor Commented:
What a simple great solution!
I just tried it out.
Fantastic, Thank you.
0
 
LambertHeenanCommented:
Thanks for the compliment. Glad to have helped.
0
 
MADamianoAuthor Commented:
Thanks for the help.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now