compact database in VBA access 2007

Hello,

I have recently upgrade to office 2007 from office 2003. To compact a 
database from within the database itself, I used the follwing code. 
Unfortunately it no longer works in access 2007. Is there some similar code 
that will work?

Public Function FncCompactTheCurrentDB()
   CommandBars("Menu Bar"). _
   Controls("Tools"). _
   Controls("Database utilities"). _
   Controls("Compact and repair database..."). _
   accDoDefaultAction
End Function

Thank You, 
SL
0
Utf
1/29/2010 1:34:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

5 Replies
2374 Views

Similar Articles

[PageSpeed] 38

On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft.com>
wrote:

It's not recommended to compact yourself. Rather, split the database
into front-end and back-end, store the back-end on the server, and
schedule a task to compact it ever so often, for example using the
/compact switch to msaccess.exe.

-Tom.
Microsoft Access MVP


>Hello,
>
>I have recently upgrade to office 2007 from office 2003. To compact a 
>database from within the database itself, I used the follwing code. 
>Unfortunately it no longer works in access 2007. Is there some similar code 
>that will work?
>
>Public Function FncCompactTheCurrentDB()
>   CommandBars("Menu Bar"). _
>   Controls("Tools"). _
>   Controls("Database utilities"). _
>   Controls("Compact and repair database..."). _
>   accDoDefaultAction
>End Function
>
>Thank You, 
>SL
0
Tom
1/29/2010 5:55:41 AM
I know what you mean. I have some databases where I do periodically compact 
them. But in this case, I prefer to do it once in code because I have many of 
these back-end databases in different locations. The data is imported and 
used as a reference. The data is not likely to change.

Thanks.

"Tom van Stiphout" wrote:

> On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft.com>
> wrote:
> 
> It's not recommended to compact yourself. Rather, split the database
> into front-end and back-end, store the back-end on the server, and
> schedule a task to compact it ever so often, for example using the
> /compact switch to msaccess.exe.
> 
> -Tom.
> Microsoft Access MVP
> 
> 
> >Hello,
> >
> >I have recently upgrade to office 2007 from office 2003. To compact a 
> >database from within the database itself, I used the follwing code. 
> >Unfortunately it no longer works in access 2007. Is there some similar code 
> >that will work?
> >
> >Public Function FncCompactTheCurrentDB()
> >   CommandBars("Menu Bar"). _
> >   Controls("Tools"). _
> >   Controls("Database utilities"). _
> >   Controls("Compact and repair database..."). _
> >   accDoDefaultAction
> >End Function
> >
> >Thank You, 
> >SL
> .
> 
0
Utf
1/29/2010 4:09:02 PM
But what you posted as working in Access 2003 only compacts the front-end, 
not the back-end!

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"SL" <SL@discussions.microsoft.com> wrote in message 
news:FFE46594-C886-45CF-9F97-818AF7A1776A@microsoft.com...
>I know what you mean. I have some databases where I do periodically compact
> them. But in this case, I prefer to do it once in code because I have many 
> of
> these back-end databases in different locations. The data is imported and
> used as a reference. The data is not likely to change.
>
> Thanks.
>
> "Tom van Stiphout" wrote:
>
>> On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft.com>
>> wrote:
>>
>> It's not recommended to compact yourself. Rather, split the database
>> into front-end and back-end, store the back-end on the server, and
>> schedule a task to compact it ever so often, for example using the
>> /compact switch to msaccess.exe.
>>
>> -Tom.
>> Microsoft Access MVP
>>
>>
>> >Hello,
>> >
>> >I have recently upgrade to office 2007 from office 2003. To compact a
>> >database from within the database itself, I used the follwing code.
>> >Unfortunately it no longer works in access 2007. Is there some similar 
>> >code
>> >that will work?
>> >
>> >Public Function FncCompactTheCurrentDB()
>> >   CommandBars("Menu Bar"). _
>> >   Controls("Tools"). _
>> >   Controls("Database utilities"). _
>> >   Controls("Compact and repair database..."). _
>> >   accDoDefaultAction
>> >End Function
>> >
>> >Thank You,
>> >SL
>> .
>> 


0
Douglas
1/29/2010 5:08:02 PM
Yes, but on first use of this back-end, I use it as a front-end. I import 
data by running various macros, queries, VBA code all from one or more 
buttons on a form. I will probably go back into it (as-required) to correct 
or update the data. The last button runs this code which compacts the 
database. It is very handy to have a button that will compact the database as 
required rather than periodically.

SL

"Douglas J. Steele" wrote:

> But what you posted as working in Access 2003 only compacts the front-end, 
> not the back-end!
> 
> -- 
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
> 
> "SL" <SL@discussions.microsoft.com> wrote in message 
> news:FFE46594-C886-45CF-9F97-818AF7A1776A@microsoft.com...
> >I know what you mean. I have some databases where I do periodically compact
> > them. But in this case, I prefer to do it once in code because I have many 
> > of
> > these back-end databases in different locations. The data is imported and
> > used as a reference. The data is not likely to change.
> >
> > Thanks.
> >
> > "Tom van Stiphout" wrote:
> >
> >> On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft.com>
> >> wrote:
> >>
> >> It's not recommended to compact yourself. Rather, split the database
> >> into front-end and back-end, store the back-end on the server, and
> >> schedule a task to compact it ever so often, for example using the
> >> /compact switch to msaccess.exe.
> >>
> >> -Tom.
> >> Microsoft Access MVP
> >>
> >>
> >> >Hello,
> >> >
> >> >I have recently upgrade to office 2007 from office 2003. To compact a
> >> >database from within the database itself, I used the follwing code.
> >> >Unfortunately it no longer works in access 2007. Is there some similar 
> >> >code
> >> >that will work?
> >> >
> >> >Public Function FncCompactTheCurrentDB()
> >> >   CommandBars("Menu Bar"). _
> >> >   Controls("Tools"). _
> >> >   Controls("Database utilities"). _
> >> >   Controls("Compact and repair database..."). _
> >> >   accDoDefaultAction
> >> >End Function
> >> >
> >> >Thank You,
> >> >SL
> >> .
> >> 
> 
> 
> .
> 
0
Utf
2/2/2010 7:55:02 PM
Try it from the command line. See
http://www.codeforexcelandoutlook.com/blog/2009/06/clean-and-repair-your-ac=
cess-database/
for sample code (scroll down to the comments).

--JP

On Jan 28, 8:34=A0pm, SL <S...@discussions.microsoft.com> wrote:
> Hello,
>
> I have recently upgrade to office 2007 from office 2003. To compact a
> database from within the database itself, I used the follwing code.
> Unfortunately it no longer works in access 2007. Is there some similar co=
de
> that will work?
>
> Public Function FncCompactTheCurrentDB()
> =A0 =A0CommandBars("Menu Bar"). _
> =A0 =A0Controls("Tools"). _
> =A0 =A0Controls("Database utilities"). _
> =A0 =A0Controls("Compact and repair database..."). _
> =A0 =A0accDoDefaultAction
> End Function
>
> Thank You,
> SL
0
JP
2/2/2010 8:31:00 PM
Reply:

Similar Artilces:

Accessing Windows Office Templates
There are some very nice templates at the Windows Office Template Gallery. I would like to download (mostly Word, but a few Excel) some of these templates, but the format comes out with a ".cab" extension that Office v.X doesn't understand. Is there any way to get these templates? Unfortunately, they make the Mac template archive look very weak. TIA Len Hi Len, Microsoft has a Mac template download page: http://www.microsoft.com/mac/resources/templates.aspx?pid=templates There's a link to the on-line gallery on that page. It looks like a newly re-designed gallery to m...

Access 2003 resets form variables during debugging while the form is open
Hello, I am using MS Access 2003. (FWIW, this is about an ADP project, not MDB.) A form has a variable that is initialized (by some function call) in Form_Load() and then stays unchanged for the form's entire lifetime. (In a language more advanced than VBA, it would be a constant, not a variable; unfortunately, in VBA I cannot initialize a constant by non- constant expression). Specifically, in my case it looks like this: Private sTempFileName as String ... Private Sub Form_Load() ... sTempFileName = GenerateTempFileName(...) ... End Sub Priv...

How to link with an Access DB
Hi, I have an Acces DB with many tables. I need to choose the name of a customer in a cell of Excel. For example, in acces I have this tables: Table1 Id Name Last Name City I need to choose the last name from a drop down menu in a spreadsheet and then in other cell I need to put all the data regarding the last name that I choose. I hope to be exaustive, and sorry for my english. :-) Many Thanks Stefano ...

Access 97 can't resize database window
My database window with the listings of forms tables etc was adjusted to a smaller width, but resizing it is completely disabled and renders Access 2007 utterly useless for me. Is there anyway to 'reset' the window? ...

exporting outlook 2000 pst files to a database
I need to export outlook 2000 to a database- attachments and all. Is there a simple way to do this so you can search and retreive messages and attachments in a database form? Thanks Jeff You can export individual folders to access or excel to have them available for searching. I have never tried to export my entire .pst file to access or excel however. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Jeff <laacid@yahoo.com> asked: | I need to export outlook 2000 to a database- ...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

OWA. CA's. and Exchange 2007
The other day, I had to renew the Certificate on my Exchange 2007 server. Everything seemed to go alright, but eventually I received complaints that outbound email wasn't being sent. Looking at EMC showed all of the email queued up and waiting for something. After I relaxed the settings on my router (Allowed all outbound SMTP instead of restricting it to the Exchange server sending to MxLogic) all of the outbound email went though. I also was receiving reports of Webmail being broken (which was halfway fixed with the help of http://www.microsoft.com/technet/support/ee/transform.aspx?Pr...

How do I get Powerpoint 2003 fade in 1 by 1 in 2007
I frequently used the 'fade in one by one' feature in Powerpoint 2003, which allowed me to fade in individual bullet points by way of a click of a mouse. Not only is this feature now not standard in 2007, but I can't work out how to create it: anything that I find, tends, instead, to fade the bullet points in on some kind of automatic time schedule, rather than - as I want - on a mouse click. I want only to use text, no sounds, no pictures, and nothing fancy. Currently, I am reduced to copying my material into an old Powerpoint 2003 presentation, but this is silly....

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

Outlook 2007 addresses
How can I find the list of addresses I have sent emails to? When you start a new email you can put a letter in the To line and list will come up. Where is that list? How can I print it out? Thanks Matt that is the autocomplete cache. You would need a utility to print it out. http://www.outlook-tips.net/archives/2004/20040621.htm http://www.slipstick.com/problems/nicknamecache.asp -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Out...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Outlook 2007 Install gone wrong
We recently switched e-mail hosts and I successfully (mostly) switched over my company's accounts from a POP server to our new IMAP server. But I'm trying to set up one more account and am running into issues. I put in all the same settings as for our other accounts, but it won't allow me to create or copy any sub-folders into the InBox folder for this account (it tells me it cannot and this is "probably due to a limitation of your imap server")...but I can do so for the other accounts on the same server. Also, when I try to view the Imap Folders, it says "The...

users with restricted access
We have some users that we have directed to only get their information from a report that has been set up. Because of that, I set up a parameter query to make the information more easy to see. The parameter query prompts for last name or broker #, is there a way, when the last name is entered to include Jr's & Sr's? Or should this be another field in the table to make the last name field more accurate? ...

Access 97/2000/2003 comparisons
I have a rather large application that uses an access 97 database (DAO). We also have a version that works with access 2000, Oracle, msde, and SQLServer (ADO). We would like to retire the DAO version of the product, but there are places where the ADO version is much slower compared to where we run DAO seeks (very noticeable when looping). So, I have a few questions. 1. Are there any tips/tricks to speed up ADO queries to compare with DAO seeks? 2. Would there be any benifit in using access 2003 over previous versions of the software besides the added features (xml support, etc). 3...

no access to send internet email
Hi, Exchange 2003. Can a mail enabled user be restricted to send internal email only and not internet email? Nich Hello, > Exchange 2003. Can a mail enabled user be restricted to send internal email > only and not internet email? yes, that is possible. There are multiple ways to archive this goal. Depents on your infrastructure,too. If you have mailing to external over an SMTP Connector, try this: First look into your SMTP Connector, you will find Delivery Restrictions there. It is self explaining. If you have trouble with that or you won't find it, then i will need additi...

Backing Up Exchange Database
Hi I am running Exchange 2003 on a Windows 2000 server. Veritas Backup 10, is installed on the server. The Exchange Agent is also installed on this server. A full backup with the option to clear committed logs is used to perform the daily exchange backups. I noticed that in the MDBDATA folder the logs are continuously being added approximately 5MB in size. Is there a way to have these logs removed automatically as I thought that the backup would perform that function. Thanks On Thu, 5 Jan 2006 17:18:02 -0800, microsoft <microsoft@discussions.microsoft.com> wrote: >Hi >...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Installed Font does not display in Word 2007
Only 6 of of 8 recently installed TTF fonts will display in Word 2007 Are the missing ones variants like Italic or Bold? On Dec 25, 1:28=A0pm, GWHA <G...@discussions.microsoft.com> wrote: > Only 6 of of 8 recently installed TTF fonts will display in Word 2007 ...

Backing up databases on remote SQL
Hi, I recently followed one of the MS articles on how to move GP 9 sql database to a different server. Everything went well except when trying to run the backup option from the GP9 server. I currently have a server with GP9 installed and another server with SQL and the GP databases. When I try to run the backup from the backup option on the GP server it says " You must be on the server machine to perform this task". presume this is because the SQL is no longer on that server. The SQL server has a backup routine of its own, but I would like our finance users to have the ability...

VBA form
Im using a form/macro that i linked to through another thread and a tailoring it to my needs. I basically have it doing what i need but cant get a button to do what i want. When the user starts the macro i brings up the form with a message and 2 buttons (Continue/Cancel). Whe continue is pressed i want the message on the form to change to aniothe message and so on until all messages are shown. Ive attacehed what i working on and apprecaite any help offered. Thank yo +------------------------------------------------------------------- |Filename: Excel.zip ...

Share Calender in Outlook 2007
In our organization, we have changed the the default data file path. This makes the "Share a Calender" option disappear. First of all, is there any anything to work around this? Secondly, if there is nothing that can be done in the above case, I tried sharing the calender by publishing it in Office Online. Apparently the calender is shared but when I sent it to my colleague's work e-mail address, he is unable to access it. It asks for authentication and cannot contact the web server when he enters his credentials. How to make this work? I don't want to go sharing my W...

Excel 2007 Line Chart
Hello, Is it possible to configure a line chart in Excel 2007 to ignore the intervals and graph straight to the next value. For example if I have the periods: Jan with the value 1000 Feb with the value 900 March with the value 500 April with the value 0 May with the value 0 June with the value 0 I want the March value to drop directly from 500 to 0 ignoring the interval to April, I do not want a curved line it must drop directly to zero then the line is straight across to April. I have no idea if this is possible, any ideas? Thanks, Brett On Tue, 11 Oct 2011 15:40:45 -0700 (PDT), TyreDu...

upgrading from Access 97 to Access 2007
I designed a database (forms, reports, et al) several years ago in Access 97 and it continues to work well. I've just received a MS Office Professional 2007 and wonder if it is time to migrate my data into the new program. I've attempted two or three times to do this in Access 2002 and I keep getting error messages along the line of "...corrupted file..cannot be opened by Access 2002 (or some such message)..." The help radio button opens up a box which suggests I do some programming in Virtual Basic to perform my transfer task. The easy way out, of course, is to sti...

Can not open additional mailboxes in my mailbox after a exchange 2003 rebuild database
To Exchange 2003 Gurus I have a exchange server that serve a lot of users. 3 week ago I had to repair the database with great success. But now I noticed a problem that in the past I was able to acomplished I can not open additional mailboxes in my mailbox.(right click on my mailbox>properties>advanced>Advanced tab> and when I click on add I get THE NAME COULD NOT BE RESOLVE. THE ACTION COULD NOT BE COMPLETED. This message appear eventhough I did not type anything Here is more info -I am able to open additional mailboxes if I use a computer with my profile that I created before I r...