Microsoft Access Database Frequently Asked Questions (FAQs)
What are some of the reasons why I might want to use Microsoft Access?Microsoft Access provides many advantages for
organizing and managing information. Some of the most common reasons for using Microsoft Access include:
What types of applications can I build with Microsoft Access?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 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 Access 2013, Microsoft has expanded the types of databases that can be built. Access 2013 allows the creation of Access databases with SharePoint interfaces and also creation of Access databases leveraging a cloud hosted SQL Server Azure database. With Access 2013, Microsoft is expanding the ability of Access databases to integrate with cloud solutions such as Office365. 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. Can multiple users open and use a Microsoft Access database at the same time?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.
For Access 2010, the maximum number of users would can use the database at the same time 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. What is the maximize size of an Access database and what options do I have if I reach this limit?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 between 4 GB and 10 GB. SQL Server 2005 Express has a size limitation of 4 GB while SQL Server 208 R2 Express has a size limitation of 10 GB. If you have a licensed version of SQL Server you can use this for the backend database. In this case, there would be 524 PB (petabyte) size limitation on the backend SQL Server 2008 R2 database. Can I integrate Microsoft Access with other Microsoft Office applications including Microsoft Excel, Microsoft Outlook, and Microsoft Word?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. 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. What types of security are available with a Microsoft Access database?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 held 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. Access database projects that require robust user security generally use SQL Server database backends (called an Access Data Project, .adp) where user security can be controlled at a very granular level using SQL Server security. SQL Server security also allows integration with Windows security making this a more efficient methodology for handling security than the separate usernames and passwords associated with a Access System Database. 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). Can I integrate a Microsoft Access database with third-party software applications such as QuickBooks accounting software?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.
Can I migrate data from other database software (Lotus Approach, Dbase, Paradox, etc.) to a Microsoft Access database?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.
Can I use Microsoft Access to create reports using data from other databases?Yes, this one of the features that makes MIcrosoft Access such a flexible database system. Using Access's linked table funcationality, data from any ODBC compliant database can be linked into Microsoft Access for querying and/or reporting. Database file formats including Oracle, DBase, Paradox, SQL Server, FoxPro, and even Lotus Notes 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.
Can I upgrade an Access database created in a previous version of Microsoft Access to work with a newer version of Access?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. Can I use Microsoft Access with Microsoft SQL Server database software and what are the advantages?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 (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 SQL Server 2008 R2.
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. Particularly in databases that stored picture or image files, databases can grow quote large in size rather quickly, and reaching 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, SQL Server Integration Services, .NET CLR Integration, etc.) What are my hosting options for using SQL Server with Microsoft Access?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.
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 desire to host your own SQL Server database. For many small and medium size businesses, however, SQL Server hosting can present a challenge. Fortunately, with the increased Internet bandwidth available today, many businesses are turning to outsourcing their SQL Server hosting to companies like Lemington. Outsourcing SQL Server hosting is economical because the server, maintenance 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. Can I migrate my data from a Microsoft Access database to a Microsoft SQL Server database?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.
Can I use SQL Server Reporting Services with an Access Database?Yes, there are a number of ways to use SQL Server Reporting Services (SSRS) with Access databases. As background, SQL Server allows for the importing of Access database reports into SQL Server Reporting Services. 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 Access Data Project (ADP) where SQL Server is being used at the backend database this makes the transition to SSRS easier. Your data already resides in SQL Server tables and you can migrate existing Access reports and/or build new SSRS reports for distribution via the web or email. SSRS can also be used with Access Services in SharePoint 2019 to provide a reporting interface for Access databases. The free Reporting Services Add-in for SharePoint Server 2019 is a prerequisite to utilize this functionality Can I run Microsoft Access applications on an Apple Mac?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. Can I use Microsoft Access on a Windows Mobile or Windows Phone device?No, currently there is no version of Microsoft Access for Windows Mobile and Windows Phone devices. Database applications for mobile devices
are generally built around SQL Server CE databases and a user interface built using the .NET Compact Framework. 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, it is now possible to use Microsoft Access on Windows 8 Professional for mobile computing. While tablets are not quite a portable as smartphones, this does give users another option for extending Access database applications to a mobile setting. What are some things I can do when my Access database corrupts?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. Can I run multiple versions of Microsoft Access on the same computer?Yes, in general you can, although with the many different versions of Microsoft Access historically available and the number of historical operating systems (Win XP, Vista, Windows 7, Windows 8) 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.
If you need further convincing, please ask for references from many of our satisfied clients.
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. |