How to Compact & Repair OneSource Company Data File

Why Compact Company Data File.

• Compacting your data file can make OneSource run faster.
• Compacting your data file reduces the size of your data file, which saves hard drive space (especially since you are backing up the file at least once a day).
• Compacting your data file reduces the chance of data corruption.
• Compacting your data file usually removes any corrupt data.
  

How often to Compact your Company Data File:

• This depends on various factors, but generally every 1-4 weeks is a good rule of thumb. 
  
• Important Note: If your data file gets bigger than 1.5 Gigs, then you will need to compact more often.  (The maximum size that a single MS Access data file can be is 2 GB.  There are numerous benefits to switching to OneSource's new 'Distributed' data file, which 'distributes' the 50+ most important tables into 14 individual data files, by data type.

How to Compact and Repair Your Company Data File

VERY IMPORTANT NOTES: Make a Backup Copy of your OneSource Company Data File(s) before proceeding. It is very important to complete the following steps in order.

STEP 1: MAKE SURE ALL USERS ARE OUT OF YOUR COMPANY DATA FILE.  Here is how to do this:

• Option 1: You can open up TSADMIN.MSC (Terminal Services Manager) to see which users still have MSACCESS.exe open.
• Start > Run > and then type: tsadmin.exe (on Windows 2003) or tsadmin.msc (on Windows 2008), and then click OK.
• Click on the Processes tab and then click on the Image tab to sort all of the images (programs running) in memory--by user.
• If you see any users that have msaccess.exe open you can:     (The steps below are in the most preferred order)
• go ask that user to exit out of OneSource
• or, click on the Users tab (in TSAdmin) then right-click on the Server to the LEFT of the user name and select Log Off
• or, go back to the Processes tab (in TSAdmin) and then right-click on the Server Name on the row that has msaccess.exe for x user and select End Process
• or, you can reboot your server
• Option 2: If you cannot seem to get all users out of the OneSource Company Data file, you can reboot your server and that will solve this problem.

STEP 2: LOCATE, BACKUP, and RENAME YOUR COMPANY DATA FILE.  Here is how to do this:

• Locate your Company Data File: In OneSource click Help > About OneSource > then make note of the file name at the end of the path in the field labeled Company Data File (and then click the Magnifying Glass button out to the right side of the path--which will open the folder where your Company Data File exists).
• Next locate the file, right-click on it and select Copy (Ctrl+C), and then Paste (or Ctrl+V).  (You should see and verify that it did in fact make a copy of the file, which will usually at the bottom of the directory, with the exact same file date and size). NOTE: If you have a distributed database you will need to copy and paste each of the 14 databases listed below.  1 quick way to do this is to create a new folder to store them in and then copy and paste each of the data files listed below into the new folder--so you will have a backup of all of them.

1. YourCompanyDataFile.mdb  (Where YourCompanyDataFile is your own company name).
2. osap.mdb
3. osar.mdb
4. osbk.mdb
5. oscsa.mdb
6. osgl.mdb
7. osicm.mdb
8. osinv1.mdb
9. osinv2.mdb
10. oslog.mdb
11. ospo.mdb
12. osqu.mdb
13. ossa.mdb
14. ossa-a.mdb
    

• Next RENAME your Company Data File:
• Right-click your Company Data File and select Rename--(add _compact to the end of the file name to make the name is different). 
• Then click off the file.  If you see an error related to the file name NOT being able to be changed then that means that someone is still connected to the data file--which means you will need to go back to Step 1 first.
• NOTE: This serves 2 important purposes: (1) It makes sure that ALL users and ALL copies of OneSource are no longer connected to the Company Data File, and (2) this also makes sure than no one inadvertently logs into the data file while it is being compacted.  (THESE ARE BOTH VERY IMPORTANT REASONS TO PERFORM THIS STEP). 

STEP 3: Compact Your Company Data File.

• Double-Click on your Company Data File to open it up.
• Execute the Compact & Repair command:
• For Access 2003, click Tools > Database Utilities > Compact and Repair.
• For Access 2007, click the round Office button (in the top left corner) > Manage > Compact & Repair
• For Access 2010, click the purple File tab (in the top left corner) > then click the large Compact & Repair button in the center of the screen.
• NOTE: If you get a message such as: 'You attempted to open a database that is already opened in exclusively by user 'Admin' on machine...' (or another message similar to that, that typically means that you still have at least 1 additional user connected to the data file (which means you need to repeat steps 1 and 2 again.  Reboot the server if you have to. 

Step 4: Rename Data File Back to Original Name

• Wait until the compact and repair is complete. NOTE: You will see a progress bar on the bottom of the screen when the database is performing the compact and repair.  After it is complete the tables will be displayed again.
• Next, right-click your Company Data File (The one you just renamed and compacted in step 2 & 3) and select Rename.  (Remove the _compact from the end of the Company Data File name).
• NOTE: IF you don't do this, no one will be able to Log In to OneSource. 

Step 5: Testing the Database

• Make sure the database is in read/write mode by following these steps:\
• After the compact and repair is complete Close and Reopen the database. Then open one of the tables in the database. (Double Click the Database and it will open)
• Scroll to the bottom of the table to a BLANK Space and type xxx (or whatever), then click on the record directly above it.
• If the you don't get an error message you now know that the database is in read/write mode.
• Delete the entire line that you put the XXX into before closing the database. Click on the Grey cell to the left of the 1st column in the table, This will highlight the entire row. Now Right click on that same Grey Cell and select Delete Record. Select Yes in the popup that warns you that you are about to delete 1 record.
• NOTE: If you have a Distributed Database you will need to do this on all 14 of the Individual Databases NOT just the main database.
• NOTE: What to do if you cannot write data to the database or if you get a message stating that 'database x is read-only' (when you first open up the database).
• Close the data file.
• Right-click on it, Select Properties and un-check the Read-Only property
• Then repeat the steps above to re-test it.  (If the database is still read-only contact OneSource tech support).