How to convert ACCESS to SQL database-access to SQL software Brief Introduction: CooSel2.0CreateSQLV1.05 files containing ASP and Exe versions Asp versions do not support direct import to SQL Server, only the Exe version of the script can be directly imported to SQLserver function: generate the SQL upgrade script and save it *.
How to convert ACCESS to SQL database-access to SQL software Brief Introduction: CooSel2.0 CreateSQL V1.05 files containing ASP and Exe versions Asp versions do not support direct import to SQL Server, only the Exe version of the script can be directly imported to SQLserver function: generate the SQL upgrade script and save it *.
How to convert ACCESS to SQL database-access to SQL Software
Access-to-SQL Software
Software introduction:
CooSel2.0 CreateSQL V1.05
The file contains two versions: ASP and Exe.
The Asp version does not support direct import to SQLserver, but only supports script writing.
The Exe version can be imported directly to SQLserver.
Function:
Generate the SQL promotion script and save it as the *. SQL file. Then, import it to the SQL2000 query analyzer for execution.
Generate a promotion ASP script, so that you can use this script and the corresponding MDB database to upload to the server for online promotion
Directly import Access data to the SQL2000 server (table structure and data)
V1.03
The version fixes the problem that the bit type cannot be converted normally in the default value.
The default value of time is increased to convert time () date () to getdate ()
The Vba function of the view is not processed, and later versions will support T-SQL conversion corresponding to SQLserver
V1.04
Allows you to enter databases and log on to create databases.
Supports direct SQL import using Access
V1.05
Version added the function of directly importing SQL Server
Add import option all table indexes are imported in Unicode encoding, and whether to generate imported data.
Fixed the problem that the default value is used to process the peripheral single double quotation marks. The default value of Access is not enclosed by single quotation marks, and some errors indicate that if the default value is set''
Access is incorrect. It is not the same as ('') in SQLserver. The import program automatically fixes this problem.
V1.05 has found many forums and whole-site databases for testing. All of them have been imported successfully.
The promotion script (table structure and data) generated by the program from the Access2000 MDB database to the SQL server)
Including the default, index the foreign key view of the primary key and the table in the entire MDB Library
----------------
Source code compilation: MiscroSoft Visual Basic 6.0
Compiling environment: windows2000 server 2.00.2195 SP4
If the message "COMDLG32.OCX expired" is displayed
Use the COMDLG32.OCX provided with the program to replace
Run regocx.exe to re-register the COMDLG32.OCX control.
Note the backup source file name. The Microsoft file selection dialog box Control
Many of my friends want to use the SQL2000 database programming method, but they suffer from learning ACCESS, just a little understanding of SQL, here I will provide you with the following reference-methods and precautions for converting ACCESS into SQL2000
I. First of all, I am talking about the conversion between ACCESS2000 and SQL2000. I have not tried any other products yet. I hope you can experiment more and find a solution;
Ii. Conversion Method
1. Open "database source" in "Management Tools" under "Control Panel";
2. Press "add" to add a new data source, and select "Driver do microsoft Access (*. mdb) ", a box will appear, enter the name you want to write in" database source ", I name it" ABC ", it means no need to fill in, then, find your database address and select the address (Note: Back up your own ACCESS database first), and then confirm. The data source is created here, and the conversion is left.
3. Open SQL2000 Enterprise Manager, enter the database, and create an empty Database "ABC ";
4. Select the new database, right-click the database, select "import data" under "all tasks", and click "Next;
5. Select "Driver do microsoft Access (*. mdb) ", in" User/system DSN ", select the" ABC "you just added and press" Next ";
6. You do not need to modify the "purpose". Select a server (generally your local machine, or the server address or local area network address) to check whether your permissions can be operated ,), using WINDOWS identity authentication means using your system administrator identity. Using SQL identity authentication can be used for website operations. The latter is recommended;
7. After SQL authentication is selected, enter your username and password. I chose the default system number sa, *** and select the newly created ABC for the database, follow the next step;
8. Select two items in this step. Copy the table and view from the data source and use a query command to specify the data to be transmitted. Select the former and continue with the next step;
9. The tables in your own ACCESS database will appear here. After selecting all the tables, next step;
10. In the DTS import/export wizard, check that the running is selected and press next,
11. continue after completion;
12. In this step, you will see that your data has been imported into SQL2000. When the words "XXX tables have been successfully imported to the Database" appear, and there is a green check in front of all the tables, it indicates that all data is successfully imported. If there is a problem in the middle or there is a red * in front of the table, it indicates that the table has not been imported successfully. In this case, check whether your operations are correct.
3. data modification
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.
What should I pay attention to when converting Access to SQL Server?
Problem:
For beginners, what should I pay attention to when converting Access to SQL Server?
Method 1:
Here, we are talking about how to upgrade Access development to SQL Server 2000. You can select several upgrade methods.
1. You can use a chain table to link to a table in SQL SERVER 2000 through a chain table. The program originally developed in ACCESS does not need to be changed unless you want to change the data structure.
2. You can upgrade the database using ADP. The ADP mode basically discards most of the original MDB mode. You must adapt to the new data structure and accept the results of a query written by jet SQL into a stored procedure, view, and other objects written by the T-SQL. That is to say you have to re-learn T-SQL.
Method 2:
Let me give you some experience. Anyway, it is very painful to port access to sqlserver. I have done it several times.
I hope you can add more information.
1. After the database is imported, it is recommended that fields be automatically added and the length of all numeric types be increased.
Decimal.
2. All default values are lost. It mainly belongs to the numeric type and date type.
3. Change all now (), time (), date () to getdate ().
4. Change all datediff ('D', time1, time2) to datediff (day, time1, time2)
5. Some values of the true/false type may not be used. The value must be 1/0.
6. cast (column as varchar) is used for the remarks type. UploadFiles/2005-12/1222208225. rar
7. Change CursorType to 1, that is, when the database is opened, the first numeric parameter must be set to 1. Otherwise, the record may
Incomplete display.
8. Change isnull (rowname) to rowname = null
Method 3:
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 you delete 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. In the ACCESS database processing, functions such as date () and time () can be used,
In SQL SERVER database processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time.
5. In ACCESS database processing, some VB functions can be used in SQL statements, such as cstr () functions, but not in SQL SERVER database processing.
Http://www.bztv.net/blog/read.php/84.htm
**************************************** ********************
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.
3. ACCESS: delete * from user where id = 10
To delete an SQL SERVER instance, use: delete user where id = 10.
4. functions such as date () and time () can be used in ACCESS processing,
In SQL SERVER processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time.
5. In ACCESS database processing, some VB functions can be used in SQL statements, such as the cstr () function,
SQL SERVER databases are being processed, but cannot be used.
1. For the Date Field
Access: #1981-28-12 #
SQLSERVER: ''1981-02-12''
2. Differences between the update statement during multi-Table operations ACCESS and the UPDATE statement in SQLSERVER:
UPDATE statements for updating multiple tables in SQLSERVER:
UPDATE Tab1
SET a. Name = B. Name
FROM Tab1 a, Tab2 B
WHERE a. ID = B. ID;
ACCESS should be
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. All referenced tables are listed after the UPDATE keyword.
3. delete statement
Delete * from table1 where a> 2 is used when access is deleted. That is, you only need to replace select in the select statement with delete.
In SQL server, "delete from table1 where a> 2" means no "*".
4. Differences between calculated fields after
In access, select a, sum (num) as kc_num, and kc_num * num as all_kc_num can be used AS a database field for calculation.
In SQL Server, select a, sum (num) as kc_num, sum (num) * num as all_kc_num cannot be used AS a database field for calculation.
5, [.] and [!] Difference
Select tab1! A as tab1a, tab2! B tab2b from tab1, tab2, the intermediate AS can be left blank.
In sqlserver: select tab1.a as tab1a, tab2. B tab2b from tab1, tab2, the intermediate AS can not.
6. During joint query,
Multi-table joint query in access: 'select a, B from (
Select a, B from tab1 where a> 3 union select c, d from tab2) group by a, B
In sqlserve, 'select a, B from (
Select a, B from tab1 where a> 3 union select c, d from tab2) tmptable group by a, B is to add a virtual table tmptable, the table name is arbitrary.
7. When access is upgraded to sqlserver,
You can use SQL Server's data import tool to import data, but necessary processing is required.
The automatic number in access does not automatically convert the automatic number in SQL. It can only be converted to the int type. You need to manually change it to the ID field, and the seed is 1,
Remove all the n types of fields whose imports have been converted to "n" by sqlserver, such as nvarchar-> varchar. change the Date Field of the second type to the datatime type (SQL converts all the dates to the smalldatetime type)
8, true and 1 = 1
Access uses where true to indicate that the condition is true,
Sqlserver uses where 1 = 1 to indicate that the condition is true.
9. determine the difference between Null Field Values
Normal blank:
Access is the same as SQL server. where code is null or where code is nol null
Condition NULL:
Access: iif ([num] is null, 0, [num]) or iif ([num] is null, [num1], [num])
SQLServer: isnull ([num], 0) or isnull ([num], [num1])
10. Differences between SQL statements and substrings
Access: MID (field, n1, [n2]), LEFT (field, n), RIGHT (field, n)
For example, select left (cs1, 4) + '-' + cs2 as cs3
SQLServer: SUBSTRING (expression, start, length)
For example, select substring (cs1, 1, 2) + substring (cs1, 4, 2) + '-' + cs2 as cs3
Supplement:
ACCESS differs from SQL2000 SQL statements.
For example, now () must be changed to getdate () in SQL2000 ()
Also, the keyword must be added with []. For example, the field name in ACCESS must be added with name SQL20000. Otherwise, an error occurs.
Database Connection word Reconfiguration
1. After access is converted to an SQL database, you need to create keywords for each table and set the Incremental Quantity. Some data types need to be redefined.
2. The now () function is acceptable, but getdate () must be used in the date comparison process ()
3. Add [] for reserved words
4. Single double quotes need to be changed
5. Follow the standard SQL definition (the most critical one)
Http://louiswun.itpub.net/post/10311/189977
**************************************** *************
