Solved

Compacting an Access 2007 Database Using VBA

Posted on 2010-09-08
32
683 Views
Last Modified: 2013-11-27
Hi,

I have some code that appends .csv files to an Access database and the files are quite large (400,000 records aprox). Since these files are appended weekly, part of the code agregates the data to fiscal months as soon as a fiscal period ends. However, since the size of an Access database is limited to 2GB, I have to make sure the database is very tight. This means I need to compact the database each time the code runs and a file is appended or a group of files are aggregated.

I was snooping around the site and came across this code written by GringoMike

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20733340.html?eeSearch=true

It looks like exacty what I need, however, when I use it I get the following error:

 -2147024894 (80070002) Method 'Run' of object  'IWshShell3' failed

and If someone can point out what I am doing wrong I would greatly appreciate it.

Here is GringoMike's code:
Option Compare Database
Option Explicit
Const csTmpFile As String = "Compact.bat"
Const csTmpScript As String = "Compact.vbs"

Public Function CompactCurrentDB()
Dim strApp$, strPath$

'get app name and path
strApp = Dir(CurrentDb.Name)
strPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(strApp))

'remove any left over tmp files
On Error Resume Next
Kill strPath & csTmpFile
Kill strPath & csTmpScript
On Error GoTo 0

'make batch file and script
Call CreateBatchFile(strPath, strApp)
Call CreateVBScript(strPath, strApp)

'run script
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.Run strPath & csTmpScript

End Function

Public Sub CreateBatchFile(strPath$, strApp$)
Dim sTmp$
sTmp = strPath & strApp

Open strPath & csTmpFile For Append As #1
   Print #1, "CD " & Application.SysCmd(acSysCmdAccessDir)
   Print #1, "START /W MSAccess.exe " & sTmp & " /compact"
   Print #1, "MSAccess.exe " & strPath & strApp
Close #1

End Sub

Public Sub CreateVBScript(strPath$, strApp$)
Dim sTmp
sTmp = strPath & strApp

Open strPath & csTmpScript For Append As #1
   Print #1, "Dim objApp"
   Print #1, "Set objApp = GetObject(""" & sTmp & """)"
   Print #1, "objApp.Quit"
   Print #1, "Set objApp = Nothing"
   Print #1, ""
   Print #1, "Dim wsh"
   Print #1, "Set wsh = CreateObject(""WScript.Shell"")"
   Print #1, "wsh.Run """ & strPath & csTmpFile & """, 0, True"
Close #1

End Sub

Open in new window


Big thanks to GringoMike for this code - if you are around please respond.

Thanks,
scurvylion
0
Comment
Question by:scurvylion
  • 12
  • 7
  • 5
  • +2
32 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 33628041
Try this, add quotes using chr(34)
Change this line: wsh.Run strPath & csTmpScript
to this
wsh.Run Chr(34) & strPath & csTmpScript & Chr(34)

and see if that works for you.
0
 
LVL 7

Accepted Solution

by:
shaydie earned 250 total points
ID: 33628491
Also.. as a side note.. I don't know why but in case you experience the same problem, I couldn't get the script to work at all until I added extra quotes around the paths in the creation of the bat and vbs files.. I changed these to what follows and got it to work.

Public Sub CreateBatchFile(strPath$, strApp$)
Dim sTmp$

sTmp = strPath & strApp

Open strPath & csTmpFile For Append As #1
   Print #1, "CD " & Application.SysCmd(acSysCmdAccessDir)
   Print #1, "START /W MSAccess.exe " & Chr(34) & sTmp & Chr(34) & " /compact"
   Print #1, "MSAccess.exe " & Chr(34) & strPath & strApp & Chr(34)
Close #1

End Sub

Public Sub CreateVBScript(strPath$, strApp$)
Dim sTmp

sTmp = strPath & strApp

Open strPath & csTmpScript For Append As #1
   Print #1, "Dim objApp"
   Print #1, "Set objApp = GetObject(""" & sTmp & """)"
   Print #1, "objApp.Quit"
   Print #1, "Set objApp = Nothing"
   Print #1, ""
   Print #1, "Dim wsh"
   Print #1, "Set wsh = CreateObject(""WScript.Shell"")"
   Print #1, "wsh.Run " & Chr(34) & Chr(34) & Chr(34) & strPath & csTmpFile & Chr(34) & Chr(34) & Chr(34) & ", 0, True"
Close #1

End Sub
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33628650
Did you try checking the compact on close option in the access properties? by chance?
compact.bmp
0
 

Author Comment

by:scurvylion
ID: 33629110
Hi conagraman,

I only want the compact to run when the VBA code runs which will be once a day. The database may be accessed many times in a day by multiple users so I don't want to compact it every time that it closes.

That's why I would like to use this sub routinue with my code.

@shaydie
I'm going to check out your solution right away and will get back to you asap.

Thanks
scurvylion
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33629188
according to the msdn website The compact and repair will only occur when the last person has closed the database. It does not occur when others have the database open.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33629313
First, is the DB Split?
(http://www.databasedev.co.uk/split_a_database.html)

1. Because there is no "stable" way to compact a multi-user database while it is in use.
(And for the reason conagraman stated above)

2. With the Front end (Forms, reports, Queries Only) on each users workstation, Splitting the DB will probably save you on both the 2GB limit.

;-)

JeffCoachman
0
 

Author Comment

by:scurvylion
ID: 33629359
The application has an Excel frontend which pulls data through SQL queries generated by combobox selections in the Excel application. So the only time the database is open is when a query is being processed. All aggregation and segmentatin is handled throught the SQL queries while additional calculations for performance metrics are processed in Excel.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33629389
...continued...

2. With the Front end (Forms, reports, Queries Only) on each users workstation, Splitting the DB will probably save you on the 2GB limit.

If the DB *IS* split, and either part is approaching 2GB, then compacting will only buy you a little time...
In other words, ...what happens when the compacted size of the DB is 2GB?
(Compacting the DB does not "Force" the DB to be below 2GB in size)

Things like corruption and sluggish performance may start to effect the DB way *before* the 2GB limit is reached.

You then should really look into moving the Back end to one of the SQL Server variants
(SQL Server Express is free)
http://www.microsoft.com/express/database/

;-)

JeffCoachman
0
 

Author Comment

by:scurvylion
ID: 33629629
Hey shaydie,

That worked great! Thanks!

One last question - how do I keep the MS Access warning from appearing?

This keeps appearing when the database closes:

0
 

Author Comment

by:scurvylion
ID: 33629658
didn't take the image - try again.

MSAccess2007-warning.jpg
0
 

Author Comment

by:scurvylion
ID: 33629687
@ Jeff

I'm pretty sure that I can keep the database under 2GB if I keep it compacted - yur right that if this isn't possible I'll have to look at SQL Server or some similar app.
0
 
LVL 10

Assisted Solution

by:conagraman
conagraman earned 150 total points
ID: 33629711
i would recommend you looking into using the built in option. it will compact your database in a stable way - also giving you the results you want.
0
 

Author Comment

by:scurvylion
ID: 33629980
Won't that compact it every time a query is issued and the database opens and closes? That could be hundreds of times a day.
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33630098
it shouldn’t matter. i dont think you will see too much of a difference. Also i believe the pros weigh out the cons. Would you rather the extra few milliseconds or a potential error every time you run your update?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33630131



you can make the db do a compact and repair when it reaches a certain size.
0
 

Author Comment

by:scurvylion
ID: 33630298
@capricorn1
how do I do that using the built in option?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Expert Comment

by:shaydie
ID: 33632392
Personally I don't really see anything wrong with the code.. the issue being whether everyone is logged out of the database or not. You could also simply use a batch file and compact the database using scheduled tasks at let's say 2 am every day if that worked for you too. I do something similar and it works just fine. What I do however is I make sure everyone is out of the database before compacting. I can't see the reason in compacting your database 100 times per day.. I certainly wouldn't want to do that. Besides it is a large database and takes time to compact.. at which point nobody would be able to access it.. It doesn't make sense to me. Is there something I'm missing here?
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33632434
Scurvylion

i would like to explain what is going on when access compacts/repairs the database.

Capricorn perhaps you can elaborate more on this and also give your opinion on if it is risky or not,  but from what i understand in the background Access is copying the database - creating a new compact database while deleting the original. Again from what i understand- if something goes wrong undesired effects may occur.

Using code is fine but i would want to try and find a way to do it when i could be sure it is not in use by anyone.


0
 
LVL 7

Expert Comment

by:shaydie
ID: 33632498
>>  i would want to try and find a way to do it when i could be sure it is not in use by anyone.

Yes absolutely.. I agree with conagraman on that...  you should be sure it is not in use at the time.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33633130
<I'm pretty sure that I can keep the database under 2GB if I keep it compacted >
Well if data is being constantly being added then this will soon become impossible...
OK, but as I said, if you are already close to 2 GB, you may experience other issue as well.

So as soon as you find that you "Must" constantly compact the database in order to keep it below 2gb, then you may already be fighting a losing battle. (and possibly introducing other abnormalities)

(If your 12 year old is 5 feet tall, then letting out the hem in his or her pants is only a temporary fix.)
;-)

Jeff
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33633314

when sql express is free it might be less of a headache just switching.

(If your 12 year old is 5 feet tall, then letting out the hem in his or her pants is only a temporary fix.)
:) classic
0
 
LVL 10

Expert Comment

by:conagraman
ID: 33633357
you know there maybe other approaches to what you are doing depending.

what program are you using to generate the csv files?  

if you could instead export your records to an excel spreadsheet you could link to the spreadsheet or spreadsheets using Access. this might solve your size issue.  of-course i cant know if this will work for you its just a thought.
 
0
 

Author Comment

by:scurvylion
ID: 33649390
The Access database will only ever hold at a maximum 12 fiscal periods of data + 5 weeks of data. The reason that I want to compact it is that at the end of each fiscal period, the 4 or 5 weeks of weekly data (depends on the length of thefiscal period) are aggreagated to a fiscal period level. Remember that I said the data is initaly added at the weekly level of ggregation. So I want to compact it to keep the size down so that I son't have any problems. I don't believe that given the structure I have developed that it will ever exceed 2 GB unless some additional functionality with regrd to the weekly data is added.

The data import will happen overnight so there shouldn't be any users when the data is updated and the database is compacted. I am unsure why the code would not do anything any differently than the built in feature since it uses a vbscript file to close the database and run a .bat file to compact it before opening it again.

Any idea of how I get rid of that Access warning or if it will be a problem??

Thanks.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33649446
<The data import will happen overnight so there shouldn't be any users when the data is updated and the database is compacted.>

then, just set the Compact on close property as suggested by conagraman.. no need to use codes.
0
 

Author Comment

by:scurvylion
ID: 33650075
But what about the queries that run in the day - won't they open and close the database when each query is issued and cause the database to be compacted multiple times daily?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33650362
<But what about the queries that run in the day>
how are this queries ran?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 100 total points
ID: 33650384
Yes, but this is why this is issue with your request.
1. Your database is near the 2GB Limit, but you state that splitting it or otherwise reducing the size is not possible
2. You want to compact the DB after certain code is run, again, you cannot compact a DB while other users are using it.
3. You cannot use compact on close because you are concerned with the amount of times this will happen per day.

As was stated, Setting Compact on close will only compact the DB when the last user exits.
So if you have 100 people in the DB, only when user 100 logs out, will this occur.

If you are saying that you are 100% positive that each user who opens the DB is the only one having it open, then I cant see how this would happen hundreds of times per day.
That would be an extreme coincidence.

In most cases (if the DB is as active as you say), many users will be in it at the same time, meaning that only after the very last user Exits would CR run.

Besides, Can you try it and see if this is even an issue?

If it were me I would seek to reduce the size of the DB.
Remember splitting the DB is only one technique.
You can divide all the tables into multiple Databases and link them all in one central DB.
So you could divide the the tables into 4 databases (each with a 2GB Limit) then link all four databases' tables to a 5th "Central" database.

Or you could trim your DB by removing any "Stored" images and replace them with "Linked" images.

You can also review your datatypes.
Don't use a Long Integer datatype where an Integer, or Byte would suffice.
The same goes for Single and Double datatypes.

Delete any unused objects
Delete any unused code.

Finally you could create a new DB and import all of the old objects into it.

;-)

JeffCoachman
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33650420




<But what about the queries that run in the day>
how are this queries ran?









0
 

Author Comment

by:scurvylion
ID: 33654721
The application has an Excel front end that runs different SQL procedures that are then sent to the Access database which then combines, aggregates and segments the data prior to importing it to the Excel application which then calculates additional performance related metrics for display in a visible sheet to the user.

So a query is run every time a different selection is made through the combobox selection choice combination (there are 7 different comboboxes that modify the SQL calls). There are multiple instances of the Excel front end installed on different computers so queries can happen at any time (but probably not during the night).
0
 

Author Comment

by:scurvylion
ID: 33665843
HI guys,

So I wanted to update the question here and let you know what I ended up doing.

After much deliberation, I ended up just useing the compact database on close. I ran some trial queries with that option in place and it seemed to run ok so I thought rather than risk screwing things up that I had better be safe.

In terms of allocating the points I want to recognize all of the contributions to this thread, however, since shaydie correctly answered my original question correctly, I'm going to give him the majority of the points with the rest spread between conagraman and boag2000.

If someone could answer my question about the pop-up window from Access when the database is closed, I would be most appreciative.

Thanks for all of your help.
scurvylion
0
 

Author Closing Comment

by:scurvylion
ID: 33665903
Thanks again.
scurvylion
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33667502
ok
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now