By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

compressing an access database

P: 5
I have a ms access database that I have split into a front and backend database.

After the user enters data into the database (stored in the backend), the DB grows to about 50 MB.

The user then runs the application (VB module stored in the front end DB) and the backend database grows to about 1.6 GB. When the database is closed it returns to a size between 50-100 MB



The performance of the application slows as the database grows in size which is why I need to compress it.



If the application is stopped part way and the DB is closed and re-opened and then the application is restarted it performs well until the DB grows again. When I tried to perform a compress via VB as a module from the front end DB, I get an error that the backend DB is not in exclusive mode



What I would like to know is how do I close the backend database when it is open and linked to the front end database to allow me to run the compress and then allow me to re-connect it to the front end database



Or does someone have a better suggestion on how to free up the space in the DB without doing a compress?
Jun 12 '07 #1
Share this Question
Share on Google+
12 Replies


JConsulting
Expert 100+
P: 603
I have a ms access database that I have split into a front and backend database.

After the user enters data into the database (stored in the backend), the DB grows to about 50 MB.

The user then runs the application (VB module stored in the front end DB) and the backend database grows to about 1.6 GB. When the database is closed it returns to a size between 50-100 MB



The performance of the application slows as the database grows in size which is why I need to compress it.



If the application is stopped part way and the DB is closed and re-opened and then the application is restarted it performs well until the DB grows again. When I tried to perform a compress via VB as a module from the front end DB, I get an error that the backend DB is not in exclusive mode



What I would like to know is how do I close the backend database when it is open and linked to the front end database to allow me to run the compress and then allow me to re-connect it to the front end database



Or does someone have a better suggestion on how to free up the space in the DB without doing a compress?

These kinds of questions are difficult to answer, because if we tell you the answer you want...we really aren't helping you with the actual problem.

I can only assume that your database makes extensive use of temp tables. Anytime a temp table is written to, and subsequently cleared out..the space it took to store it is still allocated by the database. Closing and reopening the database with a compact and repair will handle this as you've already found out.

In order to be able to do this, you will have to get all of your users to log out. Generally, unless your DB is approaching 2gb or there is a performance hit to your application, you should perform any kind of maintenance during a nightly process.

Let us know your thoughts...and hopefully you won't have to ask your users to exit the database just because it's growing.
J
Jun 13 '07 #2

ADezii
Expert 5K+
P: 8,636
I have a ms access database that I have split into a front and backend database.

After the user enters data into the database (stored in the backend), the DB grows to about 50 MB.

The user then runs the application (VB module stored in the front end DB) and the backend database grows to about 1.6 GB. When the database is closed it returns to a size between 50-100 MB



The performance of the application slows as the database grows in size which is why I need to compress it.



If the application is stopped part way and the DB is closed and re-opened and then the application is restarted it performs well until the DB grows again. When I tried to perform a compress via VB as a module from the front end DB, I get an error that the backend DB is not in exclusive mode



What I would like to know is how do I close the backend database when it is open and linked to the front end database to allow me to run the compress and then allow me to re-connect it to the front end database



Or does someone have a better suggestion on how to free up the space in the DB without doing a compress?
Post the code in the VB Module which causes the size of the Backend Database to drastically bloat to 1.6 GB. I think here is where the prioblem lies.
Jun 13 '07 #3

P: 5
The code is doing as expected. It is analysing the data and finding the best scenario which uses tables to hold the temporary data.

I only have one user using the database and this is the person starting the application via the front end database.

do you know of a way to disconnect the backend database from the front end to allow me to close the backend database to then programmatically run the compress function?
Jun 13 '07 #4

JConsulting
Expert 100+
P: 603
The code is doing as expected. It is analysing the data and finding the best scenario which uses tables to hold the temporary data.

I only have one user using the database and this is the person starting the application via the front end database.

do you know of a way to disconnect the backend database from the front end to allow me to close the backend database to then programmatically run the compress function?
closing the front end, allows you to do this.

Otherwise, you're talking about creating a re-link subroutine for each of your linked tables.

This isn't a difficult thing really...it involves creating a table with all the linked table names in it...deleting the links.

You can use this to compact the back end
Expand|Select|Wrap|Line Numbers
  1. Dim strfile2 As String <your external db path and name
  2. Dim strfile3 As String <string to hold db name in which to compact
  3. strfile2 = "your external db path and filename"
  4. strfile3 = Left(strfile2, InStrrev(strfile2, ".")-1) & "cr.mdb"
  5. DBEngine.CompactDatabase strfile2, strfile3
  6. Kill strfile2
  7. Name strfile3 As strfile2
  8.  
then relink the tables using the TransferDatabase command

J
Jun 13 '07 #5

JConsulting
Expert 100+
P: 603
closing the front end, allows you to do this.

Otherwise, you're talking about creating a re-link subroutine for each of your linked tables.

This isn't a difficult thing really...it involves creating a table with all the linked table names in it...deleting the links.

You can use this to compact the back end
Expand|Select|Wrap|Line Numbers
  1. Dim strfile2 As String <your external db path and name
  2. Dim strfile3 As String <string to hold db name in which to compact
  3. strfile2 = "your external db path and filename"
  4. strfile3 = Left(strfile2, InStrrev(strfile2, ".")-1) & "cr.mdb"
  5. DBEngine.CompactDatabase strfile2, strfile3
  6. Kill strfile2
  7. Name strfile3 As strfile2
  8.  
then relink the tables using the TransferDatabase command

J
This function will delete all the linked tables GIVEN THAT YOU create a table with all their names in it and use THAT in this function

Expand|Select|Wrap|Line Numbers
  1. 'FUNCTION:  DeleteListedTables
  2. 'PURPOSE:   Deletes all tables in the delete list.
  3. 'RETURNS:   True if all listed tables were deleted, otherwise False.
  4. 'EXAMPLE:   DelStatus = DeleteListedTables("tbl_ListOfTables")
  5. '
  6. Function DeleteListedTables(ByVal TableList$) As Boolean
  7.  
  8.     On Error GoTo DeleteListedTables_Err
  9.     Dim DB As DataBase, rs As Recordset
  10.     Dim TName$
  11.  
  12.     Set DB = CurrentDb
  13.     Set rs = DB.OpenRecordset(TableList)
  14.     Do Until rs.EOF
  15.         TName = rs!TableName
  16.         DB.TableDefs.Delete TName
  17.         rs.MoveNext
  18.     Loop
  19.     DeleteListedTables = (err = 0)
  20.  
  21. DeleteListedTables_Err:
  22.     Exit Function
  23.  
  24. End Function
  25.  
using the same loop above, replacing the Delete line with this...you can relink

Expand|Select|Wrap|Line Numbers
  1. DoCmd.transferdatabase acLink, "Microsoft Access", "YourBackEndPathAndName", acTable, TName, TName, False
  2.  
Hope it helps!
J
Jun 13 '07 #6

ADezii
Expert 5K+
P: 8,636
The code is doing as expected. It is analysing the data and finding the best scenario which uses tables to hold the temporary data.

I only have one user using the database and this is the person starting the application via the front end database.

do you know of a way to disconnect the backend database from the front end to allow me to close the backend database to then programmatically run the compress function?
What exactly does this analyzing process involve, perhaps a better algorithm could be created such as storing the holding the data in Arrays as opposed to creating Tempory Tables. I would think that this would be a less costly and more efficient approach.

Consistently deleting and re-creating Links on External Tables is really not a good idea.
Jun 13 '07 #7

P: 5
thanks. I inserted the code and I can rebuild the links

what I have done is deleted the links
inserted the compress DB code
rebuild the links

I am still getting database is unavailable for exclusive use when trying to do the compress
I put
Set DB = DBEngine(0).OpenDatabase("T:\spacial_be.mdb")
db.close

before the compress but that did not help.
Can you suggest how I can close the backend database to let the compress run
Jun 13 '07 #8

P: 5
we looked at using arrays but the amount of data points exceeded the number of entries we could put in multi-dimensional arrays. This is why we opted to put the data in tables during the calculation process.

There may be risk in breaking a dn making links, but we are willing to take the risk as the program currently takes 5 seconds to perform one iteration when the database is small. When the database becomes large we are looking at 3 minutes per iteration. Since we have near 10,000 iterations to do stopping and compressing the DB is the best solution.

Our data analysis is done only once on a set of data, so we don't mind waiting a few hours for it to crunch the numbers, but we can't wait days.

We are constantly looking at how the code works to see if we can streamline it more, but right now it works well and at an acceptable speed when we are under 1,000 iterations which is the bulk of our processing. But we need to handle the larger volumes.
Jun 13 '07 #9

ADezii
Expert 5K+
P: 8,636
thanks. I inserted the code and I can rebuild the links

what I have done is deleted the links
inserted the compress DB code
rebuild the links

I am still getting database is unavailable for exclusive use when trying to do the compress
I put
Set DB = DBEngine(0).OpenDatabase("T:\spacial_be.mdb")
db.close

before the compress but that did not help.
Can you suggest how I can close the backend database to let the compress run
You can probably Close the Back End Database via Automation but, this would be a dangerous thing to do.
Jun 13 '07 #10

JConsulting
Expert 100+
P: 603
thanks. I inserted the code and I can rebuild the links

what I have done is deleted the links
inserted the compress DB code
rebuild the links

I am still getting database is unavailable for exclusive use when trying to do the compress
I put
Set DB = DBEngine(0).OpenDatabase("T:\spacial_be.mdb")
db.close

before the compress but that did not help.
Can you suggest how I can close the backend database to let the compress run
If you're running this from your front end. Make sure that all forms are closed (forms that would use any table from your back end).

If you are certain that nothing is open (causing the lock file on the backend) then you need to go and manually delete the .ldb file because somehow it's been locked open.
J
Jun 13 '07 #11

P: 5
thanks you were right. I had the switchboard form in the BE. I moved it to the front end and now I can unlink all my tables, compress the DB and then re-link


thanks for all the help
Jun 15 '07 #12

JConsulting
Expert 100+
P: 603
thanks you were right. I had the switchboard form in the BE. I moved it to the front end and now I can unlink all my tables, compress the DB and then re-link


thanks for all the help
You're very welcome!
J
Jun 15 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.