Data Management optimize

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.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments