Precautions and methods for upgrading an Access database to a SQL database

Many Access and SQL Server developers often face the problem of upgrading an Access database to the SQL Server database. Because of the existing Access Upgrade Wizard, this transformation process becomes quite simple, especially when you create an ADP associated with SQL Server data. However, the Wizard is not perfect, and the problems to be solved still exist.


First, some objects are not simple upgrades, so you have to deal with them manually. Second, many Access features, such as some query types, objects, and specific data types, can cause errors if you are not prepared for the upgrade. Now, let's discuss the problems that may be encountered during Database Upgrade. I will provide some general guidance methods to solve the problem. Finally, you must spend some time and energy applying this knowledge to development.

Which cannot be upgraded?
Before dealing with actual problems, let's take a look at objects that cannot be upgraded at will. They include the following:

Cross tabulation Query
Any query that contains the SQL DISTINCTROW keyword
All hidden objects
Query table data as parameters (these tables can be upgraded, but they cannot run correctly)
Pass-Through query
SQL data definition language query (such as Create TABLE, Alter TABLE, and Drop statements)
These Access objects require specific processing. Specifically, you will create a comparable SQL Server object. In addition, SQL Server does not support Jet security, so you must use Windows Authentication and/or SQL Server security mechanisms.

Issues included
Before upgrading the database, if you know where errors may occur and how to handle the errors, the possibility of errors caused during the Database Upgrade will be greatly reduced. The best advice I can provide for Database Upgrade is to make the most complete plan before development. Now, I will list the problems that may occur during Database Upgrade-if you are not prepared for the plan.

Unsupported date

There is a big difference between Access and SQL Server about the date. Access supports a wide range of dates, from January 1, January 1-9, 100 to January 1, December 31, 999. On the contrary, SQL Server supports dates from January 1, January 1-9, 1753 to January 1, December 31, 999. The Database Upgrade Wizard cannot update tables that contain dates not supported by SQL Server. This means that you must manually process these dates before the upgrade. Fortunately, this problem only affects a few databases.

Query related to Table Control
Developers usually use table-controlled queries to limit or query a data source. A table provides multiple options for displaying data in a specific report. For example, the SQL Select statement contains 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 limit the data in the report, you can enter a start date and end date to the list (DateFrom and DateTo ). Other code can open and display records between two dates that meet user input.

Because this query method is processed by Jet, the problems in the table can be quickly solved. However, when the database is upgraded, SQL Server does not involve table control, and the result is generally a query failure. To correct this query method, the developer must change the table. We recommend that you use the input parameter attribute and pass the value to the SQL Server storage program.

Cross tabulation Query
SQL Server does not support the Jet TRANSFORM statement, which makes it possible to query a cross table. For example, the Database Upgrade Wizard supports the following query methods:

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])
Partition "Qtr" & DatePart ("q", [OrderDate],) In ("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4 ")



Fortunately, you don't have to re-compile an Access Cross-Table query using the Transact-SQL (T-SQL) CASE keyword in SQL Server. The following Select statements describe the syntax for recreating a cross-tabulation query in T-SQL mode:

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 MERs inner join orders
ON CustomerID = Orders. CustomerID



Hide objects
All hidden objects are ignored during Database Upgrade. In this regard, your best solution is to use the GetHiddenAttribute attribute of the program object to check the object. For example, the following code uses this method to determine whether an object is hidden.
Dim IsHidden As Boolean
If Application. GetHiddenAttribute (objtype, objname) Then
IsHidden = True
End If

If a specific object is hidden, the Boolean value of IsHidden is True.

Table with index

The Database Upgrade Wizard does not support tables without indexes or other restrictions. The Upgrade Wizard can update a table without an index, but it can only be a read-only table after conversion. Fortunately, it is easy to solve this problem: add an index to every table without an index. Once you have upgraded the database, remember to delete the added index.

Database Upgrade procedure
Once you have prepared everything and are sure you can start the operation. The Database Upgrade Wizard provides three options:

Output the Access table to SQL Server and link it to the Access Database
Use Pass-Through to query tables that communicate with SQL Server's background Server
Move the entire Access database to an Access database project (contact SQL Server only)
To start the Database Upgrade Wizard, select the database function from the tool menu bar, and then select the Upgrade Wizard from the sub menu. The first Panel of the wizard provides two options: you can create A new SQL Server database to store Access tables, as shown in A, or you can add tables to A ready-made SQL Server database. If you select a ready-made SQL Server database, you need to enter a data service name (DSN ).
Figure:


Choose to create a new database or use a ready-made Database

The second panel requires information about the SQL Server sample. In addition, you must authenticate the security mechanism (if any) and provide a default name for the new database, as shown in B.
Figure B



Name a new database
At this point, you can copy the required table to SQL Server. You can also point out that the completed part will be a complete ADP or a linked table, as shown in C.

Figure C



Specify an ADP or a linked table


Avoid troubles

Every developer has different experience in Database Upgrade operations, so it cannot be guaranteed that the first operation can be successful. However, if you follow the rules provided in this article, you should encounter fewer errors, even if you encounter them, you can easily correct the errors and continue to operate.