Access database upsizing SQL Server problem

There are often problems with database upsizing during software development, such as acsess access is slower than SQL Server, Access leaves space after data logging is deleted, files become larger (and can be reduced in a compact fix, but too cumbersome), and access is slower and faster. Even the database is damaged, the lighter damage can be found by Third-party tools to repair, it will cause the database can not be repaired, or after the repair of data records will be damaged. So it's important for us to upsize our existing Access database to SQL Server;

This paper discusses the author's practice and matters needing attention in the promotion

1, For Date field fields

Access is represented as: #1981 -28-12#

SQLSERVER2000 said: ' 1981-02-12 '

2,sql statement Differences, SELECT, update are similar to single table operations, but the difference between update statements in a multiple table operation is compared to the UPDATE statement in SQL Server:

Update statements for multiple tables are updated in SQL Server:

UPDATE TAB1

SET A.name = B.name

From TAB1 a,tab2 b

WHERE a.id = b.id;

SQL statements with the same functionality should be in Access

UPDATE TAB1 a,tab2 b

SET A.name = B.name

WHERE a.id = b.id;

That is, the UPDATE statement in Access does not have a FROM clause, and all referenced tables are listed after the UPDATE keyword.

When you update a single table:

UPDATE table1 Set ab= ' ", cd=444 where ....

3,delete statement

Delete in Access with: Delete * from table1 where a>2 that is, just replace the select in the SELECT statement with the delete.

Sqlserve is: Delete from table1 where a>2 no * number

calculated field differences after 4,as

In Access, you can do this: select A,sum (num) as kc_num,kc_num*num as All_kc_num can take the As field as a database field to participate in the calculation.

SQL Server is: select A,sum (num) as kc_num,sum (num) *num as all_kc_num that you cannot participate in calculations as a field after as a database field.

5,[.] with [!] The difference

When a multiple table union query in Access: Select Tab1!a as Tab1a,tab2!b tab2b from TAB1,TAB2, the middle as can not.

Sqlserve: Select tab1.a as tab1a,tab2.b tab2b from TAB1,TAB2, the middle as can not.