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.
|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.
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.
|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
Table B: Data Types Comparison
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.
|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.)
|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
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.
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.