SQL Server

SQL Server with Access Articles, tips and tricks

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

Whether to exists or not exists, that is the question…2018-02-19T18:36:01-05:00

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

Mixed Blessings of SQL Triggers2013-09-10T00:45:07-05:00

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

New Access 2013 book, plus meet the authors!2013-08-23T20:06:53-05:00

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

Getting a list in comma delimited format with T-SQL2014-10-22T02:23:57-05:00

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

DAO or ADODB? Which one should you use and when?2015-07-03T19:58:25-05:00

Why you should always use a recordset when executing stored procedures

I've talked about using a recordset with stored procedures before, but this time I wanted to emphasize the importance of always using a recordset when executing a stored procedure from Accerss VBA. Command Object does not cut it Usually a programmer will use a command object to execute a stored proc [...]

Why you should always use a recordset when executing stored procedures2013-04-01T01:50:13-05:00

I’ve started a new LinkedIn group: “MS Access with SQL Server”

For some time now I've been posting articles on LinkedIn MS Access groups. The posts vary the gamut, from Access topics to management posts. I've enjoyed the discussion and feedback on the boards, and now I've decided to create a new group: The reasons why I've decided to create my own group on this [...]

I’ve started a new LinkedIn group: “MS Access with SQL Server”2013-03-18T01:58:14-05:00

Securing your ADO connections

A while ago, I wrote about securing the ODBC connections which was published on Access team's blog. Also, Juan recently wrote about the alternate method of deleting/recreating linked tables in a four part article. But what about ADO connections? A typical approach for creating an ADO connection may [...]

Securing your ADO connections2012-06-04T08:21:32-05:00

Part 2: Linking tables using a SQL Server table

Author note: This is part 2 of a series on DSN-less tables in Access. You can review part one here. Part three here. Part four here. In last week's post I talked about using a single table in your SQL Server database to easily manage security, in today's post I'm going to take it one step further an [...]

Part 2: Linking tables using a SQL Server table2012-04-04T09:00:34-05:00

DSN-less tables; a better way…

Author note: This is part 1 of a series on DSN-less tables in Access. You can review part two here. Take a look at part three here and part four here. In my first post on this topic, I sent you to Doug Steele's great article on how to do DSN-less table connections. Today I'm starting a new series of [...]

DSN-less tables; a better way…2012-03-27T09:04:34-05:00

Why using unbound forms are a bad idea

Many of you know that I'm a big fan of Access with SQL Server, and if you're a frequent blog reader you will also know it's not easy to optimize the relationship between them, it takes work but it's very rewarding when they're working great.  Some developers however go to the extreme and use unbound [...]

Why using unbound forms are a bad idea2012-03-13T20:26:42-05:00