Designing Data Access - Professional SQL Server 2005 Reporting Services - page 174

source for reports; you're not going to have the assistance of the graphical query builder and other auto-
mated features of the report designer. Nevertheless, Reporting Services can work with the language syn-
tax and features of most databases; you'll just need to do some of the easy stuff yourself. This section
showcases a few different products we've used as data sources. One point to keep in mind is that the
compatibilities and behaviors will be influenced by a number of factors.
The technique demonstrated a little later in the chapter with an Access query (see “Building a Query in
a String Expression”) is a universal pattern that applies to all database products. I strongly recommend
that you take a look at this technique because it will be useful to you at some point, regardless of the
data source you will use for reporting.
Microsoft Access
Microsoft Access is built on top of the JET Database Engine with data stored in a single MDB file. This
is simple and convenient for small, portable databases. However, Microsoft continues to take steps to
replace JET databases with SQL Server and the desktop implementations of SQL. These include the SQL
Server 2000 Desktop Database Engine (MSDE) and SQL Server 2005 Express Edition. As a desktop appli-
cation, Access may also be used as a front end to SQL databases. If you have the luxury of building a
new database solution, it may be best to use one of these newer products in place of older Access data-
bases. But if you have existing solutions based on older Access databases, it will likely be easier to con-
tinue to work with them in their present form.
There are two standard data providers that may be used to connect to Access databases. The JET 4.0
.NET OLE DB provider is newer and should be a little more efficient than using the older Access ODBC
driver. The fact is that the data provider is rarely going to be a performance bottleneck, so this is proba-
bly a moot point. The OLE DB provider is typically easier to use and more efficient. One of the nice fea-
tures of the new data provider is that it will accept Transact-SQL and translate it into Access-specific
syntax. Although Access SQL and Transact-SQL are very close, there are some subtle differences. This
feature enables the report designer to utilize the Transact-SQL graphical query builder when a data set
uses a JET data source.
Figure 5-69 shows the Connection Properties dialog used when defining an Access database connection
using the JET OLE DB provider:
Note that the default security credentials used with an unsecured Access database are the Admin user
with a blank password. Even if you were to explicitly provide this information and check the Blank
password box, the dialog doesn't show these values. This is so because the data provider knows to use
default credentials when the database hasn't been secured.
The connection string and credentials are shown in Figures 5-70 and 5-71.
Select the Credentials tab to view or modify the user authentication information.
Access has some minor quirks that you should be aware of. Any file-based data source can present a
challenge for Reporting Services because the service must have the necessary security access to open
the database file. If the MDB file is on the report server, this shouldn't be a concern, but if the file is on
another network share, it may be. If you get file-sharing errors, make sure that Reporting Services ser-
vice runs using a network account that has privileges to open the Access database file and its containing
folder.