Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the matters needing attention and methods for upgrading Access database to SQL database". In daily operation, I believe that many people have doubts about the matters needing attention and methods of upgrading Access database to SQL database. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "what are the precautions and methods for upgrading Access database to SQL database"! Next, please follow the editor to study!

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, this transition process becomes quite simple, especially if you create an ADP associated with SQL Server data. However, the guide is not perfect, and there are still many problems to be solved.

First of all, some objects are not simple 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 if you are not ready to upgrade. Now, let's discuss the problems that may be faced during the database upgrade process. I will provide some general guidance to solve the problem. Finally, you must spend some time and energy applying this knowledge to development.

Which cannot be upgraded?

Before dealing with practical problems, let's take a look at objects that cannot be upgraded at will, including the following:

Crosstab query

Any query that contains the SQL DISTINCTROW keyword

All hidden objects

Queries for table data as parameters (these tables can be upgraded, but they do not run correctly)

Pass-Through query

SQL data definition language queries (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 features, so you must use Windows authentication and / or SQL Server security mechanisms.

Included problem points

Before the database upgrade, if you already know what may cause errors and know how to deal with the resulting errors, the possibility of errors caused by the database upgrade process will be greatly reduced. The best advice I can provide for database upgrades is to make the most complete plan before development. Now, I will list the problems that may arise during the database upgrade process, ── if you are not prepared before planning.

Unsupported date

With regard to dates, there is a big difference between Access and SQL Server. Access supports a wide range of dates, from January 1, 100 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 tables that contain dates that are not supported by SQL Server. This means that you have to deal with these dates manually before upgrading. Fortunately, this problem affects only a small number of databases.

Queries related to table control

Developers often use table-controlled queries to restrict or ask about a data source. A table can provide multiple 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 limit the data in the report, the user can enter a start and end date into the list (DateFrom and DateTo). Other code can open and display records between two dates that satisfy user input.

Because this query method is handled by Jet, the problems generated in the table can be solved quickly. However, when the database is upgraded, SQL Server does not involve table control, and the result is usually a query failure. In order to fix this query, the developer must change the table. I recommend that you use the input parameter property and pass the value to the SQL Server stored program.

Crosstab query

SQL Server does not support the Jet TRANSFORM statement ──, which makes a crosstab query possible. 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])

PIVOT "Qtr" & DatePart ("Q", [OrderDate], 1 OrderDate 0) In ("Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4")

Fortunately, you don't need to rewrite an Access crosstab query in SQL Server using the Transact-SQL (T-SQL) CASE keyword. The following Select statement describes the syntax for recreating a crosstab query using T-SQL:

Select Customers.CustomerID, Customers.CustomerName

SUM (Case When orders.Orderdate BETWEEN'01 Latin America 1990 'AND' 31 December 1996'

Then [UnitPrice] * [Quantity] Else 0 End) as 1997)

FROM Customers INNER JOIN orders

ON CustomerID=Orders.CustomerID

Hide object

All hidden objects are ignored during the database upgrade. The best way to deal with this is to check the object using the GetHiddenAttribute property of the program 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 IsHidden Boolean variable will be True.

Table containing index

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

Operation steps of database upgrade

Once you have everything ready, and make sure that the operation can begin. The Database upgrade Wizard gives you three options:

Export Access tables to SQL Server and link to Access database

Use Pass-Through query to communicate with the tables of the back server of SQL Server

Move the entire Access database to an Access database project (which only contacts SQL Server)

To start the database upgrade wizard, select the database feature from the tools menu bar, and then select the upgrade wizard from the submenu. The first panel of the wizard provides two options: you can create a new SQL Server database to hold Access tables, as shown in figure A, or you can add tables to an off-the-shelf SQL Server database. Selecting an off-the-shelf SQL Server database will require entering a data service name (DSN).

Figure A:

Choose to set up a new database or use a ready-made database

The second panel asks for information about the SQL Server example. In addition, you must authenticate the security mechanism (if it exists) and provide a default name for the new database, as shown in figure B.

Figure B

Name a new database

At this point, you can copy the desired table to SQL Server, or you can indicate that the completed part will become a complete ADP or a linked table, as shown in figure C.

Figure C

Indicates an ADP or a linked form

Avoid trouble

Each developer has different experience with database upgrade operations, so there is no guarantee that the first operation will be successful. However, if you follow the rules provided in this article, you should encounter fewer errors, and even if you do, you can easily correct them and continue.

At this point, the study on "what are the matters needing attention and methods for upgrading Access database to SQL database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.