Lemington Consulting

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 functional 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 project management/tracking applications, and order and inventory management applications. Also, we have seen custom business intelligence reporting application built with Microsoft Access for sales, order, and inventory due to Microsoft Access' built in reporting engine.

Microsoft makes available many Microsoft Access database templates for common needs such as contact management, inventory, etc. which can be customized further to meet a particular business need, or when needed, entire new applications can be built from scratch using the custom form design functionality of Microsoft 365 Access. These templates are available on the Microsoft.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.

With Microsoft 365 Access, Microsoft has expanded the types of databases that can be built. Microsoft 365 Access allows the creation of Access databases with SharePoint interfaces and also creation of Access databases leveraging a cloud hosted SQL Server database. With Microsoft 365 Access, Microsoft is expanding the ability of Access databases to integrate with cloud solutions such as those offered through Microsoft 365 subscriptions.

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 complimentary cost estimate.

Yes, Microsoft Access is a multiuser 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.

For Microsoft 365 Access, the maximum number of users would can use the database at the same time (i.e. concurrent users) is limited to 255. This is a hard maximum. For performance and other reasons the maximum number of users is less than this depending on the exact configuration of your Access database (e.g. split Access database, Access database with SQL Server linked tables, etc.).

The maximum size of an Access database is two (2) gigabytes. This limit applies to both the older .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. While requiring more maintenance, the split database option allows you to work around the hard 2 GB storage limit of each Access database.

A second option when the size limit has been reached is upsize the backend database (tables and queries) to SQL Server, and then linking the SQL Server tables into Microsoft Access via ODBC. This is called upsizing the database. There is a free version of called SQL Server Express Edition that you can use which has a size limitation of 50 GB (increased from the previous limit of 10 GB) for SQL Server 2025 Express Edition. If you have a licensed version of SQL Server (SQL Server Standard Edition or SQL Server Enterprise Edition) you can use this for the backend database. In this case, there would be 524 PB (petabyte) size limitation on the backend SQL Server 2025 database.

Yes. The Microsoft 365 Office suite of products including Excel, Outlook and Word are all designed to be accessible from other Microsoft 365 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 formatted email 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, thus giving you the ability to generate custom contracts, quotes, invoices and other Word documents.

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.

New features in Microsoft 365 Office allow the integration of Microsoft Access with cloud based technologies such as other Microsoft 365 applications and Microsoft 365 SharePoint Server. These cloud-based integrations open new opportunities for collaboration, sharing, and automation of Access database information.

Older versions of Access (version 2003 and earlier versions) supported an Access security methodology called the System Database. The System Database (.mdw) is a separate database from the Access database file (.mdb) and holds security related information and permissions. Access database administrators would use a tool called the Workgroup Administrator to connect a System Database with a specific Access database. The System Database allowed the Access database administrator to provide usernames, group names, and passwords, as well as user and group specific permissions on specific Access objects (tables, queries, form, reports, etc.).

The new Access database format introduced with Microsoft Access 2007 (.accdb) no longer supports the System Database concept. A separate type of Access database, called an Access database project (.adp), continued to exist in Microsoft Access until Microsoft Access 2010, after which it was deprecated. The Access Database Project was generally used when the user required robust user security generally using SQL Server database backends where user security can be controlled at a very granular level using SQL Server security. But again, both the System Database (.mdw) and Access Database Project are now both depreciated.

In more recent versions of Microsoft Access (Microsoft Access 2013 and later including the current Microsoft 365 Access, granular security permissions can most easily be accomplished by creating and linking backend database table SQL Server, and then using SQL Server security to set granular security permissions. SQL Server security also allows integration with Windows security making this a more efficient methodology for handling security in Windows domain security environments. The backend SQL Server database can be hosted locally, or hosted over the Internet using Lemington's SQL Server Hosting service.

Of course, Windows security (Access Control Lists, or ACLs) can be used at times where only a coarse level of security is required (e.g. read-only access to the database).

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, this one of the features that makes Microsoft Access such a flexible database system. Using the Microsoft Access linked table functionality, data from any ODBC compliant database can be linked into Microsoft Access for querying and/or reporting. Database file formats including SQL Server, MySQL, DB2, Oracle, PostgreSQL, DBase, Paradox, FoxPro, and any other ODBC compliant database format can be linked into Microsoft Access. Linked tables in Access are essentially treated like any other Access table and can be used as the basis for report queries or directly as a report record source.

Yes. The newest Microsoft 365 Access database format (.accdb) supports the upgrading of the older Microsoft database format (.mdb). Surprisingly, there are still quite a few Microsoft Access databases using the older .mdb database format still in use today. However, there are some caveats to the upgrade process depending the specifics of the older .mdb file you are trying to upgrade. Databases with just table data (or queries) can be upgraded relatively quickly and easily. Older .mdb Access database with existing forms and report that contain VBA coding the upgrade process is a little less certain, and may require upgrades to the VBA coding to make the same database function properly in the newer .accdb database format. However, Lemington is well-experienced in these types of VBA coding upgrades and can expertly assist you with this type of Microsoft 365 Access database upgrade.

Most older .mdb Microsoft Access databases are in already Access 2003 format, the last version of Microsoft Access to support this format as the primary database format. In the rare case of an older Microsoft Access database format, for example, Microsoft Access 2000 or Microsoft Access 97, these older .mdb formats can first be upgraded to Microsoft Access 2003 format and then upgraded to the new Microsoft 365 Access database format (.accdb).

The new Microsoft 365 Access database file format (.accdb) 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 Microsoft 365 Access format (.accdb) is supported for Access 2003, Access 2002, Access 2000, and Access 97.

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 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 (between 4 GB and 10 GB depending on the version) whereas the licensed versions have a very large size limitation of 524 PB (petabytes) for the licensed version of SQL Server (SQL Server Standard Edition and SQL Server Enterprise Edition).

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 Microsoft SQL Server 2025 Express Edition has a maximum database size limitation of 50 GB and unlimited capacity for the licensed versions (only limited by the available hard disk space). Therefore, were databases are predicted to be large in size, SQL Server can provide a better platform for building an Access database application. Particularly in databases that stored picture or image files, databases can grow quote large in size rather quickly, and reaching the Microsoft Access 2.0 GB storage limit is not uncommon.

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 (SSRS), SQL Server Integration Services (SSIS), .NET CLR Integration, etc.)

Many Microsoft Access databases now use SQL Server as a backend for the datastore. The advantages are many including increased scalability, avoiding the 2 GB database limit on Microsoft Access databases, and the ability to protect your data using SQL Server security with granular user permissions.

When using SQL Server with Microsoft Access (or even without Microsoft Access) you have several options for hosting the SQL Server database. One you can host the SQL Server database yourself if you have the requisite hardware and database administrator knowledge/expertise and desire to host your own SQL Server database. The self-hosting configuration can be complicated and does open your server to the Internet which has its own set of potential security issues.

For many small and medium size businesses, however, SQL Server hosting can present a challenge both from the database administration standpoint and the more complicated SQL Server hosting configuration. Fortunately, with the increased Internet bandwidth available today, many businesses are turning to outsourcing their SQL Server hosting to companies like Lemington Consulting. Lemington Consulting provides SQL Server hosting service for many of our clients to manage and administrate their SQL Server database.

Outsourcing SQL Server hosting is also economical because the server, maintenance, database administration, and SQL Server licensing costs can be spread across many hosting customers, thus reducing the hosting cost for any individual hosting customer. If you are interested in exploring the outsourcing of your SQL Server database please contact us for more information.

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.

Yes, there are a number of ways to use SQL Server Reporting Services (SSRS) with Access databases. As background, SQL Server Reporting Services (SSRS) allows for the importing of Access database reports into SQL Server Reporting Services reports. This provides the advantage of building Access reports initially and then migrating those same Access reports to SSRS later without having to reproduce the reports. SSRS provides additional features above and beyond Access database reporting including the ability to view reports via an Internet browser, report subscriptions, and many other features.

If you have an Microsoft 365 Access database where SQL Server is being used at the backend database via Microsoft Access ODBC linked tables, this makes the transition to SSRS easier as the database tables are already in SQL Server. Your data already resides in SQL Server tables and you can import existing Access reports and/or build new SSRS reports for distribution via the web or email using data in your SQL Server tables.

SQL Server Reporting Services (SSRS) can also be used with Microsoft Access Services in Microsoft SharePoint server to provide a reporting interface for Microsoft 365 Access databases. The complimentary SQL Server Reporting Services Add-in for SharePoint Server is a prerequisite to utilize this functionality.

Currently, there is no version of Microsoft Access that will run directly on a Apple Mac. There is a version of Microsoft Office for an Apple Mac, however, it does not include Microsoft Access currently. That being said, there are a couple of approaches you can use to run Microsoft Access applications on an Apple Mac.

One, you can use Boot Camp Assistant, a feature of the Apple operating system (found in System Preferences), to create a separate Windows operating system partition on the Apple Mac hard drive. This allows a user to have both the Apple Mac operating system and the Microsoft Windows operating system installed on the same Apple Mac. Once the Windows operating system is installed using Boot Camp Assistant, you can then install Microsoft Office, or Microsoft Access individually on the Windows operating system and run your Microsoft Access applications from there.

A second option, is to purchase a virtualization application such as Parallels, VM Fusion, or Crossover for Mac and virtualize either an entire Windows operating system, or just the Microsoft Office software.

For further information regarding any of these options, please feel free to contact us.

For the Android operating system, there currently is no version of Microsoft Access that will run natively on an Android phone. With a Microsoft 365 subscription that includes SharePoint, you can deploy Microsoft Access databases to SharePoint and then access them through a web browser. Another option is to export the Microsoft Access data to SQLite, or a cloud database such as Microsoft Azure SQL database, or Google Cloud SQL.

You may find third party software which allows the use of Microsoft Access databases on mobile phone, however, we do not have any recommendations at this time. With the introduction of the Microsoft Surface tablets and laptops, it is now possible to use Microsoft Access on Windows 11 Professional on tablets. While tablets are not quite a portable as smartphones, this does give users another option for extending Access database applications to a mobile-like setting.

Similarly, for the Apple iPhone, there is no version of Microsoft Access that will run natively on an iPhone. Many iMac users take advantage of the dual boot capability to install a separate Windows operating system and then boot into Windows to run Microsoft Access, or subscribe to Windows 365 Cloud PC and remote into the Windows 365 Cloud PC to run Microsoft Access.

Access database corruption is less frequent in more recent versions of Microsoft Access than in earlier versions. How you deal with a corrupt Access database depends in part of the extent of the corruption. Sometimes the corruption only affects a single form or report, and other times the database corrupts to the point where the database cannot even be opened. In cases where the Access database can be opened and the corruption is limited to one or a few objects, the first troubleshooting step should be to compact and repair the database. If the corruption persists after a compact/repair then the next step would be to delete the corrupted form, report, or other object and import the same object(s) from a recent backup.

In other cases, you may be better off creating a new Access database and importing all the objects from the corrupt database into the new database. For situations where the database cannot be opened due to corruption there are third-party software utilities that may be able to repair the database corruption, but of course your success with these types of utilities will depend on the circumstance surrounding your particular database and there is no guarantee that such utilities can successfully recover your database.

Due to the possibility of Access database corruption, we highly recommend at least a daily backup of your Access database for frequently used databases, and more frequent backups if the software is critical to your business operations.

Yes, in general you can, although with the many different versions of Microsoft Access historically available and the number of historical operating systems (Windows 11, Windows 10, etc.) your mileage may vary. In order to be successful running multiple versions of Microsoft Access on the same computer, you MUST install from oldest version to newest version in that order. Installing an older version when a newer version is installed will almost certainly break the newer version installation.

Therefore, if you want to run multiple versions and the newer version is already installed, you must uninstall the newer version, then install the older version, then reinstall the newer version. With the advent of virtualization, both operating system and application, there are other options available for running multiple versions of Microsoft Access.