Precautions for converting access to SQL Server

I. First of all, I am talking about the conversion between Access2000 and SQL2000. I have not tried any other products yet. I hope you can experiment more and find a solution;
Ii. Conversion Method
1. Open "database source" in "Management Tools" under "Control Panel";
2. Press "add" to add a new data source, and select "Driver do Microsoft Access" in the selection bar.
(*. Mdb) ", a box will appear after completion,

Enter the name you want to write in "database source". My name is "ABC", which means you do not need to enter it. Next, select the name as follows, find your database address and select it (Note: Back up your own access database first), and then confirm.
The data source is created here, and the conversion is left.

3. Open SQL2000 Enterprise Manager, enter the database, and create an empty Database "ABC ";
4. Select the new database, right-click the database, select "import data" under "all tasks", and click "Next;
5. Select "Driver do Microsoft Access (*. MDB) ", in" User/system DSN ", select the" ABC "you just added and press" Next ";
6. The "destination" does not need to be modified. You can select a server (generally "local" for your local machine) or a server address or LAN address, determine whether your permissions can be operated.) "using Windows Identity Authentication" means using your system administrator identity. "using SQL Identity Authentication" can be used for website operations, the latter is recommended;
7. After selecting "use SQL authentication", enter your username and password. I chose the default system number "sa", "***", select the newly created "ABC" for the database and press "Next".
8. Select "Copy table and view from data source" and "use a query command to specify the data to be transmitted", select the former, and press "Next" to continue;
9. here you will see your own access database table. After you press "select all", next step;
10. In the "DTS import/export wizard", see "Run now" and press "Next",
11. Press "finish" to continue;
12. In this step, you will see that your data has been imported into SQL2000. When "XXX tables have been successfully imported into the database" appears, in addition, if there is a green check in front of all tables, it means that all data is successfully imported. If there is a problem in the middle or there is a red cross in front of the table, it means that the table has not been imported successfully, in this case, you have to go back and check whether your operations are correct.

3. data modification
1. Because SQL2000 does not contain "automatic number", all fields you set with "automatic number" will become non-empty fields, which must be manually modified, and select "yes" for his "mark", the seed is "1", and the increment is "1",
2. In addition, after Access2000 is converted to SQL2000, the field with the original attribute "yes/no" will be converted to a non-empty "bit", at this time, you must modify it to the desired attribute;
3. In addition, you should be aware of the time functions. Access and SQL are different.

what should I pay attention to when I change access to SQL?
after the database is imported, fields need to be overwritten automatically, and the length of all numeric types needs to be increased. It is best to use decimal.
all default values are lost. It mainly belongs to the numeric type and date type.
change all now (), time (), and date () to getdate ().
All datediff ('D', time1, time2) must be changed to datediff (day, time1, time2).
some true/false types may not be used, to change to 1/0.
the remarks type must be cast (column as varchar.
change the value of cursortype to 1, that is, when you open the database, set the first numeric parameter to 1. Otherwise, the record may be incomplete.
change isnull (rowname) to rowname = NULL
when the automatic numbering type in the ACCESS database is converted, SQL server does not set it to automatic numbering, we need to add the identity in the SQL creation statement to indicate the automatic number!
for date-related fields during conversion, the default value of SQL Server is "smalldatetime". We recommend that you change it to "datetime" because the range of "datetime" is larger than that of "smalldatetime. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.
the SQL statements used to operate the two databases are not all the same. For example, when you delete a record of an Access database, use "delete * from user where id = 10". to delete an SQL Server database, run the following command: "delete user where id = 10".
date functions are different. functions such as date () and time () can be used in Access database processing. However, only datediff can be used in SQL Server database processing, dateadd and other functions, instead of date (), time () and other functions.
in Access database processing, some VB functions can be used in SQL statements, such as CSTR () functions, but not in SQL Server database processing.

experience in converting access to SQL Server databases
1. when the automatic numbering type in the ACCESS database is converted, SQL server does not set it to the automatic numbering type. We need to add the identity in the SQL creation statement to indicate the automatic numbering!
2. For date-related fields during conversion, SQL server uses the smalldatetime type by default. We recommend that you change it to the datetime type because the datetime type has a larger range than the smalldatetime type. In this case, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.
3. the SQL statements used to operate these two databases are not all the same. For example, when you delete a record in an Access database, use the following statement: "delete * from user where id = 10". to delete an SQL Server database, run the following command: "delete user where id = 10".
4. date functions are different. functions such as date () and time () can be used in Access database processing. However, functions such as datediff and dateadd can only be used in SQL Server database processing, instead, functions such as date () and time () cannot be used.
5. in the process of Access database, some VB functions can be used in SQL statements, such as the CSTR () function, but cannot be used in the process of SQL Server database.