To upgrade an existing Access database to SQL

Many access and SQL Server developers are often faced with the problem of upgrading an Access database to a SQL Server database. Because of the existing Access Upgrade Wizard, the process of this transformation becomes quite simple, especially if you are creating an ADP associated with SQL Server data. However, the Wizards are not perfect, and the problems that need to be solved are significant.

First of all, some objects are not simply upgrades, so you have to deal with them artificially. Second, many access features--such as query types, objects, and specific data types--can lead to errors when you're not ready to upgrade. Now, let's take a look at some of the possible problems in the database upgrade process, and I'll provide some general guidance on how to solve the problem, and finally, you have to spend a certain amount of time and effort applying that knowledge to development.

What cannot be upgraded?
Before dealing with the actual problem, let's look at the objects that we can't upgrade at will, including the following:

Cross-Table Query
Any query that contains the SQL Distinctrow keyword
All Hidden objects
Queries for tabular data as parameters (these tables can be upgraded but they do not run correctly)
Pass-through query
SQL data Definition language query (such as CREATE TABLE, ALTER table, and drop statement)
These access objects require specific processing. Specifically, you will build a comparable SQL Server object and, in addition, SQL Server does not support the Jet security feature, so you must use Windows authentication and/or SQL Server security.

The problem points included
Before the database is upgraded, if you already know which areas will likely cause errors and know how to handle the errors that have occurred, the likelihood of errors resulting from the database upgrade process will be greatly reduced. The best advice I can offer for a database upgrade is to make the most complete plan before developing. Now, I'm going to enumerate the problems that might arise during a database upgrade--If you're not ready to plan ahead.

Unsupported Date

There are big differences between access and SQL Server about dates. Access supports a wide range of dates, from 100 years from January 1 to December 31, 9999. Instead, SQL Server supports dates from January 1, 1753 to December 31, 9999. The Upgrade wizard for the database cannot upgrade a table that contains dates that SQL Server does not support. This means that you have to deal with these dates manually before you upgrade. Fortunately, this problem affects only a handful of databases.

Queries related to Table control
Developers often use table-controlled queries to limit or inquire about a data source. A table can provide a variety of options for displaying data in a particular report. For example, the SQL SELECT statement contains the user's 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 qualify the data in the report, users can enter a start and end date to the list (Datefrom and Dateto). Other code can open and display records that meet the two dates entered by the user.

Because this type of query is handled by jet, the problems created in the table can be resolved quickly. However, when a database is upgraded, SQL Server does not involve table control, and the result is typically a query failure. To correct this query, the developer must change the table. I recommend that you use the input parameter properties and pass the values to the SQL Server stored program.

Cross-Table Query
SQL Server does not support the Jet transform statement-This statement makes it possible to make a crosstab query. 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 the 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")

Fortunately, you do not need to rewrite an Access crosstab query in SQL Server using Transact-SQL (T-SQL) Case keywords. The following SELECT statement describes the syntax for re-constructing a crosstab query using T-sql:

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 are ignored during the database upgrade process. Your best approach to this is to use the GetHiddenAttribute property 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 particular object is hidden, the Ishidden boolean variable is true.

Table containing indexes

The Database Upgrade Wizard does not support tables that do not have indexes or other restrictions. The Upgrade Wizard can upgrade a table without indexes, but it can only become a read-only table after conversion. Fortunately, the solution to this problem is simple: add an index to each table without an index. Once you have finished upgrading the database, remember to delete the added index.

Steps for Database Upgrade operation
Once you have made all the preparations and make sure that the operation can begin. The Database Upgrade Wizard can provide you with three options:

Output an Access table to SQL Server and link to an Access database
Use pass-through to query the tables of SQL Server's background servers to communicate with each other
Move an entire Access database to an Access database project (which only contacts SQL Server)
To start the Database Upgrade Wizard, first select the database features from the Tools menu bar, and then select the Upgrade Wizard from the submenu. The first panel of the wizard offers two choices: You can create a new SQL Server database to hold an Access table, as shown in figure A, or you can add a table to a ready-made SQL Server database. Selecting an out-of-the-box SQL Server database will require you to enter a data service name (DSN).

Figure A

choose to build a new database or use a ready-made database The second panel asks for information about the SQL Server paradigm. In addition, you must authenticate the security mechanism (if present) and provide a default name for the new database, as shown in Figure B. Figure B

named

for a new database

At this point, you can copy the required form to SQL Server, and you can also point out that the completed section will be a full ADP or a linked table, as shown in Figure C.

Figure C

Indicates an ADP or a linked table

Avoid trouble

Each developer has experience with different database upgrade operations, so there is no guarantee that the first operation will achieve success. However, if you follow the rules provided in this article, you should have fewer errors, and even if you do, you can easily fix the error and continue to operate.