Wednesday, May 15, 2013

What are the differences between Access SQL and T-SQL (SQL Server)?

Access SQL and SQL Server's T-SQL have more in common than differences. They share much the same structure, syntax, and many functions. And yet for all that, the differences can be frustrating. There are many times when an experienced Access user will know exactly how to do what they want in Access, but cannot figure out how to do the same thing in T-SQL.

This is especially true when trying to convert an Access query to a Pass-Through query. A Pass-Through query passes the SQL Statement on directly to the Server database (ie: SQL Server, Oracle, etc.). The problem is it must be in the syntax used by the Server database.

 

Basic Differences

I'll start by just listing the basics, but I'll spend some time over the next few months expanding on them. The following is certainly not exhaustive, but they are differences that cause most of the confusion when first trying to use T-SQL.

 

Delimiters

  Type

  Access

  T-SQL

  Numeric   no delimiter   no delimiter

  Text

  " or ' (quote or apostrophe)

  ' (apostrophe)

  Date

  # (pound sign)

  ' (apostrophe)

  Wildcard

  *

  %

  Delimited Identifiers   [..]   [..] or “..”

 

Distinct and DistinctRow

Both Access and SQL Server have the DISTINCT predicate which follows the SELECT, but T-SQL does not have DISTINCTROW.

 

Joins

Both Access and T-SQL use INNER JOIN for inner joins. However, for outer joins, Access uses LEFT JOIN and RIGHT JOIN, while T-SQL uses LEFT OUTER JOIN and RIGHT OUTER JOIN. T-SQL also has a FULL JOIN, which Access lacks. (Note: In the comments, Michal points out that T-SQL can also use LEFT JOIN and RIGHT JOIN.  He’s quite correct.)

 

Built-in Functions

Type

Access

T-SQL

Conditional

IIF()

CASE, IF-THEN-ELSE

Conversion

Cdate(), Clng(), Cint(), etc

CAST()

Formatting

Format()

CONVERT()

Nulls

Nz()

ISNULL()

Aggregates

Min

Max

First

Last

Sum

Avg

Count

No equivalent

MIN

MAX

No equivalent

No equivalent

SUM

AVG

COUNT

COUNT DISTINCT

Current Date

Date(), Now()

GETDATE()

Domain Aggregate Functions

DMax(), DMin, DCount, DLookup, etc

No counterpart. Must use subqueries.

String

InStr()

Trim()

Mid()

CHARINDEX

RTRIM and LTRIM

SUBSTRING

String Concatenation

&, +

+

 

Parameter Queries

Parameter queries as known in Access SQL cannot be converted directly to a Pass-through query. The most common way to simulate parameters is to build the Pass-Through query in VBA code and then execute it.

 

Great Features of T-SQL you should know about

 

Comments

Access does not allow comments in queries, but T-SQL DOES! This is a great feature of SQL Server. There are two methods of commenting:

  • two dashes ( -- ) comments out a single line
  • blocks of text bracketed by /*…*/ comments out multiple lines

 

Variables

Variables are another thing which Access does not have that T-SQL does. Variables provide another way to parameterize a T-SQL query.

 

Count Distinct

As mentioned in the Functions list, T-SQL has a COUNT DISTINCT aggregate function, which Access lacks. You can read more about it here: COUNT DISTINCT in Access

 

Multiple SQL Statement and Temporary Tables

These two things go hand in hand. In Access, a "query" can only have a single SQL Statement. But in T-SQL, you can execute multiple SQL Statements. You can even use a Make-Table query (SELECT..INTO) to create a temporary table, which can be used in subsequent SQL Statements. You can use this feature in an Access Pass-Through query.

 

Conclusion

There are, of course, many more differences between Access SQL and T-SQL. What I've concentrated on here, are those "gotchas" that crop up when trying to convert an Access query to a Pass-through query in T-SQL syntax. I'll discuss the details of these differences in future posts.

Wednesday, May 1, 2013

New Sample: DataDICTIONARY_DisplayControl_Crystal

By Crystal Long

Zip file contains 2 objects: 1 form and 1 module:

  • f_DataDICTIONARY_DisplayControl
  • mod_crystal_DataDICTIONARY_DisplayControl

You can find this sample here:
http://www.rogersaccesslibrary.com/forum/data-dictionary-display-control_topic610.html
Other Samples By Crystal:
http://www.rogersaccesslibrary.com/forum/long-crystal_forum71.html

How to Use this tool:

 
Import the DataDICTIONARY_DisplayControl form and module into a working database, then compile and save, then Open the form: f_DataDICTIONARY_DisplayControl

 

Overview

  1. View Data Dictionary for selected table 
  2. Go to Table Design view of selected table
  3. Open table Datasheet View of selected table
  4. Rename selected table
  5. See if there are text or memo fields where Unicode Compression is not set
  6. See an estimate of record width (sum of the data type sizes, taking compression into account)
  7. Change Display Control of selected fields:
    1. Combo and Listbox to Textbox
    2. Integer to Checkbox

Screen Shots

When you first open the f_DataDICTIONARY_DisplayControl form, you will not see much until you choose a table to look at.

Choose Table

 Menu: Data Dictionary, Display Control

 3 Lookup fields, Need Unicode Compression

Rename Tables 


 Rename Table with bad characters

List of Tables with New Name chosen

 

Delete Lookups

3 Lookup fields to change

Integer to Checkbox


Select Integer fields to set DisplayControl to Checkbox