SQL Server

SQL Server with Access Articles, tips and tricks

Detangling the new SQL Server ODBC and OLEDB drivers and what about Native Client?

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 an [...]

Detangling the new SQL Server ODBC and OLEDB drivers and what about Native Client?2018-11-15T09:04:02-05:00

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 n [...]

Join me for the first of three Access with SQL Server Acdemy sessions September 11 20182018-09-08T12:30:49-05:00

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 suggeste [...]

Designing a T-SQL trigger2019-08-08T07:43:40-05:00

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-val [...]

Drag and Drop In MS Access and Conditional Sorting2018-06-27T20:26:39-05:00

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 t [...]

Questions About SQL Server Security and Access Control You Were Too Shy to Ask2017-02-07T00:18:38-05:00

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!

Avoid number conflicts with SQL Sequences2017-01-13T01:08:55-05:00

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 q [...]

Insert CreatedBy and CreatedOn in any missing tables2016-08-25T22:16:12-05:00

Use SQL Server Native Client 11.0 for the best performance

In our experience in using linked tables with Access, we get the best response using Native Client 11.0 with dsnless tables. You could use ODBC 11 for SQL Server but then you would NOT be able to use ADODB, so we prefer the latter and not the former. Microsoft does a great job of hiding Native Clien [...]

Use SQL Server Native Client 11.0 for the best performance2015-11-11T21:43:33-05:00

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 delet [...]

Ways To Do Sequential Numbering in Access2015-07-30T20:51:20-05:00

Create Delete and Update Audit Tables in SQL Server

This is a modification to an earlier tip I wrote, whereas this code will only record delete changes, my earlier article will record delete and update changes to the data. I found code online by Brett Kaiser and I modified it for my purposes, with a mind to other Access developers I made some changes [...]

Create Delete and Update Audit Tables in SQL Server2015-10-26T19:43:02-05:00

SQL Server update breaks Access application

UPDATE 12/3/14: Please visit Darren's blog for the latest on this issue, than come back here to see some solutions to this problem. You can visit Darren's blog here. My inbox has been flooded by reports around the world of Access suddenly being slow or unusable after the latest set of patches issued [...]

SQL Server update breaks Access application2014-12-03T17:35:15-05:00