Upgrade an existing Access database to a SQL database

Many Access and SQLServer developers often face the problem of upgrading an Access database to an SQLServer database. Because of the existing Access Upgrade Wizard, this transformation process becomes quite simple, especially when you establish an ADP associated with SQLServer data. However, the Wizard is not perfect,

Many Access and SQLServer developers often face the problem of upgrading an Access database to an SQLServer database. Because of the existing Access Upgrade Wizard, this transformation process becomes quite simple, especially when you establish an ADP associated with SQLServer data. However, the Wizard is not perfect,

Many Access and SQLServer developers often faceDatabaseUpgradeTo SQLServerDatabase. BecauseExistingAccessUpgradeWizard, this transformation process will become quite simple, especially when you establish an ADP associated with SQLServer data. However, the Wizard is not perfect, and the problems to be solved still exist.

First, some objects are not simpleUpgradeSo you have to deal with it manually. Second, there are many Access features-for example, some query types, objects, and specific data types are not ready for you.UpgradeThe previous preparations may cause errors. Now, let's discussDatabaseUpgradeI will provide some general guidance methods to solve problems that may be encountered during the process. Finally, you must spend some time and energy applying this knowledge to development.

Which cannotUpgrade?
Before dealing with actual problems, let's see if we can't feel freeUpgradeObjects, including the following:

Cross tabulation Query
Any query that contains the SQLDISTINCTROW keyword
All hidden objects
Query table data as parameters (these tables canUpgradeBut they cannot run correctly)
Pass-Through query
SQL data definition language query (such as CREATETABLE, ALTERTABLE, and DROP statements)
These Access objects require specific processing. Specifically, you will create a comparable SQLServer object. In addition, SQLServer does not support Jet security features, so you must use Windows Authentication and/or SQLServer security mechanisms.

Issues included
InDatabaseOfUpgradePreviously, if you already know where it will lead to errors and know how to handle them,DatabaseUpgradeErrors may be greatly reduced. What I can provideDatabaseUpgradeThe best advice is to make the most complete plan before development. Now, I will listDatabaseUpgradeProblems that may occur during the process-if you are not prepared for the plan.

Unsupported date

There is a big difference between Access and SQLServer 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, SQLServer supports dates from January 1, January 1-9, 1753 to January 1, December 31, 999.DatabaseOfUpgradeWizard failedUpgradeA table that contains dates not supported by SQL Server. This means thatUpgradeYou must manually process these dates before. Fortunately, this problem only affects a fewDatabase.

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 SQLSELECT statement contains user input:

SELECTOrders. RequiredDate, Orders. ShippedDate, Orders. Freight,
Orders. ShipName, Orders. ShipAddress, Orders. OrderDate
FROMOrders
WHERE
Orders. OrderDateBetween [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, whenDatabaseUpgradeSQL Server does not involve table control, and the result is usually 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
SQLServer does not support the JetTRANSFORM statement-this statement can make a cross-Table query possible. For example,DatabaseUpgradeThe wizard supports the following query methods:

TRANSFORMSum (CCur ([OrderDetails]. UnitPrice * [Quantity] * (1-[Discount])/100) * 100)
ASProductAmount
SELECTProducts. ProductName, Orders. mermerid, Year ([OrderDate]) ASOrderYear
FROMProductsINNERJOIN (OrdersINNERJOIN [OrderDetails]
ONOrders. OrderID = [OrderDetails]. OrderID) ONProducts. ProductID =
[OrderDetails]. ProductID
WHEREOrders. OrderDateBetween #1/1/1997 # And #12/31/1997 #