scurvylion
asked on
Compacting an Access 2007 Database Using VBA
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
https://www.experts-exchange.com/questions/20733340/Use-Shortcut-Compact-and-then-open-database.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:
Big thanks to GringoMike for this code - if you are around please respond.
Thanks,
scurvylion
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
https://www.experts-exchange.com/questions/20733340/Use-Shortcut-Compact-and-then-open-database.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 SubBig thanks to GringoMike for this code - if you are around please respond.
Thanks,
scurvylion
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you try checking the compact on close option in the access properties? by chance?
compact.bmp
compact.bmp
ASKER
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
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
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.
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
(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
ASKER
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.
...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
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
ASKER
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:
That worked great! Thanks!
One last question - how do I keep the MS Access warning from appearing?
This keeps appearing when the database closes:
ASKER
ASKER
@ 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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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?
you can make the db do a compact and repair when it reaches a certain size.
ASKER
@capricorn1
how do I do that using the built in option?
how do I do that using the built in option?
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?
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.
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.
>> 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.
Yes absolutely.. I agree with conagraman on that... you should be sure it is not in use at the time.
<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
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
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
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.
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.
ASKER
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.
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.
<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.
then, just set the Compact on close property as suggested by conagraman.. no need to use codes.
ASKER
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?
<But what about the queries that run in the day>
how are this queries ran?
how are this queries ran?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<But what about the queries that run in the day>
how are this queries ran?
ASKER
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).
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).
ASKER
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
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
ASKER
Thanks again.
scurvylion
scurvylion
Change this line: wsh.Run strPath & csTmpScript
to this
wsh.Run Chr(34) & strPath & csTmpScript & Chr(34)
and see if that works for you.