Feb 25

Breaking ACE out of the Bubble!

An important announcement from the Microsoft Access team addresses the problems with connecting to Access ACCDB databases from other programs.

The ACCDB database format was introduced with Access 2007 and offered a new Access Database Engine (ACE) for external programs to connect to it. Connecting to the earlier MDB database format was never an issue because that requires Data Access Object (DAO) which is part of Windows.

Connecting to Microsoft Access Databases Outside of Access

ACE was available when Access 2007 and 2010 were installed. However, later Access versions sandboxed ACE so only Office could use it. It prevented other programs, including Microsoft programs such as PowerBI and the SQL Sever Migration Assistant (SSMA), from using it to support ACCDB databases.

The solution was to separately install the ACE Redistributable which provided ACE OLEDB (Microsoft.ACE.OLEDB.16.0, or Microsoft.ACE.OLEDB.12.0). That was a hassle and complicated because

  • Not every user had permissions to install it
  • Installations were 32 or 64-bit specific
  • Even if it were installed, it could be out-of-sync with the Access version that is installed.

This was especially frustrating because when Access is installed on the machine, it includes ACE but simply didn’t allow other programs to use it. And because Access/Office 365 was constantly being updated, its version of ACE may support features that the redistributable didn’t, creating conflicts.

ACE is Now Available with Access

With this Microsoft announcement, ACE is now exposed and available for external programs to use it.

If you have Office 365, or click-to-run versions of Access 2016/2019 Consumer installed, you no longer need to install ACE to support external programs.

This change enables previously unsupported scenarios, including Microsoft programs, to connect to Access ACCDB databases without installing ACE. It eliminates incompatibility issues between different versions of ACE. It also helps our programs Total Access Admin, Total Access Startup, and Total Visual Agent connect to Access ACCDB databases directly.

Microsoft’s Official Announcement

Visit the announcement from the Microsoft Access program manager Ebo Quansah: Breaking ACE Out of the Bubble.

Apr 15

Total Access Startup Available for Microsoft Access 2016 and 2013

total-access-startupTotal Access Startup makes it easy to centrally manage all your Microsoft Access database deployments. Ensure that all your users run the latest version of your database application with the right version of MS Access. Easily deploy updates without having to manually change things on each user’s PC. Simply point your users to a shortcut and they never need to know the actual name of the database.

overview

Total Access Startup 2016 is now shipping to let you:

  • For optimal performance, deploy a local copy of your master database on each user’s PC and keep it updated when you update the master
  • Run it with a specific Access version or a range of allowable Access versions. This makes it easy to support legacy versions of Access even if users install later versions of Office/Access.
  • Specify the bitness (32 and/or 64-bit) that are allowed for Access 2010, 2013, and 2016.
  • Display a professional splash screen graphic while your database loads
  • If your users can’t launch your database, a message appears with information you provide to contact you. You can customize our messages or translate them to your user’s language.

startupMore Resources

Total Access Startup is also part of the Total Access Ultimate Suite. Existing Total Access Startup customers can upgrade at a discounted price.

Jul 09

Microsoft Access Application Standards

Here’s a response to a question about the standards we implement for our Microsoft Access applications. These are some fundamentals for ensuring a solid foundation for professional Access solutions in priority with some resources related to them:

  1. Establish a backup, compact and repair, and disaster recovery plan. We use our Total Visual Agent program to schedule and automate the daily database chores.
  2. Putting in a comprehensive and global VBA error handling structure so crashes are documented with the procedure call stack and we don’t have to rely on a user’s recollections. As part of the error handling, we also always add line numbers to the code we deliver so we can pinpoint exactly where a crash occurs. We use the Code Delivery feature of Total Visual CodeTools to do this.
  3. Split Database Design. Without this, it’s nearly impossible to enhance the database while others are changing the data.
  4. Cleaning up VBA code. From code indentations to applying our variable naming conventions along with prefixes for global vs. module vs. procedure, and constants vs parameters vs. regular variable names. We use the Code Cleanup feature of Total Visual CodeTools to do this. Personally, without cleaning it up to our standards, I find it nearly impossible to get any work done while I’m struggling with someone else’s convention.
  5. Setting up a development, testing and deployment process so changes can be implemented safely and efficiently. Lots of issues around this but having a fast, stable way to manage changes and how people launch the Access application is critical. We use our Total Access Startup program to help with this.
  6. After these structures are in place, we’re ready to run Total Access Analyzer against the database to address the issues it finds wrong and could be improved in the application.

For additional details, including Luke Chung’s PowerPoint presentation at the Portland Access User Group conference in May, read his paper Taking Over Legacy Microsoft Access Database Applications.

Sep 22

Total Access Startup for Microsoft Access 2010 is Now Shipping

Total Access Startup for Microsoft Access DatabasesLaunch Microsoft Access Databases

Total Access Startup lets you centrally manage and guarantee that all your users always launch the right version of Microsoft Access with the right version of your MS Access database (ACCDB, MDB, or ADP). Simply distribute a shortcut to your users to start your application. When you post an updated database, your users automatically get it installed on their machine the next time they launch your program. They don’t need to know the name of the database. You manage all of that in one place and can change it at any time. A runtime version lets you control the Access version launched for users not on your network.

Total Access Startup 2010 is now shipping. It supports both 32 and 64 bit versions of Microsoft Access 2010 letting you control which version your application requires. It also supports all legacy versions of Microsoft Access. Learn more about the new features and try out the free trial version.

May 31

Microsoft Access Database Scalability: How many users can it support?

What’s the Maximum Number of Microsoft Access Users?

Microsoft Access 2013There is a persistent myth that Microsoft Access Jet databases can only support 20 or so users. Here’s my response to a recent inquiry:

I flatly refute any suggestions that Microsoft Access users are limited to around 30. We’ve run many tests and have never seen that kind of degradation in performance. It is a myth from Access 2.0 days that was eliminated with Access 97 almost two decades ago.

A poorly designed Access database won’t support two users, but a well designed Access solution can support hundreds of users. Of course, what matters is the number of simultaneous users, and what they’re doing.

At Least 200 Simultaneous Microsoft Access Users on an Access/Jet Database

If everyone is just viewing data or entering data into a table, that takes very little work and a large number of people (well over 200) can be supported. People cannot type faster than what Access can handle. If users are all running massive reports and queries with data updates, that can still be done but performance would be an issue which applies to any technology, so testing and optimization would be necessary.

Migrate Back-End Access Database to Microsoft SQL Server for More Users

Microsoft SQL ServerIf the back-end database is in SQL Server rather than an Access/Jet database, the number of users can be practically unlimited if each user has their own front-end copy of the Access application. Performance issues still apply based on what they are doing. In some cases SQL Server is slower than Access, so it is important to understand the situation before thinking SQL Server is the answer.

All that said, any Access application that is distributed to others with shared data should be a split database design. Here are a few resources we’ve written:

Total Access StartupSimplify Support for All the Users of Your Access Databases

Total Access Startup helps with the distribution of databases to each user’s desktop, and launching it with the right version of Microsoft Access.

This allows you to centrally support a large number of Access users across your network and ensure everyone is using the latest version of your application. It also simplifies the migration from one version of Access to another.

Additional Discussions

This topic was also discussed on the Microsoft Developer Network, Microsoft Access Database Scalability: How many users can it support?

Microsoft