Agee Software, Inc.
Plan Your Network
Many of my customers have five or six employees and outsource their mail and web services. They
have Microsoft Access databases that do mission critical tasks like customer contact, schedule, inventory, invoice,
accounting and document management. They often get by using a shared folder on a workstation. If you have a large
amount of data, store it on an external USB drive. You can then back it up with XCOPY instead of having to use
backup programs that handle locked and system files. The external drive is also easier to grab if you have to
leave in a hurry and you will be able to connect it to a laptop to continue operations more easily. If you need
the performance of an internal hard drive, create a partition for data or add a non-system drive.
To set up a shared folder, you need to know five things from the Server.
- Computer name of the server. Window key R, Cmd to open a Cmd prompt. WHOAMI or ECHO %ComputerName%
- Share name. Open the Cmd prompt. NET SHARE
- UserID to use when mapping drive. Open the Cmd prompt. WHOAMI or ECHO %UserName%
- Password for the UserID. If you don't know the password, you can set it. Right-click on the desktop, New, Shortcut.
Cmd, Next, Finish. Right-click the Cmd prompt, Run As Administrator. If your UserID was owner and you want the password
to be Secrit,
NET USER owner Secrit
- Choose a drive letter that is not in use to map the drive. To see what drives are mapped at the Cmd prompt: NET USE
- If the shared folder is on the PC of the person who uses it the most, it will perform better for that person because there is no network delay. Simply put the database in a folder called something like DB, right-click on the folder, select Properties, Sharing, click Advanced Sharing and check Share this folder. Click Permissions and add Users in the Select Users or Groups box. Note the name of the sharing PC (we will refer to it as the 'Server' even though it is not). Click OK. Click Users in the Group or user names box and check Allow by Full Permissions in the Permissions for Users. Click Apply. Click Everyone in the Group or user names box and click Remove. Click OK to close the Share Permissions box, OK to close the Advanced Sharing box and Close on the Properties window.
- For added security, you may want to create a special database user who doesn't have permissions to any folder except the shared DB folder. If you do this, you can give the permissions above to that user instead of Users in the step above.
- Next map a drive letter. On the 'Server', right-click on Computer and select Map Network Drive. Select the drive letter. It should be the same letter for all users, so make sure it is available on the other PCs. Access databases are normally split into two parts, the 'front end' database that contains forms, reports, queries and code, and the 'back end' that contains the shared tables. The Access developer must specify the location of the shared tables in the links of the front end. Using this drive letter is the quickest way to do this. In this example, we will assume we are using drive Z:. In the folder combo, enter \\Server\db where Server is the name of the server you noted above (your PC will probably be named something else). DB is the name of the share you created above. Check Reconnect at logon and Connect using different credentials. Enter the userid and password for the server and check Connect at Logon.
- Complete step 4 for each of the other PCs that will use the database. Drive Z:, Folder: \\Server\db, check both boxes, use the server user's userid and password and check Connect at logon. Test each user's connection to the database. If they get an error, verify they can see the shared files and use Notepad to create a small file to verify they have write access to the shared folder. Verify each connected PC has a shortcut to the database on the desktop and test to make sure the user can log in.
Back Up Your Network
Daily backup of a critical file. This helps protect you from errors entered into your database.
This batch file backs up the shared data tables in z:\ContDat.mdb to your local c:\work\bk folder. Paste the file below into Notepad and save it as c:\work\bk.bat. Change ContDat to the name of your database.
::Backs up 7 generations of ContDat.mdb in folder z:\work\ to C:\work\bk ::Run daily in Task Scheduler @ECHO OFF C: IF NOT EXIST C:\work\Nul MD \work IF NOT EXIST C:\work\bk\Nul MD \work\bk CD \work\bk DEL ContDat7.mdb REN ContDat6.mdb ContDat7.mdb REN ContDat5.mdb ContDat6.mdb REN ContDat4.mdb ContDat5.mdb REN ContDat3.mdb ContDat4.mdb REN ContDat2.mdb ContDat3.mdb REN ContDat.mdb ContDat2.mdb COPY z:\ContDat.mdbTo schedule the task, open the Task Scheduler
(In Windows 7, Start, Search, Task. In Windows 10, Right-click Start, Search, Schedule).
- Click Create Task in Actions
- General tab, Name: Bk
- Triggers tab, New, Daily, (choose a time to start), OK. If you set the time to five minutes into the future, you can test it.
- Actions tab, New, Browse to c:\work\bk.bat, OK.
- Click OK to close the Task Scheduler
Weekly Backup. Save this batch as c:\work\BKW.bat and schedule weekly to back up your local
My Documents, Downloads, Favorites, Pictures and external or mapped drives you specify. Add your own
lines to back up other data folders. You can map drives on other PCs if you add your userid and password as
another account to the other PCs and share the C: drive as C. Consider how to back up your email if a copy
of your messages aren't stored on a server off your network. Rotate the external drives the day after your
backup runs. Your goal is to be able to restore your operation without losing more than a week of data using
nothing in your office; only the quarterly backup drives on similar hardware and your weekly backup.
:: BKW.bat backs up drive X: and local user files to E: :: Run weekly in Task Scheduler @ECHO OFF E: XCOPY x:\*.* \ /d /s /h /i /c /k /y XCOPY %USERPROFILE% E:\%USERDOMAIN%\%USESRNAME% /d /s /h /i /c /k /y :Uncomment next 3 lines to copy AnotherPC user settings. Change AnotherPC to the correct name. ::NET USE T: \\AnotherPC\C ::XCOPY T:\Users E:\AnotherPC /d /s /h /i /c /k /y ::NET USE T: /d
- Monthly Archive:
::Bk30.bat backs up .mdb files in c:\work to c:\archive as BKyymm.zip where yy is year and mm is month ::Requires 7-zip be installed. TASKSCHD (Schedule task) to run on Last day of month. "c:\program files\7-zip\7z" a -stl c:\archive\bk%date:~12,2%%date:~4,2%.zip c:\work\*.mdb
- FTP Off Site Backup:
::Uses WinSCP to log onto ftpsite.com as joe w/password of secrit and send it c:\work\contdat.mdb @c: @cd \work @"c:\program files\winscp\winscp.com" /command "open ftp://joe:email@example.com" "put contdat.mdb" "exit"
- Quarterly Hardware backup. This lets you replace the lost drive, boot, install Windows and antivirus updates and restore files from daily or weekly backups. It requires buying a $30 Kingwin Clone adapter to duplicate your hard drive to another drive of equal or larger capacity. Use EaseUs ToDo Backup if you need to clone your drive to a smaller one. Label the copy with the date before you start. I usually run Windows updates and an antivirus update and scan manually to make sure the backup is healthy before I start. After you verify the copied drive boots, store it in a safe, secure place off site or a faraday cage in your underground bunker ;). Removing the drive is inconvenient, but this needs no software configuration like software backups and only needs to be done quarterly unless you make a major hardware or software change.
- Recovery Tools. Store application media, activation codes, account passwords, recovery tools, backup hardware and support contact info in a secure location too. Use Rufus to create a bootable Windows USB drive. If your drive is not bootable, you can boot from the USB and repair it or copy files to the USB. It is also good to have a Live Linux Distribution in your tool kit that doesn't rely on Windows drivers to get to the web. Plan to have access to a laptop or tablet PC away from your network that you can use to continue operating in a power failure or after a fire or theft. Your recovery tools let you get the latest version of files, see what is lost or restore every part of your network operations on completely different hardware without relying on anything in your office.
NOTE: While backing up, don't forget your personal info.