compressing an access database

[jimdan]

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?

[JConsulting]

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

[ADezii]

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.

[jimdan]

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 programmaticall y run the compress function?

[JConsulting]

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 programmaticall y 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

Dim strfile2 As String <your external db path and name
Dim strfile3 As String <string to hold db name in which to compact
strfile2 = "your external db path and filename"
strfile3 = Left(strfile2, InStrrev(strfile2, ".")-1) & "cr.mdb"
DBEngine.CompactDatabase strfile2, strfile3
Kill strfile2
Name strfile3 As strfile2

then relink the tables using the TransferDatabas e command

J

[JConsulting]

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

Dim strfile2 As String <your external db path and name
Dim strfile3 As String <string to hold db name in which to compact
strfile2 = "your external db path and filename"
strfile3 = Left(strfile2, InStrrev(strfile2, ".")-1) & "cr.mdb"
DBEngine.CompactDatabase strfile2, strfile3
Kill strfile2
Name strfile3 As strfile2

then relink the tables using the TransferDatabas e 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

'FUNCTION:  DeleteListedTables
'PURPOSE:   Deletes all tables in the delete list.
'RETURNS:   True if all listed tables were deleted, otherwise False.
'EXAMPLE:   DelStatus = DeleteListedTables("tbl_ListOfTables")
'
Function DeleteListedTables(ByVal TableList$) As Boolean

    On Error GoTo DeleteListedTables_Err
    Dim DB As DataBase, rs As Recordset
    Dim TName$

    Set DB = CurrentDb
    Set rs = DB.OpenRecordset(TableList)
    Do Until rs.EOF
        TName = rs!TableName
        DB.TableDefs.Delete TName
        rs.MoveNext
    Loop
    DeleteListedTables = (err = 0)

DeleteListedTables_Err:
    Exit Function

End Function

using the same loop above, replacing the Delete line with this...you can relink

DoCmd.transferdatabase acLink, "Microsoft Access", "YourBackEndPathAndName", acTable, TName, TName, False

Hope it helps!
J

[ADezii]

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 programmaticall y 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.

[jimdan]

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).Ope nDatabase("T:\s pacial_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

[jimdan]

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.

[ADezii]

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).Ope nDatabase("T:\s pacial_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.

[JConsulting]

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).Ope nDatabase("T:\s pacial_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

[jimdan]

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

[JConsulting]

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