Compact access database using code

  • Thread starter Thread starter Tangz
  • Start date Start date
T

Tangz

I would like to compact a large access database from another database
using VBA. Can anyone tell me how this can be done ?

thanks
Thangam
 
strBackEnd = "path to back end file"

strToFile = "c:\myData.bak"

DBEngine.CompactDatabase strBackEnd, strToFile
' copy the compacted backup to production
FileCopy strToFile, strBackEnd


When you compact, you must send the compacted mdb to a different file..and
then overwrite...

You should put in error trapping for the compact...in case it fails...you
don't want to run the filecopy code...
 
I want to do something that starts with compacting: With the DB open, I want
to compact it, then create a compressed file of it (Windows compressed
folder) and then send that zipped file to my FTP site (password protected).
How do I do these 3 things in one script?
 
Brossyg said:
I want to do something that starts with compacting: With the DB open, I
want
to compact it, then create a compressed file of it (Windows compressed
folder) and then send that zipped file to my FTP site (password
protected).
How do I do these 3 things in one script?
Click to expand...

First, to do the compact..*and* run code, you have to be running a split
database. On the other hand, you find developing an application VERY
difficult if you don't split. So, this much solves the 1st problem of
compacting. So, split your database. It will not only allow you to compact,
but also allow you update your software.

I explain the concept of splitting here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

So, split. You be MUCH better able to compact, and also much better able to
develop an application. Just make sure you close all tables before you
compact.

And, since the compact command actually forces you to send the compacted
results to another file name...this is just what we need, and the next step
is to zip the database.

I have a ms-access win zipping utility here:

http://www.members.shaw.ca/AlbertKallal/zip/index.htm


And, for a nice ftp utility, we have one here:

http://www.mvps.org/access/modules/mdl0037.htm

And, if you want a really simple ftp, you can use this one:

http://www.mvps.org/access/modules/mdl0015.htm

I prefer the 1st module and library for ftp, as you can put the code in a
module...and not have to shell out...
 
Thank you. Could you advise the required code in the form that send to a
zipped file that uses a specific file location (existing file to be zipped
will always be the same .mdb file and the location to zip the file to will
also be the same)? In other words, I would like to initiate this from a
command button rather than using a fomr because I don't need the
functionality of browseing to find the file.

Thanks,
 
From the web page:
----
Click to expand...
To zip a file, use

MakeZip strFileName, strZipArchiveNameToMake
---
Click to expand...

So, in code, just go:

dim strFromFile as string

dim strToZipFileName as strng

strFromfile = "full path and name to your compacted mdb file"
strToZipFileName = "full path anme file name of zip file to create

MakeZip strFromFile, strToZipFileName
 
Thanks...do the 2 DLL files still need to be in the same directory as the
Access application?
 
Brossyg said:
Thanks...do the 2 DLL files still need to be in the same directory as the
Access application?
Click to expand...

Yes. However, you are also supposed to be able to put the two dll in

c:\windows\system

However, it just tested the code..and that "feature" does not work.....

So, I will post a fix for this bug....

Thus, as it stands right now...you must place the two zip files in the same
directory as the mdb/mde file...
 
Yes. However, you are also supposed to be able to put the two dll
in

c:\windows\system

However, it just tested the code..and that "feature" does not
work.....

So, I will post a fix for this bug....

Thus, as it stands right now...you must place the two zip files in
the same directory as the mdb/mde file...
Click to expand...

Albert, in a up-to-date Windows (i.e., since Windows 2000, or for
the last 8 years) you can't assume that your users will have
permission to store something in the system folder. Users should be
running not as administrators, but as users, and they don't have
write access to that folder.
 
Albert, in a up-to-date Windows (i.e., since Windows 2000, or for
the last 8 years) you can't assume that your users will have
permission to store something in the system folder. Users should be
running not as administrators, but as users, and they don't have
write access to that folder.
Click to expand...

yes, the user was asking about the two .dll's included with my example.

The choices are:


Have the user place the two .dll's in the windows
system folder, or system32 folder. In that case, then
my code should just run.

place the two .dll's in the same folder as the mdb (or mde)
file. This is preferred by me, as then it is a zero install
(xcopy). My zip code uses a windows api's to "load" the .dll's
(this is why I they don't need to be register, or placed in
the windows folder).

I suppose the 3rd alternative is to supply a "installer" for my WinZip
sample. This is quite easy to do, and I use inno all the time. So, I could
force the issue..but, then again, I don't really want to place the two dll's
in the persons system folder via a sneaky install..

The way it is now is no install need take place, but the downside is that
the two dll's must be in the same dir as the mdb (or mde) to function. In
fact, my code *should* work if you copy the two dll's into the windows
system folder, but my code incorrectly *still* looks for the two dll's in
the mdb folder...and if not found..it should simply try to run anyway just
in case they are in the system32 folder...
 
The way it is now is no install need take place, but the downside
is that the two dll's must be in the same dir as the mdb (or mde)
to function. In fact, my code *should* work if you copy the two
dll's into the windows system folder, but my code incorrectly
*still* looks for the two dll's in the mdb folder...and if not
found..it should simply try to run anyway just in case they are in
the system32 folder...
Click to expand...

Can't you put it in the Application Data folder under the user's
profile and have your app look there?
 
Can't you put it in the Application Data folder under the user's
profile and have your app look there?
Click to expand...

Hum...yes..I could do that. My current thinking is the system32 is good, as
then any mdb can run the zip code (I not tried syste32 due to my "bug", but
if dll's are in system32..then I should not even have to register the .dlls,
the api's should work, and I don't even have to use the "loadapi" that I use
now). And, of course, right now, I just assume the "same" dir as the mdb....

If I place it in the "user" Application Data folder, then I have to pull
that path name from somewhere for this to work (certainly doable..and
something I might just try) But, I would need to get the path name.

the system32 dir is nice because I don't have to do anything. and, if I use
the "same" dir as the mdb/mde, then I can use:

currentproject.Path

Which makes obtaining the current dir very easy...
 
Hum...yes..I could do that. My current thinking is the system32 is
good, as then any mdb can run the zip code (I not tried syste32
due to my "bug", but if dll's are in system32..then I should not
even have to register the .dlls, the api's should work, and I
don't even have to use the "loadapi" that I use now). And, of
course, right now, I just assume the "same" dir as the mdb....

If I place it in the "user" Application Data folder, then I have
to pull that path name from somewhere for this to work (certainly
doable..and something I might just try) But, I would need to get
the path name.
Click to expand...

The only variable is the profile name, and that's gettable with a
Windows API call. There's code for this on the Access web that gives
you access to all the standard folder locations. It's much better to
rely on that than on environment variables.
the system32 dir is nice because I don't have to do anything. and,
if I use the "same" dir as the mdb/mde, then I can use:

currentproject.Path

Which makes obtaining the current dir very easy...
Click to expand...

Do you understand the point of the Application Data folder? It's for
holding shared libraries used by apps that are not installed by a
user with administrative permissions.
 
Back
Top