[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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
0
ssteeves
Asked:
ssteeves
  • 4
  • 4
  • 3
  • +4
1 Solution
 
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)PresidentCommented:
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DedushkaCommented:
use something like this:

syscmd 602, "c:\MyFolder\MyDB.MDB", "c:\MyFolder\MyDB.MDB"
0
 
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)PresidentCommented:
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now