SQL Server migration from ASP Website System to acess

First, data conversion is the most important and noteworthy part. Because data transmission (DTS) of SQL Server 2000 cannot completely convert the access data structure, such as "automatic number", "Default Value", and "= now, if the data structure is not complex, you can manually design the table after DTS. However, if there are n databases, N tables, and N Fields, I am afraid you will be exhausted, third-party conversion software is required. The database conversion software at home and abroad has basically tried, and finally found that only "Full convert enterprise 4.0" can fully convert the data and data structure, which is strongly recommended, it supports conversion between almost all popular databases and Unicode. Unfortunately, there is no cracked version, and few users are using it in China, you don't need to worry about a series of problems such as "automatic number", "Default Value", "logical type (BIT)", and "= now! Address: http://www.spectralcore.com/
In the trial version, although the data structure can be converted, the data is changed to "*** trial version ***" every few lines. But it doesn't matter, as long as the data structure is transferred, roar. Then, we use SQL Server 200 to automatically generate the SQL script for the database, delete the database we just converted, reuse the generated SQL to create a new database, and then use DTS to import access data.
The data is converted to this, and the next step is to change the code, crazy. This time I have modified thousands of codes. I have to vomit.

Second, the database connection code needs to be changed. If you connect using a data source, you do not need to change the code. You can simply add the data source. Otherwise, use the following method:
Dim sqlusername, sqlpassword, sqldatabasename, sqllocalname, connstr
Sqlusername = "" 'User Name (sqlusername)
Sqlpassword = "" 'User Password (sqlpassword)
Sqldatabasename = "" 'Database Name (sqldatabasename)
Sqllocalname = "(local)" '(local, ip)
Connstr = "provider = sqloledb; user id =" & sqlusername & "; Password =" & sqlpassword & "; initial catalog =" & sqldatabasename &"; data Source = "& sqllocalname &";"
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open connstr

Third, the SQL statement part, the access of jet-SQL and SQL Server T-SQL some places are different, here is a more common situation.
A: Now () and getdate (). If the code is in ASP, no changes are required. If the code is in the SQL statement string, change all now () to getdate ().
B: keywords, some fields in the table may be keywords OF THE T-SQL, such as "Update", "time", etc., in the data retrieval needs to add "[]", otherwise, errors may occur.
For example, when you retrieve access, you need to change "select * from table where update <getdate () order by time DESC" to "select * from table where [update] <getdate () order by [time] DESC"
C: For "remark type", all the original "remark type" of access is "ntext" or "text" in SQL server "; when comparing the "ntext" type, you cannot use "=" "<>". You need to change it to "like ".
For example, the original access has a "Remarks" field: "content ",
Search by "content:
"Select * from table where content = 'abc'" should be changed to "select * from table where content like 'abc '";
Determine whether "content" is empty:
It turns out that "select * from table where content <>'' "should be changed to" select * from table where content is not null"
D: The value cannot be read. After adding a row of data, the system reads a value with a "Default Value" or "automatic number" in the row. For example, if "ID" is an automatic number in a table, "Y" has default values
Rs. addnew
RS ("X1") = x1
RS ("X2") = x2
...
RS ("x9") = x9
'Added
'Then read the "ID" value of the automatic number and the "y" with the default value"
Y = RS ("Y ")
Id = RS ("ID ")
It turns out that "ID" and "Y" can be fully read in access, but not in SQL. You need to calibrate the game to the row you just added, therefore, you must add "Rs" after "RS (" x9 ") = x9. movelast"
E: Rs. recordcount returns "-1". The solution is to add "Rs" After defining "Rs. cursorlocation = 3 "or add" Conn. cursorlocation = 3 ".

The above is a summary of online cool people, and is reproduced here!