Compact and Repair Questions

[Design by Sue]

I thought I was home free - but not! Now I am getting and error number 7866 can't open because it is missing, opened exclusively by another user or is not an ADP file.

Sorry to be a pain but any idea why my apptemp.mbd would give this error message?

EDIT - ok found my problem there, now the code is triggering Case 1 MsgBox "Database is not an mdb/accdb file"

I am stumped, again.

 

[Frothingslosh]

The procedure will only compact files that are not in use at all - there can be no ldb or laccdb file for the targeted database. Without seeing your application, I would have to assume that your main menu pulls data from the backend, which would keep it open.

If that's the case, my suggestion would be to put the code in your exit button, and then have that button do the following:

1) Close the main menu
2) Pause 1 second

Endloop = DateAdd("s", 1, Now())
 
Do until Now() > Endloop
Loop

3) Now run the compact process.
4) Once that's complete, exit the application.

Also, keep in mind that this can only compact non-front end files. You can use it to compact the back end and the side end, but not the front end. However, by switching to use of the side end rather than internal temporary tables, you're also removing the need to compact and repair the front end at all.

 

[Design by Sue]

I changed .mdb to accdb and everything works fine. So once again - I think I am done here.

 

[Design by Sue]

Loop[/code]
Also, keep in mind that this can only compact non-front end files. You can use it to compact the back end and the side end, but not the front end. However, by switching to use of the side end rather than internal temporary tables, you're also removing the need to compact and repair the front end at all.

Yes and I am thrilled with the solution that the combination of responses from you both have come up with. I will put the compact code within my program so that once the user has completed his use of the table the compact will take place so the side end will not bloat.

 

[Design by Sue]

Final update (I sure hope) I put the code on the close of a form that the user has to close after the table in not in use and it is working exactly as I had hoped.

I have a person who tests my work so we will see if he can find any bugs in my solution. I sure hope not.

 

[Frothingslosh]

Glad to help!

 

[Galaxiom]

One of the easiest ways to make a Side End is by copying an empty database file that has already been set up for the job.

 

[Design by Sue]

This application will be on several machines. Since the link to the side end is programmed in the code I am wondering what is the best location for that side end. I am thinking to require installation of it on the root level of the c drive (C:\apptemp.accdb) so that it is a constant location. Does this makes sense? Is there a path (location) that would be better? Is there a way, perhaps through a button that the user could browse to the location and set it? I really need this to be simple, so I am thinking a consistent path that would be the same on all systems would be better, but would appreciate any insite.

 

[Galaxiom]

My preferred location is the user's AppData folder. They automatically have full permissions so the application can create a folder there.

%APPDATA% is an environment variable pointing to the Roaming section of the user's application data folder. Files here travel around the domain if that facility has been set up so it isn't perfect.

%TEMP% leads to the user's AppData\Local\Temp folder. This location does not roam with the user on a domain.

%USERPROFILE% will get you to their profile and you can then choose where you want it to be held.

 

[Design by Sue]

Great - so just to confirm if I put %APPDATA% in the parameter, if the side end was anywhere in their application folder (C:\Program Files on my system) Access would find it? Would it have to be on the root level of the folder?

For %TEMP% Would I then put the side end here: C:\Documents and Settings\SY\Local Settings??

Sue

 

[Galaxiom]

%APPDATA% is an environment variable. These are useful because can be used without regard for the username or any variation from the defaults normally used to set up the system.

In Windows7 systems set up with the default paths it points to: C:\Users\username\appdata\Roaming.

This folder is normally hidden.

If the system is configured with users set up on a different drive or partition then the environment variable will still find them.

The case if the same for other environment variables such as %PROGRAMFILES%, %SYSTEMROOT%, %WINDIR%,

 

[Design by Sue]

I have searched for environment variable and can't find any info on how to use it in Access. How would I use this info? If my side end is apptemp.accdb and I want to put it in somewhere in user's area?

EDIT did find something on environ function and came up with the following for the my documents folder

Select Case CompactSelectedDB (Environ("USERPROFILE") & "\My Documents" \ apptemp.accdb)

Am waiting for my cooworker to test it out (I am running XP on a Mac so the paths are different) and will post back if this worked - meanwhile if you see an error please let me know.

Thanks

 

[Galaxiom]

Use the Environ function to convert the environment variable to a path.

Then use that path when you create a new database or copy the fresh empty one to the location.

 

[Design by Sue]

I am back using this in another project and would like to use it on the backend of the database. I need it to work only on the backend which is already linked to the frontend rather than hard code the location of the datafile in the programming. Any suggestions on how to make this automatically compact the backend which is linked??

Thanks
Sue

 

[RainLover]

The need to compact 50 records will be required every 6 Months or more.

Just an educated guess. So I am thinking that this is a lot of time spent on something that is not overly important.

See the attached for an Automatic Backup and Compact tool. very good for development as you can quickly back up very often. I would suggest every 5 Minutes or when ever you make an adjustment.

You will have to set this up to find the right file and destination.

 

[Design by Sue]

Thank you RainLover. I downloaded your attachment and looked it over, but at this point, the code that was provided by Frothingslosh does exactly what I need it to do except I need to know how to automatically link it to the backend. This must be possible, I just don't have the expertise to do it. Can someone (perhaps Frothingslosh if you are there) look at page 1 of this post and help me do this?

Thanks
Sue

 

[RainLover]

Long time since I used this.

I think it still works.

The tables mean nothing. They are just there to link to.

 

[Frothingslosh]

Wow, good thing I just randomly looked in this thread - I'd unsubscribed some time back. Let me see which code I provided, and then look at page 1.

 

[Frothingslosh]

Okay, the version of the routine I provided you requires the full path to the back end. You can either pull that from your connection string, save it in a table somewhere in the DB (not really recommended), or just hardcode it in.

The easiest method would be to just hardcode the backend path in function call - CompactSelectedDb("L:\Path\ToThe\Backend\BackendName.mdb"), for example.

The BEST selection would be to create a procedure that cycles through the tabledefs looking for a non-empty connection string, then pull the path from that using this:

BackendPath = Split(Split(tdef.Connect, "Database=")(1), ";")(0)

(In this case, tdef is a tabledef object I was storing each tabledef in until I found one with a non-empty connect string. BackendPath is just the string variable I stored the path in.) Then exit the loop.

The looping thing is probably overkill in your case - unless you expect the database to be moved or your computers have their drives mapped differently, hardcoding should work fine.

Hell, you could even just take the name of a linked table and pull the path with this:

BackendPath = Split(Split(CurrentDb.TableDefs("TableName").Connect, "Database=")(1), ";")(0)

Any of those approaches will tie the code into the backend.

 

[Design by Sue]

Thank you! Through searching I found the same solution as you were posting this. Here's what I have working.

Dim strFullPath As String

strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tblName").Connect, 11)

Select Case CompactSelectedDB(strFullPath)

This seems to work even though it is slightly different than yours. I will try yours now and go with that since you wrote the original code, this would be a better way to go.

Sue