Microsoft Access provides many advantages for organizing and managing information. Some of the most common reasons for using Microsoft Access include:

• Centralizing data spread across multiple Excel worksheets or workbooks, or other file types
• Creating custom data entry forms with lookups, checkboxes, listboxes, and textboxes that can be filtered easily
• You find yourself doing many and complex lookups across numerous Excel worksheets and/or workbooks
• The ability to allow multiple users to access and edit data simultaeously
• Custom and standardized reports that can be emailed, faxed, printed, exported to Excel, exported to Microsoft Word, linked to a SharePoint list, or outputted to other formats
• Integrating data with other software programs including accounting software (e.g. QuickBooks, etc.), Customer Relationship Management software (CRM), or other third-party software.

There really is no limit on the types of applications that can be built with Microsoft Access. Accounting, order management, inventory, contact management, estimating, and project management are just a few types. For example, some companies find that available accounting software packages do not give them the flexibility they need to capture their accounting information, and choose to build a custom Access application designed specifically for their business needs. The same holds true for order and inventory management.

Microsoft makes available many Microsoft Access templates for common needs such as contact management, inventory, etc. which can be customized further to meet a particular business need. These templates are available on the Microsft.com web site. Regardless of whether an existing template exists, Microsoft Access can be used to design powerful, multi-user applications that can drive extraordinary efficiencies in your business.

Have an idea for a Microsoft Access application? Please feel free to contact us and we will be happy to provide an assessment and a free estimate.

Yes, Microsoft Access is a multi user application and supports multiple users working with a database at the same time. The same file can be shared by multiple users giving them access to the forms, reports and data. This is one of the great advantages of Microsoft Access versus single user applications. By placing the Access database on a share network drive, multiple users can gain access to and change the data and print reports.

The maximum size of an Access database is two (2) gigabytes. This limit applies to both .mdb files and the newer Access database format files .accdb. For Access databases that have reached this limit, you have a number of options. One option would be to split the frontend database (forms and reports) from the backend database (tables and queries). This is called a split database. You can then link more than one backend database to the front end database with each backend database having a size limit of 2 gigabytes.

A second option when the size limit has been reached is upsize the backend database (tables and quieries) to SQL Server.. This is called upsizing the database. There is a free version of SQL Server that you can use (the version depends on your version of Access) which has a size limitation of 4 GB. If you have a licensed version of SQL Server you can use this for the backend database. In this case, there would be no size limitation on the backend SQL Server database.

Yes. The Microsoft Office suite of products including Excel, Outlook and Word are all designed to be accessible from other Microsoft Office applications including Microsoft Access. While the newer versions of Microsoft Access have enhanced Office integration features, there are few limits on how you can integrate the various Microsoft Office applications. For example, you can take data from your Access database and merge it into an HTML and have Access automatically generate this email and send it out via Microsoft Outlook. You can create Outlook tasks, appointments, and contacts all from within Microsoft Access.

Word mail merge documents are another integration possibility. You can take data from your Access database and merge it into a Word mail merge document without leaving Microsoft Access. Complex Excel worksheets can be created from data from an Access database giving you the option to export your data out of Access to Excel format with which more users are familiar.

Yes. Most accounting software packages including QuickBooks include an programming interface (API) for integrating with their accounting software application. We get many requests from clients to move Microsoft Access and SQL Server data into QuickBooks or another accouting software program to reduce data entry redundancy and improve efficiency. Most items that can be created through the QuickBooks interface and be created itegration programming including invoices, payments, credit memos, timesheets, sales orders, purchase orders, inventory, etc. This is a powerful feature which can dramatically reduce accounting and administrative expenses. Please contact us for more information.

Yes. Microsoft Access has the capabilities to connect with most ODBC compliant database technologies. As long as an appropriate ODBC compliant driver exists, then connecting to and migrating from other database software is relatively straightforward. Most common database software packages have ODBC drivers available. Some niche database software packages do not have ODBC drivers available. In these cases, other migration strategies can be used, depending on the particular database software. If you have questions about the ability to migrate from a particular database package please contact us.

Yes. There is a conversion tool in Microsoft Access that allows you to convert an older database format to a newer format. For example, you can convert an Access 2000 database format to an Access 2002-2003 formated database. The success of the database conversion will be in part determined by the design and extent of macros and Visual Basic coding that is used in the database. For example, there were significant changes to Microsoft Access between Access 97 and Access 2000. Accordingly, upgrading an Access 97 database normally requires more work due to these changes. The difference between Access 2000, Access 2002, and Access 2003 are less signficant and therefore upgrading between these formats is usually less work.

With the release of Access 2007, new Access database file formats (.accdb) where introduced. The new database file format supports some new features not found in previous versions of Access, however, it also deprecates some features (user security, replication) found in previous Access versions. Therefore, the questions as to whether to convert to the new database format depends on the specifics of your Access database. Conversions to the new Access 2007 format is supported for Access 97, Access 2000, Access 2002, and Access 2003.

Yes, you can use SQL Server database software as a backend for an Access database application. In this scenario, the Microsoft Access forms and reports are in a front end database and the SQL Server tables, views, and stored procedures are in the backend SQL Server database. There is a freely available version of the SQL Server database software (e.g. SQL Server 2008 Express) that can be used, or you can use a existing licensed version of SQL Server. The freely available versions of SQL Server normally have a size limitation (e.g. 4 GB) whereas the licensed versions do not have a size limitation.

Advantages of using SQL Server as the database backend for an Access database application include scalability, security, and increased database capacity. SQL Server is a more scalable database technology allowing more Access database users while maintaining performance. Therefore, where an Access application is required to have many users, using SQL Server as the database backend improves performance. SQL Server also has more robust security including integrated Windows based security that make it a more suitable backend database choice where data security is an issue.

Microsoft Access has a database size limitation of 2 GB, whereas SQL Server has a minimum size limitation of 4 GB for the free version and unlimited capacity for the licensed versions. Therefore, were databases are predicted to be large in size, SQL Server can provide a better platform for building an Access database application. There are many other advantages to using a SQL Server backend with Microsoft Access including access to increased functionality available in SQL Server (e.g. SQL Server Reporting Services, SQL Server Integration Services, .NET CLR Integration, etc.)

Yes, there are tools available for facilitating the transfer of tables and data to SQL Server. These tools greatly reduce the amount of time necessary for migrating from an Access database to a SQL Server database. Because SQL Server is does not have any direct form building capabilities you will still need Microsoft Access or another form building platform (Microsoft .NET Windows forms, ASP.NET web forms, etc.) to display and edit the SQL Server data. In addition, there is support for migrating Microsoft Access database reports to SQL Server Reporting Services which greatly reduces the costs of migrating reports to SQL, if this is a requirement.