Instructions on conversion from access to SQL Server

Today, I helped my students change an old system as a graduation design. This system technology is definitely lagging behind ASP + access. In order to cope with the teacher's acceptance, the programming of this student was not very good, and it was the last time that he thought of starting to complete the design, so he found me.

I saw where he got it.Code, Run, OK, can run, will be accepted in two daysProgramNow, it is no time to change ASP to Asp.net, So I proposed to change the database, so I decided to convert access to SQL Server. It seems like a company's products are turning into a lot of problems. The summary is as follows,

First, let's talk about how to transfer data, open the Enterprise Manager of sqlserver, create a table, right-click the table, click import data, and select the data source (here is the access MDB file ), next, there is basically no problem. The data has been exported in this way, but the problem is not that simple. The mechanisms of SQL Server and access are quite different.

1. Because SQL2000 does not have an automatic number, all the fields you set with the automatic number will become non-empty fields, which must be manually modified, and select his ID as, seed as 1, increment as 1,

2. In addition, after Access2000 is converted to SQL2000, the fields whose original attribute is "yes" or "no" will be converted to non-empty bits. At this time, you must change the value to the desired attribute;

3. In addition, you should be aware of the time functions. Access and SQL are different.

Experience in converting access to ms SQL database

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. During conversion, SQL Server defaults to the smalldatetime type for date-related fields. 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 deleting a record in an Access database, use: delete * from user where id = 10, to delete an SQL Server database, use: 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. For Access database processing, some VB functions can be used in SQL statements, such as CSTR () functions, but not in SQL Server database processing.