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
2325 Views

Similar Articles

[PageSpeed] 9

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:

Close Event for the Database (?)
Is there a close event for the database? I have forms referencing other forms, and if the user closes Access without backing out of the forms, an error message pops up. It would be hand to just trap that error in an "On close" event for the entire db. Of course that might be a horrible thing when other errors are happening on close.... -- croy ...

Excel as a database #3
is there any possibility that i used to print a invoice through excel for my product sale. is there any way excel can generate monthly sales book as a database. Please show me any path Thanks in advance. Rao Ratan Singh Rao, Consider Access for this project. To do this properly, you'll likely need a table for your products (your inventory or product line), one for your invoices, one for the items in your invoices, and if you're keeping customer information, one for your customers. Excel is terrific, but a lot of darn work to implement related tables, move records around, ...

access 2003 (sp3) The program stops responding
Hi When I change something i a form (design view) ,the program stops responding for while. Not used network connection are disconnected. Hodde <Hodde@discussions.microsoft.com> wrote: >When I change something i a form (design view) ,the program stops responding >for while. Not used network connection are disconnected. Check your table links. You might have some tables pointing to a network share. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://ms...

Permission probs w/Split database for Access97 & Access2003 Multi-
I've successfully split an Access 97 database and set up an identical front end in Access2003. I've verified multiple times that my user group permissions are defined identically for the tables in the back end as well as the tables and all other objects in both versions (97 & 2003) of the front end. However, with the Accesss 2003 front end, I've determined that users in Groups with no Administrator nor "Modify design" rights to any database objects can if fact change object permissions. I don't have the problem with the Access97 front end nor the back end...

Read/Write to offline database
Hi all, Does anyone know of any 'recommended' ways of reading/writing to the MSCRM database when in offline mode? I need to perform lookups on the data in there, and maybe even update/create new records. TIA, Tim Young Tim, If your talking about going directly against the MSDE datastore, I would HIGHLY discourage this. Look into the SDK, as they did provide some information about cutomsizing the Outlook client with the June upgrade to it (though I haven't dug into it specifically). If you update the MSDE datastore directly, you risk those updates not making it back into t...

How do I find Outlook 2007 archive folders that disappeared?
I set up archive folders and could view them, stored onto a spearate hard disk.Today, when Outlook loaded, the archive folder group has disappearedd from view and I do not know how to find/restore.Any ideas? Check again in Folder Tree View Otherwise, within Outlook, File>Open>Data File.........browse to the location and open the Archive If the location is an external drive, if that drive is not connected when you start Outlook, OL will complain "Nigel" <Nigel@discussions.microsoft.com> wrote in message news:5564BC1C-D07C-4820-8CC4-C585F6E7DB5D@microsoft.com... >...

Outlook Web Access #2
I have Exchange 5.5 SP4 and Outlook Web Access installed on the Member Windows NT 4.0 Server. If the regular domain user logs in to Outlook Web Access he can see a list of mail messages but when he opens any of them they look like empty, but actually they are not. If user the member of Administrators Group logs in to the Outlook Web Access he is fine. What should I change? ...

HELP Error: Trying to populate userform fields from access databas
Hi All, I have been struggling with this since long, I would appreciate if anyone can help me with getting this done. Basically I want to pull information from access database and populate my excel userform fields. For eg: If I input a Student Id field I want to populate the Name and Phone number for that student from access database, Here is the code that I have so far, But it gives me errors.. Please guide me through this Private Sub StudentId_AfterUpdate() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String strSQL = "SELECT Name, Phone FROM...

indirect accessing to cells
Hello, I use data of few thousands records (cells) which are all arranged in sequential order at single column. I need to extract specific cells' content, and I have no idea what function I can use for. For example, assuming I use a table (single column) of 100 records and I need to extract every 5th cell content to rebuild a new column. Any suggestions ? Tnx Dudi Enter in cell 1 of a column assuming data is in column A =OFFSET($A$1,5*ROW()-5,0) Copy down as far as you wish. Gord Dibben MS Excel MVP On Tue, 27 Jan 2009 22:38:19 +0200, "Dudi" <ardudi@hotmail....

Changing Text Box Size and Location on a form, at run time with Access Xp
Hello All, Is it possible to change the size of the text box (or any other control) on a form at run time, by Drag and drop or resizing borders using the mouse events. (Just like the design mode but the user will use it at run time on form view). Thanks in advance... You should be able to resize in your code if necessary. Me.control.Visible = True Me.control.Height = 1000 Me.control.Width = 1000 Don't forget to set them back when you are done. "Developer" wrote: > Hello All, > Is it possible to change the size of the text box (or any other control) on > a ...

Designing Report Troubles
Hi, We just tried to customize a report with Visual Studio 2003 We followed the instructions from the book Working with Dynamics CRM 3.0 from MS Press. We downloaded the reports, we set up a project,.. ... not made any changes yet .. ... but the report is not able to preview and brings up the following error "No connection to Adventure_works_cycle" even we made a connection to sa .. and the connection was tested successfully. Why is the report drying to use a database that is not installed? (I know what the adeventure works cycle database is :-)) Why is AWC named in the "...

Get information in Oracle Database
Hi, How To get informations in Oracle Database with CRM? Is this possible? Thnaks Hi Gilles, when you say "get information", do you mean get information into an Oracle DB, or get it out of an Oracle DB? You can certainly do both. You may want to refer to the CRM SDK on MSDN for specifics on how to build custom integrations with MSCRM. Also search MSDN for CRM post callouts. If you have more specific questions about what you're trying to accomplish, then we can probably suggest more specific approaches. Mike "Gilles T." <gillest0000000@hotmail.com> wrot...

CRM causing Outlook 2007 to crash
My outlook constantly crashes on me if I try to open random CRM linked emails, or I get message that says "This item cannot be opened". Here is the message I get when it crashes: Faulting application outlook.exe, version 12.0.6212.1000, stamp 46e03e45, faulting module outlook.exe, version 12.0.6212.1000, stamp 46e03e45, debug? 0, fault address 0x00013ca0. I have wiped this PC, and reinstalled everything (XP, Office 2007, CRM) and the error still occurs. Any help here would be appreciated. thanks How much memory does the client have? Ive seen some odd Outlook behaviors on cli...

Excel 2007; graph the best fit line from a regression analysis?
Try using this white space to ask you question with a bit more detail best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mattz" <mattz@discussions.microsoft.com> wrote in message news:574CCC66-72EA-44D0-8D36-2147B6C7C279@microsoft.com... > ...

Cannot access OMA page
I've read through a number of the threads here, but haven't been able to find an answer. Apologies in advance if this has already been solved. Would appreciate a pointer to a thread if so. Environment: Windows Server 2003 Advanced SP1, up-to-date (DCSRV) Exchange Server 2003 SP2, up-to-date (EXCHSRV) Basically I can't get ActiveSync/OMA to work, so I started by trying to access the OMA page through a browser. I'm seeing the following behavior when I try to access OMA: Browser (IE6) displays: "Unable to connect to your mailbox on server EXCHSRV. Please try again ...

Access and Daye Formats
Is there any way to make Access overrife the computer regional settings for date formats. I am having problems with mdbs using Canadian date formats DD/MM/YYYY when installed on systems that have US date formats in regional settings MM/DD/YYYY. A lot of Canadians it seems simply leave the US defaults on preinstalled Windows and it causes dates to be screwed up. Any help appreciated. Thank you. Ray Ray, take a look at: International Date Formats in Access at: http://allenbrowne.com/ser-36.html It discusses the 3 cases where Access is likely to misunderstand our d/m/y date set...

How to recover/shutdown an exchange database?
How does one recover an exchange database (edb file) from to dirty-shutdown state to a clean shutdown state. I have tried "eseutil /r" on a dirty-shutdown edb file, which reports success but actually does nothing. Is there a way to programmatically recover (transit to the clean-shutdown state) an exchange database? Thanks in advance for any advise. Faithfully, Sean B. Durkin On Mon, 30 May 2005 12:57:16 +1000, "Sean B. Durkin" <sdurkin@{removeThis}siliconrose.com.au> wrote: >How does one recover an exchange database (edb file) from to >dirty-shutdown...

Exits after compacting
I have a database which in the last week has become slower and slower to save changes. This morning I tried Compact and Repair, whereupon it closed the database. Will it have left any hints anywhere as to the problem it has encountered please? Many thanks Peter Kinsman I suspect you have at least some corruption. First, always work on a copy of the database. Working on the original may make it impossible for a repair service to fix it. Download a copy of JetComp.exe: http://support.microsoft.com/default.aspx?scid=kb;EN-US;273956 Try backing up your forms as text with the undocu...

Unhandled execption in ariel.exe(APIOCX):0xC0000005 :Access violation
Can you help me to find out the mistake i am doing while calling the dll............... i am really thankful to you for your time and support.... "Unhandled execption in ariel.exe(APIOCX):0xC0000005 :Access violation" whenever i debug my code i got this error.... Dll function : short setQuoteToDB( BSTR* strDateTimeStamp, short* intMarketNo, BSTR* strMarketName, BSTR* strBid, BSTR* strAsk, short* intMarketState); code: typedef int (CALLBACK* Set_Quote_DB) (unsigned short**, short*, ...

What happened to website templates from Pub 2007 to 2010?
I just installed MS Office with Publishers 2010 on my computer and discovered that there is virtually no mention of website templates? Did I miss something or is MS dropping this? On Thu, 3 Mar 2011 19:56:08 -0800 (PST), geedc <geedc1@gmail.com> wrote: >I just installed MS Office with Publishers 2010 on my computer and >discovered that there is virtually no mention of website templates? >Did I miss something or is MS dropping this? Using Publisher for websites has been long deprecated, and with the release of Office 2010, support for using Publisher for web pages has essentia...

Accessing Windows Using Handles
I am a beginner at MFC programming and I am working on an application that uses buttons on a Dialog to activate other applications or processes. However, once the application or process is initialized the Dialog is terminated. The user can re-initialize the Dialog and choose to start any application or process. If the user selects an application that is running, the system should not start a new process but it should bring the application running to the top window. I have been able to use PSAPI to enumerate all the processes running and detect if the process is running. Could you plea...

Database tables from other SQL DBS not showing in smartlist builder
I am having problems with smartlist builder in that I can list the other SQL DBS but when I select one non of the tables or views show. Thanks rob Rob, You will need to make sure you grant security to the DYNGRP sql group. The same GRANT.SQL utility script will do. Best regards, MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "rob.paddock@sundaysolutions.net" wrote: > I am having problems with smartlist builder in that I can list the > other SQL DBS but when I select one non of the tables or views show. > > Th...

outlook 2007 reply signature
i have a signature set to reply to messages but the original message appears to the side of my signature instead of below Why? Thanks Simon ...

Excel 2007 03-10-10
Question: Can Excel arrange items in 'alpaphabetical' order all at once if items are all put into one column after a person is through inputting them? Or must everything be input in 'alphabetical' order? Thank you. Dave __________ Information from ESET NOD32 Antivirus, version of virus signature database 4932 (20100310) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Click Data, Sort David Dyke wrote: > Question: Can Excel arrange items in 'alpaphabetical' order all at once if > items are al...

Export Excel data to Access
I have an Excel wookbook template that I use to create new project files to track and manipulate data on individual projects. I also have an Access database where I record some common data as the spreadsheet resulting in duplicate data entry. How can I export data easily (i.e. novice programming experience)on a regular basis from these separate Excel files to a common Access table to eliminate this duplicate data entry? Thanks, Jim H. Jim, initiate the process from Access. Open your Access database and File > Get External Data > Import. Set Files of type to Microsoft Excel, then ...