By Susan Sales Harkins and Martin W. P. Reid

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 store
SQL 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 diagram
One 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 table
You 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


Access
SQL Server Desktop
Explanation
Field name 
 Column name
 SQL Server Desktop uses the term “column” to refer to fields.
Data types
 Data type
 Many SQL Server Desktop data types include specific field size restrictions.
Field size
 Length
 Access restricts the field size of a numeric or text field. SQL Server uses Length to restrict a character field. Numeric restrictions are included in numeric data types.
Required
 Allow nulls
 A No setting in Access, allows Null values. To allow Null values in SQL Server Desktop, select the Allow Nulls column (see Figure C).

 
Table B: Data Types Comparison


Access
SQL Server Desktop
Comment
Yes/No
 bit
 Integers 0 and 1 only
Number, Byte
 tinyint
 Integer from 0 through 255
 

 bigint
 Integer from -2^63 through 2^63-1
Number, Integer
 smallint
 Short integer from -2^15 through 2^15-1
Number, Long
 int
 Integer from -2^31 through 2^31-1
Number, Decimal 
 decimal
 Fixed precision and scale numeric data from -10^38+1 through 10^38-1
Number, Double 
 float 
 Double-precision floating-point value from -1.79E+308 through 1.79E+308 
Number, Single 
 real 
 Single-precision floating-point value from -3.40E+38 through 3.40E+38 
Date/Time 
 datetime 
 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 query
A 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 much
You 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

Conclusion
Access 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.