Scheduled run of Compact-Repair off hours

[techexpressinc]

There should be a way to run an "auto-marco" as in Excel that does the Compact and Repair that can be put on an XP-Scheduler to run nightly.

If anyone has this process working let me know.

Thx

Russ

Russell.Neuman @ LFG.com

I will post one if I figure it out.

 

[boblarson]

Create a new database which can then be opened by the scheduler and have an AutoExec macro kick off this code using the RunCode action:

[B]Public Function CompactDb(strDbPathAndName As String)[/B]
[B]    Dim strDbNameOld As String
    Dim strDBNameNew As String[/B]
[B]    Dim fso As Object
    Dim f As Object
    [/B]
[B]    Set fso = CreateObject("Scripting.FileSystemObject")[/B]
[B]    Set f = fso.GetFile(strDbPathAndName)[/B]
[B]    strDbNameOld = f.Path
    strDBNameNew = f.ParentFolder & "\New_" & f.Name[/B]
[B]
    DBEngine.CompactDatabase strDbNameOld, strDBNameNew
    Kill strDbNameOld
    Name strDBNameNew As strDbNameOld
End Function[/B]
[B]

[/B]

You should probably put an error handler in it and maybe have it copy the database as a backup too just in case the process fails partway through.

 

[techexpressinc]

Re: Scheduled run of Compact-Repair off hours - more

Thanks Bob - do you have other ideas to keep our corruption occurrences to a minimum? We have had two corruptions within a week. Usually it runs about once ever 2 months.

The most troubled DB has 20 users, 12 local, 6 at another site and 3 home workers.

The database is split. 4k rows in the main table.

Russ

 

[boblarson]

Does each user have a copy of the frontend on their machine? How do the 6 at the other site and the 3 home workers connect up to the database?

 

[techexpressinc]

Re: Scheduled run of Compact-Repair off hours - moreinfo

6 remote users company's network

3 home users - their at home internet connection to a vpn tunnel

 

[techexpressinc]

Yes, they all have their own front-end.

And they are not developers or query writers, they just go through the a form.

 

[boblarson]

No, how do the other location and home users access it? Are they just opening the frontend on their machine and it is going over the Internet? This could cause problems. A Terminal Services session or Citrix would be the optimal for those users.

 

[techexpressinc]

The home users are just going over the Internet.

I will have to research what it would take to use a "A Terminal Services session or Citrix".

Thx for your help
Russ

 

[boblarson]

The home users are just going over the Internet.

That could be a big reason for the constant corruption.