Mar 20

Microsoft Access Query Sorting on Multiple Date or Numeric Fields with Blank Values

Read our new paper, Microsoft Access Sorting on Multiple Date (or Numeric) Fields with Blank Values.

Learn how to sort on multiple date fields when null values exist by using a calculated field. By using the IIf function with Is Null, you can easily sort to see the most recent (or oldest) records.

Microsoft Access QueriesWe’ve included information on avoiding the IsNull function to maintain SQL Server compatibility. Additional information and examples are provided to show why using the NZ function (NullToZero) is not equivalent and returns the wrong results.

This new paper is part of our Microsoft Access Query Help Center.

Mar 13

Mistakenly Blaming Microsoft Access instead of the Developer

In this March 10th Washington Post article, Alexandria tailor weaves custom solution for taking orders, a local firm is mentioned having struggled with Microsoft Access and being forced to migrate to a new system due to problems with their Access database. In particular, their database couldn’t provide multiuser support and lost data when more than one person used it.

Unfortunately, stories like this perpetuate the myth that Microsoft Access features are limited rather than the lack of skills of the developer who tried to customize it. It’s a shame the business owner and developer weren’t aware that Access could address the multiuser issues they encountered; thereby saving time, money, and headaches from having to migrate to a new platform.

Microsoft Access is Multiuser Ready

Microsoft AccessThe reality is that Microsoft Access is fully capable of providing multiuser support if it’s designed properly. For basic database solutions with under 1GB of data (maximum 2GB), Access comfortably supports up to 200 simultaneous users with a properly designed solution.

Microsoft SQL ServerAs the number of users and data expands, Access makes it relatively easy to migrate the data storage from an Access database to SQL Server, while maintaing the application layer (forms, reports, code, etc.) in Access. This also lets you share the SQL Server data on web sites and other platforms. That means supporting two users in a tailor shop would be trivial with MS Access.

Split Database Architecture for Multiuser Solutions

People sometimes treat Access databases like Excel spreadsheets and want each user to open and close the same file. That’s not the way to support multiuser data sharing in Access. A split database architecture is needed to separate the application layer from the shared database. Each user gets their own copy of the front-end database application that links to the tables in the shared database.

Microsoft Access includes a built-in wizard to split the database and another wizard to link the front-end database to back-end tables. We wrote a paper about this years ago called, Microsoft Access Split Database Architecture to Support Multiuser Environments, Improve Performance, and Simplify Maintainability.

While having a web application has its role and advantages, the article mentions their internet connection isn’t reliable and their business is negatively impacted when that happens. That’s an unfortunate result of their new platform. There are ways to create hybrid solutions to provide on premises support with shared web solutions, so these issues need to be considered when creating business critical solutions.

Using Microsoft Access Strategically

Small businesses often have very limited budgets and time to understand technological options. Completely eliminating Office and Access as viable solutions for incorrect reasons is wasteful. Microsoft Access addresses an important segment of database needs, and offers small businesses and information workers the ability to make modifications and extensions that other platforms do not allow so easily. Understanding where and how to use Microsoft Access effectively along with its limitations offers organizations of all sizes a competitive advantage. We’ve helped many small businesses, non-profits, and multi-national companies properly use this technology very effectively. Here’s our article on Microsoft Access within an Organization’s Database Strategy that discusses this in more detail.

Conclusion

There are lots of terrible applications created on every technology platform whether it’s Microsoft Access, Excel, Visual Studio, Java, Oracle, SAP, etc. In this case, the skills of the Access developer were clearly lacking. Getting that confused with the technology is misguided.

Additional Resources

For additional resources to build robust Microsoft Access solutions and understand what’s possible, visit our: