Remotely Compacting and Making Backups an Access Database
In my work setting I am responsible for the maintenance of a long running Access database. It's important to protect that data with regular backups and maintenance. Not having direct access to the machine I do this remotely. Here are some quick steps on how I do this.
You will need to set up SSH Server as an optional feature on the Windows 10 machine ( Goto Settings > Apps > Apps & Features > Manage Optional Features > Add a Feature > SSH Server)
For my environment I set up Powershell as the default shell ( https://docs.microsoft.com/en-us/windows-server/administration/openssh/openssh_server_configuration ). This isn't necessary.
Once I have that all set up I can remote in. First step is to make sure that no users are using the database via the command openfiles ( https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/openfiles ).
Next I run a slightly altered version of Daniel Pineault's CompactDB script from https://www.devhut.net/2018/02/22/ms-access-backup-a-database-using-a-vbscript/
One small change that I made is to his CompactDB function. In his script he copies the file and then compacts the the backup file here:
Call CompactDB(sBackupFolder & "\" & sDBFile & "_" & sDateTimeStamp & "." & sDBFileExt)
I changed it to compact the source database as so:
Call CompactDB(sSourceFolder & "\" & sDBFile & "." & sDBFileExt)
This makes much more sense to me, since you want to keep your working file compacted and in good condition. It also works very well with his script since you backup the database before you compact the original this way.
M.
You will need to set up SSH Server as an optional feature on the Windows 10 machine ( Goto Settings > Apps > Apps & Features > Manage Optional Features > Add a Feature > SSH Server)
For my environment I set up Powershell as the default shell ( https://docs.microsoft.com/en-us/windows-server/administration/openssh/openssh_server_configuration ). This isn't necessary.
Once I have that all set up I can remote in. First step is to make sure that no users are using the database via the command openfiles ( https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/openfiles ).
Next I run a slightly altered version of Daniel Pineault's CompactDB script from https://www.devhut.net/2018/02/22/ms-access-backup-a-database-using-a-vbscript/
One small change that I made is to his CompactDB function. In his script he copies the file and then compacts the the backup file here:
Call CompactDB(sBackupFolder & "\" & sDBFile & "_" & sDateTimeStamp & "." & sDBFileExt)
I changed it to compact the source database as so:
Call CompactDB(sSourceFolder & "\" & sDBFile & "." & sDBFileExt)
This makes much more sense to me, since you want to keep your working file compacted and in good condition. It also works very well with his script since you backup the database before you compact the original this way.
M.
Comments