The subtle differences between SQL Server Desktop and Access objects

Once you make the move to SQL Server Desktop, you'll notice that most objects are different from Access objects, even if just a little. Here are a few SQL Server Desktop objects and how they compare to their Access counterparts.

SQL Server Desktop combines the familiar and easy-to-use Access interface with SQL Server's power and stability. If your department supports SQL Server, SQL Server Desktop will offer a flexible and useful addition to your supporting toolbox. Besides providing a familiar interface for your developers and users, there are a number of reasons you might want to consider adding SQL Server Desktop: It's a great training tool for SQL Server, it's easy to learn and requires little administrative support, and it's free. If you have Access, you have SQL Server Desktop.

Once you move to SQL Server Desktop, you’ll notice that most objects—tables, queries and forms—are different from Access objects, even if just subtly. I’ll introduce you to a few SQL Server Desktop objects and compare them to their Access counterparts. Throughout this file, I'll refer to Access (.MDB) files as Access and SQL Server Desktop (.ADP) as Desktop, respectively.

What's in storeSQL Server Desktop files are referred to as Access projects (ADP files); this suggests that Desktop databases are really just Access files. The interfaces are similar and some objects are more alike than they are different, but Access and SQL Server Desktop use different file formats.

SQL Server Desktop produces an .ADP file that some developers refer to as an Access project. You'll probably feel right at home in a project if you're familiar with Access. In fact, you might almost forget that you're not working in Access. Despite their similarities, several differences will be obvious right away:

· Desktop has an extra object, the Database Diagram.

· Desktop tables have more properties.

· Desktop queries are more complex and offer a number of new objects for viewing and manipulating data.

· Desktop forms have more properties.

Just like Access, Desktop organizes objects in the Database window shown in Figure A. However, all of the objects aren't stored in the project. Only forms, reports, macros, and VBA modules are stored in the .ADP file. All other project objects are stored on the database server. Reports are essentially the same and aren't included in this discussion.

Figure A

Here is the Desktop Database window.

A new object—the database diagramOne of the first things you may notice is the new shortcut—Database Diagrams—on the Object bar. You will use diagrams to create and modify tables, relationships, indexes, and constraints.

There's no Access counterpart for the database diagram object, although the Desktop interface and features are similar to both the Access Relationships window and the Table Design window. Figure B shows the NorthwindCS (a sample database that comes with SQL Server Desktop) Relationships diagram.

Figure B

A project displays relationships in a database diagram object.

The heart of both formats: The tableYou can't readily tell the difference between an Access and a Desktop datasheet (table). However, the Desktop Table Design window, shown in Figure C, is different from the Access counterpart. Most significantly, a Desktop table has more field properties, including:

· Scale: Specifies the total number of digits for a value’s decimal component.

· Precision: Specifies the total number of digits in a column.

· Identity: Returns a unique value for each record. In Access, you’ll find the same functionality with the AutoNumber data type.

· Identity Seed: Sets the initial value for an Identity column. An Access AutoNumber field defaults to an initial value of 1.

· Identity Increment: Sets the incremental value between each identity value in an Identity column. An Access AutoNumber field defaults to an incremental value of 1.

· Is RowGrid: Returns a globally unique identifier (GUID) for each value.

· Formula: Stores the expression used to generate values in a computed column.

· Collation: Sets the sort order for a character column.

Figure C

A Desktop table in design view

Table A compares field properties that are similar, but named differently. Table B compares Desktop and Access data types.Table A: Comparison of table properties

Valid date or time (or both). Access supports year values from 100 through 9999. SQL Server Desktop supports year values from 1753 through 9999

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079

Currency

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit

money

Monetary data values from -2^63 through 2^63-1

binary

Integer data from -2^63 through 2^63-1

varbinary

Variable-length binary data with a maximum length of 8,000 bytes

Memo

text

Any character, but limited to 2.14 GB

Text

varchar

Variable-length non-unicode data with a maximum of 8,000 characters

char

Fixed-length non-unicode character data with a maximum length of 8,000 characters

nchar

Fixed-length unicode data with a maximum length of 4,000 characters

ntext

Variable-length unicode data with a maximum length of 2^30-1 characters

nvarchar

Variable-length unicode data with a maximum length of 4,000 characters

OLE Object

image

Picture of object limited to 2.14 GB

sql_variant

Can be used to store different data types

timestamp

Generated binary numbers that are guaranteed to be unique within the current database (8 bytes)

uniqueidentifer

A globally unique identifier (GUID)

Desktop also offers more table properties than Access:

· Tables: Set or view the Identity column, rename the table, and so on

· Relationships: View, create, delete, or modify relationships

· Index: View, add, remove, or modify a table's indexes. (Read more on this topic in the Indexes sidebar.)

To display a Desktop table's properties, right-click the table in the Database window and select Properties from the resulting submenu. In the Properties window shown in Figure D, click a tab to view and set corresponding properties.

Figure D

Desktop tables offer more properties than Access tables.

The more complex queryA Desktop query is more complex than an Access query. Desktop uses Transact-SQL (T-SQL), whereas the Access query uses Jet SQL. T-SQL is similar to more formal programming languages in that it supports constructs, such as CASE and IF THEN ELSE statements, and procedural blocks of code.

Both systems offer a graphical interface for building queries: Access has the Query Design window and Desktop offers the Query Builder. Using the Query Builder, you can access and build the following Desktop query objects:

· In-line function: Returns the result of a single SELECT statement as a table data type.

· View: Is similar to an Access query, in that it restricts the data you see.

· Stored procedure: Is a block of T-SQL code that's saved on the server.

· Text-stored procedure: When you want to work with the procedural aspects of T-SQL (e.g., IF THEN ELSE), use a text-stored procedure.

· User-defined function (scalar and table): These are custom functions that you create. A scalar function returns a single value and a table function returns a table data type; both can be used in T-SQL statements.

These objects extend the querying abilities of Desktop far beyond those of Access. Access has these capabilities, but in most cases, you must rely on Visual Basic for Applications (VBA) code—you certainly can't perform the same tasks in an Access query.

You'll create these objects, just as you would a query in Access, by choosing Query from the Insert menu (or by clicking a shortcut in the Database window). As you can see in Figure E, Desktop offers a number of new query options. (If you're familiar with SQL Server, these objects are probably familiar to you.)

Figure E

There are several new query-type objects in Desktop.

The form hasn't changed muchYou won't see much difference between an Access and a Desktop form, but Desktop forms have more properties. I've listed most of the new properties in Table C. In addition, note the last button on the navigation bar. When the form is loading and records are being downloaded from the server this button is red.Table C: Properties new to forms

Property

Description

Max records

The maximum number of records returned to the ADP, which defaults to 10,000

Server filter

An SQL WHERE clause used to filter the underlying data source

Server filter by form

Displays a blank filter form that is similar to the Acess Filter By Form feature

Unique table

Indicates which table is updateable when the form is bound to multiple data sources

Resynch command

Repopulates the form with updates

Input parameters

Passes parameters to a stored procedure

ConclusionAccess and SQL Server Desktop are very similar systems, but Desktop is more powerful and stable as it's based on SQL Server technology, not Jet. Fortunately, if you know how to use Access, learning Desktop will be easy because the interface and features are so similar.