Access to the SQL Server problem Instance description _access

Today to help students change an old system as a graduation design, this system technology is absolutely backward asp+access. In order to cope with the teacher's acceptance, and then this classmate programming is not very good, and it came to the last moment to start doing bi set, so found me.

I saw him do not know where to get the code, ran under, can run, after two days to accept the procedure, the ASP changed into ASP.net is no time, so I propose to change the database, so decided to turn access into SQL Server. Looks like a company's product, turns up the problem to a lot of. It is summarized as follows,

Let's talk about how to transfer data, open SQL Server's Enterprise Manager, set up a table, then right-click the table, point to import data, and then select the data source (I am here is the MDB file for access), all the way next, basically no problem, the data is so good, but the problem is not so simple, SQL The mechanism of server and access is very different

1, because there is no automatic number in the SQL2000, so your automatic numbering set of fields will become a non-empty field, this must be manually modified by these fields, and his label selection is, the seed is 1, the increment is 1,

2, in addition, when ACCESS2000 is converted to SQL2000, the field with the original attribute of Yes/no will be converted to a non-empty bit, at which point you must modify the property you want.

3, in addition, we should pay attention to the time function of the grasp. Access is a lot different from SQL.

Some experience with access to the MS SQL database

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 both 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.