Q. What is the maximum size of a Microsoft Access database?
2 Gigabytes is the maximum file size for a Single Microsoft Access file. The biggest mistakes we see are databases that embed files into the database and background images used in forms and reports. The embedded files and background images are typically the root cause of a large database file size. Our practice is to store the files in a network folder, provide a link to those files in the database, and not use background images in forms and reports. Background colors usually satisfy the requirements.
Q. Can Multiple Users be in a Microsoft Access database at the same time?
Absolutely. However, it is recommended that the Microsoft Access database be split into a Front-End / Back-End or Client / Server for Multi-User databases. The Microsoft Access Back-End or Server side will be stored on a Network Server, and the Front-End or Client file will be copied to each user’s WorkStation. The Network Server does not need a Microsoft Access version installed. However, the WorkStation will need a version of Microsoft Access installed – either the free Microsoft Access runtime version or a full version of either 32bit or 64bit Microsoft Access.
Q. Are Microsoft Access databases easily corrupted?
Not if designed and implemented correctly. Sadly, the perception is that they do, and most IT professionals shy away from anything to do with Microsoft Access.
Corruption can occur when:
- Multiple users open the same file from a network location at a time (should be client-server environment).
- Attempting to open Microsoft Access file remotely through a VPN tunnel (Client-Server does not work through a remote VPN tunnel – a Remote Desktop Connection is recommended or a Cloud-Based Service).
- Frequent Network Connection drops between WorkStation and Network Server in a Client-Server Environment (Usually, a Front-End corruption results in an easy remedy by copying New Front-End / Client file to WorkStation).
In a Client / Server environment, the repair process on a Microsoft Access data file fixes corruption (inconsistent state) issues 99.9999% of the time.
If a corruption event exists more than once in a few months, the design and implementation approach must be evaluated.
Q. How many simultaneous users can be using a Microsoft Access database simultaneously?
Technically 255, and that is a TCP/IP restriction. A typical environment is 2 – 50 users.
Q. Does the Microsoft Access database slow down over time?
Yes. The Access JET Engine does not process data requests as efficiently as SQL Server. How long it takes for users to recognize the degradation in performance is based on many factors. Factors such as:
- Workstation hardware.
- Network Cabling – CAT5 vs. CAT6.
- Network Switch Speeds = 100Mb/sec vs. 1G/sec.
- Hard Wire Network Connection vs. Wireless Connection.
- Number of Records in Tables.
- Number of Simultaneous Users.
- Query Design.
- Form Design.
When the performance degrades to a point, users start to complain, then convert the Access data file to a SQL Server database, and the performance will be improved significantly.
Q. Can I integrate Microsoft Access with other Office programs such as Microsoft Excel, Microsoft Word, and Microsoft Outlook?
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 integrating 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. In addition, you can create Outlook tasks, appointments, and contacts from within Microsoft Access.
Word mail merge documents are another integration possibility. You can merge data from your Access database into a Word mail merge document without leaving Microsoft Access. In addition, complex Excel worksheets can be created from data from an Access database, allowing you to export your data out of Access to Excel format with which more users are familiar.
New features in Office 2013 allow the integration of Microsoft Access with cloud-based technologies such as Office365 and SharePoint Server. These cloud-based integrations open new opportunities for collaboration, sharing, and automation of Access database information.
Q. Can I integrate Microsoft Access with Microsoft SQL Server, and what are the advantages and disadvantages?
You can use SQL Server database software as a backend for a Microsoft Access database application. This is called a Front-End / Back-End or Client-Server environment where the Front-End or Client file is a Microsoft Access file containing all objects, and the tables are a direct link to the SQL Server database.
The free version of the SQL Server database software is the “Express” version (e.g., SQL Server 2012 Express) can be used, or you can use an existing licensed version of SQL Server. The “Express” versions of SQL Server have a size limitation (between 4 GB and 10 GB depending on the version), whereas the licensed versions have a substantial 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 to database users while maintaining performance. Therefore, where an Access application requires many users, using SQL Server as the database backend improves performance. SQL Server also has more robust security, including integrated Windows-based security, making 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 limit of 4 GB for the free version and unlimited capacity for the licensed versions. Therefore, where databases are predicted to be significant, SQL Server can provide a better platform for building an Access database application.
Other advantages to using a SQL Server backend with Microsoft Access include gaining access to the other functionality available in SQL Server, such as Reporting Services, SQL Server Integration Services, Maintenance Plan, Automated Jobs, Database Mail, etc.
A Disadvantage is that SQL Server elevates the support expertise level. As a result, support will need additional training and expertise above Microsoft Access which generally increases support costs, and the pool of support personnel is smaller than those supporting Microsoft Access databases.
Q. Can I integrate Microsoft Access with other 3rd Party software applications such as QuickBooks accounting software and MRP / ERP business software?
Yes. Most accounting software packages, including QuickBooks, include a programming interface (API) for integrating with their accounting software application. In addition, most ERP /MRP business systems have an ODBC driver to communicate to their data tables. Finally, other ERP / MRP business systems have alternating methods for syncing up with their data from a Microsoft Access program.
Q. Can I migrate my Microsoft Access data to Microsoft SQL Server?
Absolutely. If and when the time may come when the choice of using a SQL server database for the data side of your program makes sense, the SQL Server offers an Import Tool to import data from various data sources, Microsoft Access being one-choice, into a SQL Database. After the import, the table design on the SQL Server side must be reviewed. For example, primary Keys might need to be identified, and the bit fields might need default values entered. It’s best practice if the table structure, field format, and default values are verified once the SQL Server Database is created.
Q. Can upgrade versions of Microsoft Access impact my Microsoft Access program?
Rarely. The one upgrade that caused havoc was the release of the Office 64bit platform. Some custom code in Visual Basic for Applications (VBA) could not handle the more significant long data type and rendered some Microsoft Access programs inoperable until the custom code was modified to accommodate the long pointer in a 64-bit Office platform.
Version upgrades typically add new features and functionality in Microsoft Access and rarely remove old features and functions, mainly because of the number of Microsoft Access databases throughout the world in use.
Q. What are the security options with Microsoft Access?
Varies. The first is to identify the security level that is required.
Are you securing it from a user perspective, only letting certain users access the data? If so, this is accompanied by adding a login step to the Microsoft program that can also include read-only and read-write permissions.
Microsoft Access can be password protected. It can’t open the file unless the correct password is entered.
Added security can be applied if the data is in a SQL Server database, and the security functionality of SQL Server can be applied within.
Q. How do I get Microsoft Access?
Microsoft Access is available with specific versions of Office and Microsoft 365 and can be purchased and installed as a Stand-Alone software. Available from any 3rd part Computer Software Reseller.
Q. Do I need Microsoft Access installed to open a Microsoft Access database application?
Yes. To open and use a Microsoft Access database, you must have a full version of Microsoft Access installed or the FREE Microsoft Access runtime installed.
Q. Do I need Microsoft Access to make any design changes to a Microsoft Access database application?
Yes. Design changes require a full version of Microsoft Access. Design changes cannot be completed using Microsoft Access runtime.