Several issues needing attention in transforming access into SQL2000

access| problem many friends want to use SQL2000 database programming method, but also suffer oneself is to learn access, to SQL is just a little understanding, here I give you the following reference---to convert access to SQL2000 methods and considerations
First, I said in the access2000,sql2000 between the conversion, the other I have not tried, I hope we have a lot of experimentation, there must be a way;
Second, the method of conversion
1, open the database source in Administrative tools under Control Panel;
2, press "Add" to add a new data source and select "Driver do Microsoft Access in the selection bar"
(*.mdb), when finished, a box appears,

In "Database Source" Enter the name you want to write, I named "ABC", stating that you do not need to fill out, and then, click the following options to find your database address and check (note, please back up your own Access database), and then determine.
The data source has been built here, and the rest is converted.

3, open SQL2000 Enterprise Manager, enter the database, create a new empty database "ABC";
4, select the newly established database, press the right mouse button, select "All Tasks" under "Import Data", press "next" to continue;
5, in the database source Drop-down but select "Driver do Microsoft Access (*.mdb)", in "User/System DSN", the selection of the "ABC" you just added, press "Next";
6, "purpose" does not need to be modified, select the server (generally for your own native "local", you can also select the server address or LAN address to determine whether your permissions can operate,), "Use Windows Authentication" refers to the operation of their own system administrator, "Using SQL Identity operation authentication "Can be used for Web site operations, recommended by the latter;"
7, select the "Use SQL Identity Operation Verification", fill in your username and password, I chose the system default number "SA", "* * * * * * *, the database select just new" ABC ", press" next ";
8, this step of the two single choice, "Copy tables and views from the data source" and "Specify the data to be transferred with a query instruction", select the former, press "next" to continue;
9, here will appear your own Access database table, press "Select All" after the next step;
The DTS Import/Export Wizard, see "Run Now" is selected to press "Next",
11, press "Finish" to continue;
12, this step you will see your data being imported into the SQL2000 inside, when the words "have successfully imported the XXX table into the database," and all the tables are preceded by a green tick, it means that all the data is successfully imported, if there is a problem or a red fork in front of the table, Indicates that the table did not import successfully, then go back and see if your actions are correct.

Third, data modification
1, because there is no "AutoNumber" inside the SQL2000, so your "AutoNumber" set of fields will become Non-empty fields, which must be manually modified by these fields, and his "mark" select "Yes", the seed is "1", the increment is "1",
2, in addition, ACCESS2000 converted to SQL2000, the original property is "Yes/no" field will be converted to a non-empty "bit", at this time you have to modify the properties you want;
3, in addition, we should pay attention to the time function of the grasp. Access is a lot different from SQL.


Iv. related Field issues
Automatic numbering type in 1.ACCESS database when converting, SQL Server does not set it to AutoNumber, we need to add identity to the SQL creation statement to indicate AutoNumber!
2. When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime, because the range of datetime is larger than the smalldatetime type. I met this situation, with the smalldatetime type, conversion failure, but with the DateTime type, conversion success.
3. The SQL statements that operate on the two databases are not identical, for example, when a record is deleted for an Access database: "Delete * from user where id=10", and the SQL Server database is deleted by: "Delete user Where id=10 ".
4. The date function is different, in the Access database processing, you can use the date (), Time (), and other functions, but in the SQL Server database processing, only datediff,dateadd functions, but not the date (), time () and other functions.
5. In Access database processing, SQL statements can be directly used in some VB functions, such as the CStr () function, but for SQL Server database processing, but not used.

V. Related Statement Issues
Auto-increment fields need to be overridden. AutoNumber fields that are often used in access. After import to MSSQL, he is not a self-added int, need to manually set, the number of the imported AutoNumber field is changed to "yes", "seed" and "increment" are "1" to become automatic numbering


All default values are lost. Mainly numeric types and date types


All now (), time (), date () is changed to GETDATE ()


All DateDiff (' d ', time1, time2) are to be converted to DateDiff (day, time1, time2)


All DateDiff (' ww ', time1, time2) are to be converted to DateDiff (week, time1, time2)


All DateDiff (' d ', time1, time2) are to be converted to DateDiff (day, time1, time2)


In MSSQL server, there are many reserved words that are not available in access, and when you import data into MSSQL, the problem comes out. MSSQL will automatically add "[Field name]" to these fields (including the table names in the database) when importing, so you must modify your script to include the corresponding field name (or table name) in brackets, or change the field name to a reserved word that is not MSSQL


In the use of access for time, people like to use "select * from AAAA while time=" &now () "such as SQL statements, however, in MSSQL does not have the" now "function, but instead of using" getdate () ", Therefore, the "now" in all SQL statements must be replaced with "getdate ()".


Date functions are not the same, and functions such as date (), time () can be used in Access database processing, but
In SQL Server database processing, you can use functions such as Datediff,dateadd instead of date (), Time (), and so on.


When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime, because the datetime type is larger than the smalldatetime type. Sometimes when using a smalldatetime type, the transformation fails, and when the datetime type is used, the conversion succeeds


IsNull (RowName) to be changed to RowName = NULL


CursorType to be changed to 1, that is, to open the database to give the first numeric parameter is 1, otherwise the record may
Show incomplete


The memo type is to be used by the cast (column as varchar)


True/false type cannot be used, to be changed to 1/0


The SQL statements that operate on the two databases are not identical, for example: "Delete * from user where id=10" is used when deleting records for an Access database, and the SQL Server database is deleted by: "Delete user where Id=10 ".


In the processing of Access database, some VB functions can be used directly in SQL statements, such as the CStr () function, but not in SQL Server database processing.


When using variable queries in Access's SQL statements, you generally use the "select * from AAAA while time=#" & Variable Name & "#", which is not possible in MSSQL, and his syntax is "SELECT * FROM AAAA while Time= ' "& Variable Name &" "". (The meaning is to let you use the date time variable as a string, hehe ~ ~ ~)


The original ASP "Delete * from ..." To change to "delete from ..."


It is possible to rs.update failure, modified to Update table name set field = ' value ' passed through (encountered, prompted by:
Microsoft OLE DB Provider for SQL Server error ' 80040e38 '

Optimistic concurrency check failed. The row has been modified outside this cursor.

/admin_classorder.asp, line 164)


In Access division can use "\" or "/", MSSQL can only use the "/"