Most Access and SQL Server developers eventually face the prospect of upsizing an Access database to SQL Server. Thanks to the Access Upsizing Wizard, most of the process is painless, especially if you’re creating a new ADP to interact with SQL Server data. However, the wizard isn’t perfect, and there are still problems to be solved.

First, some objects simply won’t upsize, so you’ll have to work with those manually. Second, many Access features—such as a few query types, objects, and specific data types—cause errors if you don’t prepare for them before upsizing. Let’s discuss some of the trouble spots you may face during the upsizing process. I’ll also offer some general guidelines that should reduce the number of errors and, as a result, the amount of time and effort you must dedicate to the project.

What won’t upsize
Before tackling the actual problems, let’s review the objects that simply won’t upsize. These include the following:

  • Crosstab queries
  • Any query that contains the SQL DISTINCTROW keyword
  • All hidden objects
  • Queries that reference form values as parameters (These forms may upsize, but they won’t work properly.)
  • Pass-Through queries
  • SQL Data Definition Language queries (e.g., CREATE TABLE, ALTER TABLE, and DROP statements)

These Access objects will need special handling. Specifically, you’ll have to build the comparable SQL Server object yourself. In addition, SQL Server doesn’t support Jet security at all—you’ll have to use Windows Authentication and/or SQL Server’s security.

The trouble spots
If you know what causes the error and how to deal with it before upsizing, chances are the upsizing process will return few if any errors. The best advice I can give about upsizing is to plan well in advance. Now I’ll expose the areas that will cause you problems when upsizing—if you don’t prepare for them first.

Unsupported dates
There’s a huge discrepancy between the dates supported by SQL Server and Access. For some reason, Access supports a much larger range of dates, ranging from January 1, 100, through December 31, 9999. In contrast, SQL Server supports dates from January 1, 1753, through December 31, 9999. The Upsizing Wizard will fail to upsize any table that contains a date unsupported by SQL Server. That means you have to locate and deal with those dates before upsizing. Fortunately, this problem affects few databases.

Queries that reference form controls
Developers often use queries that reference controls on forms to limit or query a data source. A form might present several options for restricting the data displayed in a specific report. For example, the SQL SELECT statement (based on tables in Northwind) incorporates user input:
 
SELECT Orders.RequiredDate, Orders.ShippedDate, Orders.Freight,
    Orders.ShipName, Orders.ShipAddress, Orders.OrderDate
FROM Orders
WHERE
 Orders.OrderDate Between [Forms]![DateFilter]![DateFrom] And [Forms]![DateFilter]![DateTo]));

To restrict the data that reaches the report, the user enters a beginning and ending date into form controls (DateFrom and DateTo). Additional code then opens the report displaying only the records that fall within the two dates supplied by the user.

Because this query is processed locally by Jet, the form reference can be quickly resolved. However, when upsized, SQL Server can’t reference the form controls, and consequently, the query fails. To fix the query, the developer must modify the form. I recommend you use the Input Parameters property to pass values to a SQL Server stored procedure.

Crosstab queries
SQL Server doesn’t support the Jet TRANSFORM clause—the clause that makes a crosstab query possible. For example, the Upsizing Wizard won’t upsize the following query:
 
TRANSFORM Sum(CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100)
    AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID =
    [Order Details].ProductID
WHERE Orders.OrderDate Between #1/1/1997# And #12/31/1997#
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT “Qtr ” & DatePart(“q”,[OrderDate],1,0) In (“Qtr 1″,”Qtr 2″,”Qtr 3″,”Qtr 4”)

The good news is you can rewrite an Access crosstab query in SQL Server using the Transact-SQL (T-SQL) CASE keyword. The following SELECT statement shows the basic syntax to recreate a crosstab query using T-SQL where the SUM function sets the date range:
SELECT Customers.CustomerID, Customers.CustomerName
    SUM (Case When Orders.Orderdate BETWEEN ’01-Jan-1990′ AND ’31-Dec-1996′
    Then [UnitPrice]*[Quantity] Else 0 End) as 1997)
FROM Customers INNER JOIN Orders
ON CustomerID=Orders.CustomerID

Hidden objects
All hidden objects will be ignored when upsizing. Your best workaround is to check objects using the Application object’s GetHiddenAttribute property. For example, the following code uses this property to determine whether an object is hidden:
 
Dim IsHidden As Boolean
If Application.GetHiddenAttribute(objtype, objname) Then   
  IsHidden = True
End If

If the specified object is hidden, the IsHidden Boolean variable will equal True.

Tables need an index
The Upsizing Wizard doesn’t like tables that don’t have at least one index or unique constraint. The Upsizing Wizard will upsize an indexless table, but it will be read-only in SQL Server. Fortunately, the solution to this requirement is simple: Add an index to each table that doesn’t already have one. Once you’ve upsized the database, remember to remove the index.

The upsizing task
Once you’ve prepared your database and are ready to upsize, the Upsizing Wizard offers three choices:

  • Export the Access tables to SQL Server and link them to the Access database
  • Use Pass-Through queries to communicate with the back-end server tables in SQL Server
  • Move the entire Access database to an Access Data Project (which works only with SQL Server)

To launch the Upsizing Wizard, select Database Utilities from the Tools menu, and then choose Upsizing Wizard from the resulting submenu. The wizard’s first pane offers two options: You can create a new SQL Server database in which to store the Access tables, as shown in Figure A, or you can add the tables to an existing SQL Server database. Choosing an existing SQL Server database will require entering a Data Server Name (DSN), so be prepared.

Figure A
Choose between creating a new database and using an existing one.

The second pane requests information about the SQL Server instance—just where is the database going? In addition, you must identify security (if any) and provide a default name for the new database, as shown in Figure B.

Figure B
Name the new database.

At this point, you can identify the tables you want copied to SQL Server, You’ll also indicate whether the finished product will be a full ADP or linked tables, as shown in Figure C. I recommend the full ADP because of all advantages a true client/server environment offers and the availability of the Access graphical tools.

Figure C
Specify an ADP or linked tables.

Avoid troublemakers
Every developer will have a different upsizing experience, so there’s no way to guarantee success the first time out. However, if you follow the guidelines I’ve provided in this article, you should have few problems. Even if you miss something, it’s easy enough to go back, fix the problem, and try again.

Try Upsizing Pro

If you’re worried about upsizing a database, try Upsizing Pro from Superior Software for Windows. This utility will check your Access file and provide an extensive report that identifies potential problems.