Compact a Database From Another Database

I'd like to create a program that I can run at the end of each day, that will compact all the databases our department uses, and copy them to a backup disk.

Is there code I can use to compact another database from my current database?  

SendKeys "%tdc"

Will compact the current database, but can I use this for other databases somehow?

ssteeves
LVL 1
ssteevesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cyber_BeberCommented:
you can use:

dbengine.CompactDatabase "Source Database","Destination Database"

You cannot use the same path for the destination and Source database.

You can use a temporary name for the Destination database and after running the compact command erase the Source database (Kill "source database") and name the destination database to the source database name (Name "Destination Database" As "Source Database".
0
RRRCommented:
Hi, ssteeves.
Try to use CompactDatabase method:

DBEngine.CompactDatabase olddb, newdb, locale, options, password

You should create some table in which you have all databases that you need to compact (path of database) and use this code:

dim rs as recordset

Set rs = currentDb.OpenRecordset("select distinct MyDBPath from tblName", dbOpenSnapshot, dbReadOnly)

While Not rs.EOF
strDbOld = path of location of db
strDbNew = Left(strDbOld, Len(strDbOld) - 3) & "tmp"
CompactDatabase strDbOld, strDbNew
Kill strDbOld
Name strDbNew As strDbOld
rs.MoveNext
Wend
 rs.Close

set rs = nothing

Good Luck
RRR.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
ssteeves,

  You might also want to drop over to FMS, Inc's web site.  They offer a scheduler, which can backup, compact, etc. databases at regular times.

  The product does nothing more then what you could do on your own, but it's already written and tested and may be cheaper in the long run.

  Their web site is www.fmsinc.com.

HTH,
JimD.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DedushkaCommented:
use something like this:

syscmd 602, "c:\MyFolder\MyDB.MDB", "c:\MyFolder\MyDB.MDB"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cyber_BeberCommented:
You can also use the following sub:

Sub CompactDatabase(Path as String)

   SysCmd 602,Path,Path

End Sub



0
Cyber_BeberCommented:
Dedushka,

Do you have any documentation other then the offical Microsoft documentation about SysCmd ?
If you do i'll be happy to have it.

0
RRRCommented:
Cyber_Beber.
Why do you think that Dedushka will be happy to give it to you?

Maybe he will give it to you in exchange for some points :))

RRR.
0
Cyber_BeberCommented:
Dear RRR,

I think you are missing the point. Exchanging knowledge is a privilege, if you will keep your approach - Keeping the knowledge to your self, you will not get far ...
I do not need the points to know I'm good in what I'm doing, And I think (hope) Dedushka is the same.
0
ssteevesAuthor Commented:
While all comments were good, this one seems to work best, and I don't have to bother renaming the files after.  

Thanks a lot!  
0
wesleystewartCommented:
ssteeves:

I put together a simple Access97 application that searches our server for .mdb and .mde files and compacts them at night while I'm home asleep and the server traffic is almost nill.  I'll be happy to send it to you so you can modify it to your needs.

Wes
0
JimMorganCommented:
Dedushka:  Both Cyber Beber and myself are on a quest to discover more about the SysCmd constants.  I don't understand why they are such a big secret.

Care to share in this quest?  If you have time.

Jim
0
wesleystewartCommented:
If you pull up the debug window, type "? " followed by the name of the constant, Access will give you the value . . .

For emample, "? acsyscmdaccessdir" (wihout quotes) will return 9

I suspect there's a way to loop through them and print all the numerical equivalents, but I thought using named constants was a way to get away from memorizing what all those numbers meant . . .

Wes
0
wesleystewartCommented:
Poking around the object browser will also show constants and their respective values.

Wes
0
JimMorganCommented:
Wes, I don't think that we are talking about the standard constants for SysCmd.  MS only provides 13 constants (1 - 13)  acSysCmdAccessDir to acSysCmdUpdateMeter.  It's the hidden and undocumented constants that we want to know.  There are somewhere over 600 different constants for calling SysCmd.

For example, 603, MDBName, MDEName will create an MDE out of the MDB.

             504, 16483 will auto compile and save all modules programmatically.

Jim
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
JimM,

  It would be nice to know all available syscmds. However they are difficult to determine.  

  With each new version, I loop through syscmd(), error meesage, the object browser, and the .exe to look for undocumented things.

  With syscmd, it's easy to tell when you have a hit, but trying to find out what that command does is very difficult.  It can be easy somtimes (like 888 - magic eight ball), but that happens rarely.

Good luck on your quest...you're going to need it<g>.

JimD.
0
wesleystewartCommented:
Hidden and undocumented?  Surely Microsoft hasn't snuck anything in there without telling us about it.

You know, there are some websites out there that aren't quite as concerned with maintaining a professional decorum like EE.  A lot of times you find some posts from smart people out there who have deconstructed a partiuclar Access behavior or feature and posted the nuts and bolts of it.  I'm sure hidden syscmd constants are out there somewhere on somebody's site.  

Why, I found out how to bypass Jet 4.0 security just the other day . . .

Wes
0
DedushkaCommented:
Hi all.
Sorry, I was away last few days and didn't see most of comments.
About SysCmd 602:
I found this in some of Russian conferences and unfortunately have no any info about a list of SysCmd constants from Microsoft.

Regards,
Dedushka
0
JimMorganCommented:
I've busted my butt searching for more information on SysCmd on the web.  I don't know how many sites I've looked at but no luck so far.  I have found quite a few German sites but they don't have an English version.

JimD:  Magic 8 ball doesn't work anymore.

I know we have a insider working at Microsoft on EE, Mike Blazack.  If he would only sneak on a copy of the complete SysCmd function set, it sure would be nice.  The few I've know about have sped up some of my 'making ready to ship' routines.

JimM
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.