Author Archives: Matt

How to split an Access Database

Posted on June 17, 2015 by Matt

In a follow up to our previous article about the Top 10 Reason to Split an Access Database, this post will give you the quick and easy way to get started with the process.

Splitting an Access database is essential when sharing a database via internal network our cloud hosting. If you have ever received errors when multiple users are editing your Access database, it is likely that you are not operating with what is called a split-database.

When splitting a database, you reorganize it into two separate files. The front-end database contains all the objects such as queries, forms, and reports while the back-end database contains all of the data tables.

Here are the direct instructions from Microsoft for how to split your database:

  1. On your computer, make a copy of the database that you want to split. Start with the database file on your local hard drive, not on the network share. If the database file is currently shared from your local hard disk drive, you can leave it where it is.
  2. Open the copy of the database that is on your local hard disk drive.
  3. On the Database Tools tab, in the Move Data group, click Access Database. The Database Splitter Wizard starts.
  4. Click Split Database.
  5. In the Create Back-end Database dialog box, specify a name, a file type, and a location for the back-end database file.
    NOTES
    • Consider using the name that Access suggests. It preserves the original file name, and indicates that the database is a back-end database by inserting _be into the name, just before the file name extension.
    • Do not change the file type unless some users will use an earlier version of Access to access the data.
    • You can enter the path to the local or network location in the File Name box, in front of the file name. For example, if the network location for the back-end database is \\server1\share1\ and the file name for the back-end database is MyDB_be.accdb, you can enter \\server1\share1\MyDB_be.accdb in the File Name box.
    • The location that you choose must be available to everyone who will use the database. Because drive mappings can vary, you should specify the UNC path of the location instead of using a mapped drive letter.
  6. When the wizard finishes, it displays a confirmation message.

Once you finish with the process, your database will be far more efficient and reliable while also ready for shared usage.  You’ll be able to distribute the front-end and back-end files to either your local network storage or a cloud solution such as Access Hosting’s Access RDP Pro plan to start sharing the database with multiple users.

To get started with a 30-day free trial of Access Hosting’s MS Access RDP Pro plan, click here.

 

References:
https://support.office.com/en-us/article/Split-an-Access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc
https://support.microsoft.com/en-us/kb/304932
Posted in Access 2010, Access 2013 | Leave a comment

Preparing your Access database for the web

Posted on October 8, 2014 by Matt

Preparing You Access Database for the Web

Have you considered running your Access Database on the web with Sharepoint and Access Services?

Access Services does not support all the data types, relationships, objects, or events that the full Access client provides. If you’ve created a web database from the start, only the supported elements will be shown during the design process. For those who have not created a web database from scratch, it is still possible to get your regular Access database up to the web with Access Services.

Access 2010 includes the “Web Compatibility Checker” tool. This tool checks the web compatibility of the tables in a database as well as web objects. However, the tool does not check any data within the actual tables and does not check linked tables from other data sources.

The goal of this tool is to make sure that the database is “web-legal” and to get you up and running with Access Services in a functional manner.

To run the Web Compatibility Checker tool in Access 2010:

1. Go to FILE > Save & Publish
2. Click “Publish to Access Services”
3. Click “Run Compatibility Checker”

Once you’ve completed this process and fixed any errors, the next step is to get the database up to a Sharepoint server.  You can get up and running with a Sharepoint 2010 Enterprise server for as low as $49 per month from AccessHosting.com

Posted in Access 2010, Access 2013, Sharepoint 2010, Uncategorized | Tagged , | Leave a comment

Compacting your MS Access Database

Posted on October 2, 2014 by Matt

As files are deleted over time, the space within your access database file can become fragmented. This will result in your database growing to a much larger file size than it needs to be. Regularly compacting your MS Access Database is a great way to improve the performance of your database and reduce the file size.

**Important** Make sure no other users are currently accessing the database!

Access 2010:

You can compact the database you currently have open by clicking File > Compact & Repair Database on the info tab in Backstage view.

Access 2013:

To compact the database you currently have open, click Database Tools > Compact & Repair Database

compact_2013

Pro Tip

How to compact your database when you close it

You can tell MS Access to compact the database each time you close it.

Open your database > Click the FILE tab  > Click OPTIONS > Select the Current Database category > Select the Compact On Close checkbox under Application Options.

Note: If multiple users are sharing the same database, Access compacts the database when the last user closes it.

compactonclose

Posted in Access 2010, Access 2013, Tips & Tricks | Leave a comment

Reasons to switch to a MS Access Database

Posted on September 29, 2014 by Matt

Switching to a MS Access Database can be a great improvement to the workflow of your business. Whether your converting data from dozens of spreadsheets, trying to speed up reporting for that large Excel workbook,  or looking for a database that multiple users can collaborate on remotely, MS Access can be the cloud-ready solution that you are looking for.

It’s easy! – Getting up and running with an Access database doesn’t require hours of training or hiring a database administrator. Users familiar with the Office Suite will find it quite easy to get started using a database along with tips and tutorials from the web.

Sharing Data – Only one person edit effectively edit data in an Excel spreadsheet at a time. If you share a spreadsheet with many people, editing the information across many sources can cause issues for the workflow. Microsoft Access locks the row of a table being edited by one person so that no conflicting changes can be made by another user, while still permitting many other users to access or update the remaining rows in of the database table.

Data Control –  Typically, different users will need to edit and update information in the database. MS Access allows for options to control this data to ensure consistency.

Affordable – Traditional client-server databases can be quite expensive. Hardware, software, and development costs can be quite high for most applications. Being a desktop application, each user must either have a copy of the software or purchase access to their database hosted on a remote cloud service.

Flexibility – It is very easy to create, design, and implement functionality to your database. MS Access allows you to make changes to elements of your database without conflict.

Cloud Ready – MS Access 2010 and newer works great in the cloud environment. If you  need to share your database and collaborate with others either locally or across the globe, you can run the database on a remote computer with relative ease. For more information on running your database in the cloud, check out the plans at http://www.AccessHosting.com 

MS Access in the Cloud

 

Posted in Access 2010, Access 2013 | Tagged | Leave a comment