Access to SQL Server Considerations _ database Other

1, automatically add fields need to be rewritten. 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.
2, all default values are lost. Mainly numeric types and date types
3, all now (), time (), date () to be changed to GETDATE ()
4, all DateDiff (' d ', time1, time2) to be converted to DateDiff (day, time1, time2)
5, all DateDiff (' ww ', time1, time2) to be converted to DateDiff (week, time1, time2)
6, all DateDiff (' d ', time1, time2) to be converted to DateDiff (day, time1, time2)
7, in the MSSQL server, there are many reserved words, in Access is not, 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
8, in Access about the use of time, we like to use the "select * from the AAAA while Time=" &now (), such as the SQL statement, however, in MSSQL does not have the "now" function, but the use of "getdate ()" , so that "now" in all SQL statements must be replaced by "getdate ()".
9, the DATE function is not the same, in the Access database processing, you can use the date (), time () and other functions, but the
In SQL Server database processing, you can use functions such as Datediff,dateadd instead of date (), Time (), and so on.
10. When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime type, because the range of datetime 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
11, IsNull (RowName) to be changed to RowName = NULL
12, 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
13. Memo type to be used by cast (column as varchar)
14, True/false type can not be used, to become 1/0
15. 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 ".
16, in the Access database processing, SQL statements can be directly used in some VB functions, such as the CStr () function, and SQL Server database processing, but can not use
17, in Access to the SQL statements time using variable query, we generally use "select * from AAAA while time=#" & Variable Name & "#", in MSSQL is not, his syntax is "SELECT * FROM AAAA while Time= ' "& Variable Name &" "". (it means that you use the date-time variable as a string)
18, the original ASP in the "Delete * from ..." To change to "delete from ..."
19, it is possible to rs.update failure, modified to Update table name set field = ' value ' so passed
20, access inside the division can use "\" or "/", MSSQL inside only use "/"
21. Establish primary key in SQL Server
22, if there are problems with: Rs.Open sql,conn,3,2 try