Compact on Close. How to trap err if another user on? 1

[jetspin]

Hi.

What is the error number and in what event do I trap for the error that I have read is displayed if you set your database to compact on close and there is another user on the database?

A side question,,,, when this happens, I assume it will attempt compact on the next close until no other user is on the database?

I have been trying to create this err, but you have to add some data to reach a threshold to cause it.

Thanks!

 

[Quehay]

Are you experiencing errors? The LDB file usually takes care of this--the mdb won't be compacted unless there are no other users in there. I have sometimes seen Access goof and create the new target mdb file (it creates a new copy of your mdb in the compact process and kills the old one) and leave it there when it realizes it can't kill the old file due to user presence.


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[jetspin]

Quehay,

Thanks. No I am not experiencing errors. I want to implement this feature and read in a book (which as we know is not always correct ) that when a user tries to close db to compact it and other users are on it, he/she will get an error. So I thought I would try to trap this to prevent the users seeing an error, but it appears that Access is somewhat smart enough to handle it. I'll go ahead and set the close on compact and let Access handle it.
Thanks for the reply.

Jetspin

 

[Quehay]

*You'll get errors if you try to compact in code while the db is open.

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com

 

[evalesthy]

On a related note: I was looking at faq705-2852 which sets the flag to CompactOnClose. However, in that example you have to hardcode a database size which is inefficient as database will legitimately grow in size and number will have to be reset.

I have many users and I don't like the CompactOnClose running (takes time & resources) every time a user closes. (even though it won't compact in other users logged in).

What I was thinking, is - is there a way to determine thru code the last successful (successful meaning no other users were logged in and compact ran to completion) compact date (and maybe compact size) and then trigger the code in the faq mentioned above to run only if last size/date warrant it?

 

[Quehay]

Here's a routine that will return the size of the backend for a linked mdb (easily modified for one file). It uses the Microsoft Scripting Runtime to get FSO object.

You can keep a MAX_FILE_SIZE constant in a module, db properties, or a table and check against that to determine whether to flag compact on close.

*I was told once that the compact forces a re-optimization of queries on the next use, but this would probably only apply to queries in same file as data.

[tt]
Public Function BackEndFileSize() As Single
On Error GoTo Error_BackEndFileSize

Dim db As DAO.Database
Dim TDF As DAO.TableDef
Dim strBackEndFile As String
Dim FSO As New FileSystemObject
Dim sngFileSize As Single
Dim sngFileSizeinK As Single
Dim sngFileSizeinM As Single
Dim File As File

BackEndFileSize = 0
Set db = CurrentDb

For Each TDF In db.TableDefs
If Len(TDF.Connect) Then
strBackEndFile = TDF.Connect
strBackEndFile = Right(strBackEndFile, (Len(strBackEndFile) - InStrRev(strBackEndFile, "=&quot))
Exit For 'One table is sufficient
End If
Next
Set File = FSO.GetFile(strBackEndFile)
sngFileSize = File.Size
sngFileSizeinK = (sngFileSize / 1024)
sngFileSizeinM = (sngFileSizeinK / 1024)
BackEndFileSize = Round(sngFileSizeinM, 2)

Exit_BackEndFileSize:
Set FSO = Nothing
Set TDF = Nothing
Set db = Nothing

Error_BackEndFileSize:
RespondToError "BackEndFileSize", Err.Number, Err.Description, "Form Setup Failed"

End Function[/tt]

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com

 

[evalesthy]

Quehay: Thanks for the post above. I have a general question. It is possible that users have uninstalled or disabled Windows Scripting Host which the above routine requires - any good way to handle that?

 

[Quehay]

When you send a copy of the front end out it will keep all your library references, and only someone with access to the VBA editor can change that (so always lock down design access, switch off bypass key, and make an MDE if possible).

Some libraries will bomb if the user's machine doesn't have the dll or the proper version. The MDAC, responsible for ADO version, is notorius for this, and simple, basic VBA functions will throw errors.

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com