SQL Server

SQL Server with Access Articles, tips and tricks

Replicating made simple: SQL Azure Data Sync

On May 13th, at 6:30 PM CST, I will be speaking about how you can easily create a solution that enable you to have your Access program working across broad geographic regions by leveraging SQL Azure Data Sync which is still in preview. In the talk, we will demonstrate how to get set up with replicat [...]

2014-05-12T00:07:46-05:00May 12th, 2014|

Running Transactions in a Stored Procedure

A lot of times, I write a stored procedure to do several things. More often than not, I might be doing mass inserts, updates or deletions. Now, I'm not your average suspenders-and-belt guy. No, I go one step further and insist on wearing an overall over my suspenders and my belt, thank you very much [...]

2015-09-11T23:05:09-05:00April 28th, 2014|

Working with XML Data Efficiently – Part Two

This is part two of our two-part series on working with XML data with a SQL Server backend. Part one can be found here. In part one, we showed how much simpler and effective it was to manipulate XML data by doing it directly in the SQL Server layer as opposed to front-end or middle-layer code. The i [...]

2022-04-18T16:31:06-05:00January 21st, 2014|

Working with XML Data Efficiently – Part One

This is part one of a two-part series that discusses working with XML data with an SQL Server backend. Part two is discussed here. I've personally found XML files to be something of a bugbear. They're going to require some kind of transformation to get it in a format that's useful for importing or e [...]

2022-08-19T10:16:04-05:00January 14th, 2014|

Easily update all tables that have the same field name

We talked about concatenating multiple records into a single string using FOR XML PATH and we also talked about EXISTS clause. Here's a practical example that incorporates both. In one of our projects, we have an archive database that contains historical data of select tables from the production dat [...]

2013-11-05T20:12:38-06:00November 5th, 2013|

Easily create a Beta environment in SQL Server

We are specialists in creating Access to the cloud solutions using SQL Server. We therefore maintain a number of databases and our clients are constantly asking us for improvements. Of course, we require that those go through a full cycle of testing and deployment: We copy an existing database using [...]

2013-10-14T20:12:28-05:00October 15th, 2013|

Whether to exists or not exists, that is the question…

This is second part of 2-part series on SQL clauses. Juan covered the IN() in the first part. We now turn to a close sibling, EXISTS clause. As Juan demonstrated, IN() can be useful for some situations where we want to match a subset of another table without necessarily changing the output due to jo [...]

2021-06-10T10:02:29-05:00October 2nd, 2013|

Mixed Blessings of SQL Triggers

Sometime we take over a project where the original developer used SQL Triggers on the tables and unfortunately, we tend to find that more often, the triggers are not written in most effective manner. I'd like to highlight few common mistakes I see made with using the triggers: Assuming only one row [...]

2013-09-10T00:45:07-05:00September 16th, 2013|

New Access 2013 book, plus meet the authors!

Over the last year, I've been working on a book with Access experts and MVPs Teresa Hennig, George Hepworth and Doug Yudovich, focused on practical examples and techniques for building powerful Access applications. The book covers both new web apps and new solutions to client solutions. Here are few [...]

2022-07-26T03:24:23-05:00August 23rd, 2013|

Getting a list in comma delimited format with T-SQL

Sometime we want to get data in a list. A good example is getting a list of orders that's coming up for delivery and has other orders that are past due. We would like to know what those upcoming orders are so we can ensure that the past due accounts are settled prior to actually delivering the order [...]

2014-10-22T02:23:57-05:00July 30th, 2013|

DAO or ADODB? Which one should you use and when?

On my Access with SQL Server group at LinkedIn, one of our members posed the follwoing question: ADO or DAO for new development work? Let's break it down The question implies DAO or ADODB are mutually exclusive, but they are not. You could easily create an Access app using just DAO that would work j [...]

2015-07-03T19:58:25-05:00April 9th, 2013|
Go to Top