Create a Linked Server to an Access Database

On a recent project I've had the need (unfortunately) to pull data from Access into SQL Server. We implemented this using Linked Server to query the Access database. In case I need to find it again, here's the code...

The Linked Server can be created using SQL Enterprise Manager, but we needed to script it.

USE MASTER
GO

-- Remove linked servers and drop associated logins
EXEC sp_dropserver 'Link_MyDatabase', 'droplogins'

-- Create a linked server
EXEC sp_addlinkedserver 'Link_MyDatabase', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'D:\MyDatabase.mdb'

-- Provide login credentials for the access database (no username or password required in this case)
EXEC sp_addlinkedsrvlogin 'Link_MyDatabase', 'false', NULL, NULL, NULL

To query the database, we can add the linked server to the front of the table name (no database name or owner name required). Sometimes we needed to query around bad data before it gets to the ODBC driver to prevent data corruption issues and ODBC driver errors. Date data proved particularly troublesome in this regard. To do so, we use an OPENQUERY statement, which runs the query on Access before it gets to the OLEDB driver.

-- Query data directly
SELECT *
FROM Link_MyDatabase...MyTable

-- Query data using OPENQUERY to avoid invalid date values
SELECT *
FROM OPENQUERY(WXIS_OPS, 'select * FROM MyTable WHERE BadDate > #01/01/1900#')

Alternating Rows In SQL Reporting Services

Reporting Services does not support alternating row colors out of the box. But you can get the effect using a little custome code and expressions. Most object properties support expressions. For example, we can specify the background color for a cell as a color (i.e. "White") or a code expression.

To get alternating row colors, I use two functions which I add to the report code module. To access the report code module, view the report options, select the Code tab, and copy the following code into the code window.

Shared AltColorRowCounter as Long

Public Function GetAltColor(color As String, altColor As String, bumpRowCounter As Boolean) As String
  
  If (bumpRowCounter = True) Then
    AltColorRowCounter += 1
  End If

  If (AltColorRowCounter Mod 2 <> 0) Then
      Return color
  Else
      Return altColor
  End If
  
End Function

Public Function ResetAltColor(color As String) As String
  
  AltColorRowCounter = 0
  Return color

End Function

The code window in Reporting Services is painfully small with no code support. I usually do my coding in a script editor and then paste the code into the code window when I'm done. Since Reporting Services accepts most Visual Basic .Net code, I use Visual Studio) to create a .vb file, then do my code editing there. That way I at least get some language intellisense support, formatting and undo functionality to make code editing more pleasant.

The code contains 2 functions and a shared module-level variable. The shared variable AltColorRowCounter is used to hold a row counter, used to determine if we're on an odd or even row. We use this counter to alternate row or column colors. Reports are rendered from top to bottom, left to right. The counter is bumped and reset by our code as the report is rendered, modifying the colors accordingly by calling the following functions.

The ResetAltColor() fuction is used to reset the counter. It returns the string passed in as the color, and I normally use it on a background expression. For example, if I want the alternating rows within a group, and usually like to reset the counter on each group header row. In the group header, I call the reset method with the background color of my group header (in this case LightSteelBlue). To do this, I set the Background expression of the group header. In this example, I set the expression to =Code.ResetAltColor("LightSteelBlue").

On the rows, I call the GetAltColor() function. I pass it the 2 colors I want to alternate, and a third boolean parameter I've named bumpRowCoutner. The bumpRowCounter parameter is used to increment the counter. I normally set it to True on the first column in the row, and leave it False for all additional columns. In this case, I want alternating row colors of White and LightGrey. On the first detail row column, I set the Background expression to =Code.GetAltColor("White", "LightGrey", True). On all the other columns in the row, I set the Background expression to =Code.GetAltColor("White", "LightGrey", False).

This gives me alternating row colors. If I set it to True for each column, I'll get alternating column colors, which can also be a nice effect to have in your bag of tricks. These two functions can be used to give you a wide variety of alternating color effects.