Some of you may already know that Microsoft backtracked on their planned deprecation of OLEDB and provided a new OLEDB driver. However, it can be a headscratcher to figure what you should be using. When we were using SQL Server Native Client, it was pretty easy — the Native Client had both OLEDB and ODBC shipped in a single DLL file, making for easy installation. All you had to make …Read More
SQL Server with Access Articles, tips and tricks
Join me for the first of three Access with SQL Server Acdemy sessions September 11 2018
We kick off the new season of Access with SQL Server group meetings this year with three months of training: Introduction, Intermediate and Advanced topics on how to optimize Access with SQL Server. Part 1: Introduction On Tuesday September 11th 2018 I’m going to discuss: When you should or should not use SQL Server with Access The ONE thing you must do BEFORE you start moving your data to SQL …Read More
Designing a T-SQL trigger
On occasions when building a project involving an Access front-end and a SQL Server backend, we’ve run into this question. Should we use a trigger for something? Designing a SQL Server trigger for Access application may be a solution but only after careful considerations. Sometime this gets suggested as a way to keep the business logic within database, rather than the application. Normally, I like having the business logic defined …Read More
Drag and Drop In MS Access and Conditional Sorting
Drag and Drop In MS Access Doug was on a roll in January of 2004 when he wrote his article on drag and drop in MS Access. He tested it now using Access 2016 and it still works. This article shows you how to add drag-and-drop to your Access application using combinations of multi-value and single-value controls. Supporting Custom Sort Orders Presenting data to your users in an order that …Read More
Questions About SQL Server Security and Access Control You Were Too Shy to Ask
For many developers, database security and Access control is just something that gets in the way of development work. However, several recent security breaches have had devastating consequences and have caused a change in attitude about the value to any organisation of having database applications that meet industry standards for access control and security. The problem, however is in admitting that you have a problem and finding answers to those …Read More
Avoid number conflicts with SQL Sequences
Note: I’ll be presenting on this topic at the Access with SQL Server group online. Please join me on September 13th at 6:30 PM CST, join the group to get an email with all of the meeting details, it’s free!Read More
Insert CreatedBy and CreatedOn in any missing tables
When we design Access with SQL Server solutions we almost always add CreatedBy and CreatedOn to all of our tables, allowing our clients to see who created records in the Access frontend. Script to add CreatedBy and CreatedOn Automatically to tables Many times we inherit SQL databases and we need a quick way to add both fields to all tables in the database. To that end we use the following …Read More
Ways To Do Sequential Numbering in Access
Occasionally we come across project requirements that include the ability to do sequential numbering in Access on a set of data. We cannot just use the auto-number data type because there is no promise of sequentiality but only uniqueness. In other words, if someone creates a record then later deletes it, there will be a gap in the sequence. So, what are our options? There are actually several ways, each …Read More
