How to convert access to SQL database

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" in the selection bar.
(*. 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, click the following options to find your database location
Address and selection (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 into the database" appear, and all the tables have a green check mark before them, indicates that all data is successfully imported.
If there is a problem in the middle of the table or there is a red cross in front of the table, it indicates that the table has not been imported successfully, then you have to go back and 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.
Date-related fields during conversion, SQL
Server is of the smalldatetime type by default. We 'd better convert it to the datetime type, because the range of the datetime type is larger than that of the smalldatetime type.
Large. 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? Method 1: First, you need to understand what access development is and what a jet application is. If you still cannot understand it, see: Http://access911.net/index.asp? Board = 4 & recordid = 79fab51e12dc 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. Originally developed in accessProgramYou do not need to change the data structure 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.For more information, see: Http://access911.net/index.asp? Board = 4 & recordid = 72fab61e14dc 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. 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. Date-related fields during conversion, SQL Server is of the smalldatetime type by default. We 'd better convert it to the datetime type, because the range of the datetime type is larger than that of the smalldatetime type. Large. In this case, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful. 3. operations on the two databases The SQL statements are not all the same. For example, when you delete a record in an Access database, use: "delete * from user where id = 10 ". The server database is deleted using: "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. For Access database processing, some VB functions can be used in SQL statements, such as CSTR () functions, but not in SQL Server database processing.

An absolutely successful way to convert access to SQL Server2000 Database A few days ago, I encountered many problems in converting the website database from access to SQL. The following method can make the conversion successful! A good way to convert an Internet database access to an SQL Server2000 Database is absolutely successful: The procedure is as follows: 1. You must first install Microsoft Office Access 2003 and SQL Server. 2. Back up the old dynamic network database. After the backup is complete, use Access 2003 to open the old dynamic network database. A warning will appear when it is opened. Ignore it (Security Warning) and press the open key. , And then press the toolbar -- database utility -- convert database -- to convert to 2002-2003 format, and convert the database to 2003 format. 3. After the conversion is complete, open it with Access 2003. Then, press the toolbar -- database utility -- promote wizard -- create database -- enter the sqldatabase login name, password, and To create a new dynamic network database (to be converted to a new dynamic network database), Press next, press "", then press next, select all options, then press next, and select "No application" Make any changes. 4. Open SQL Enterprise Manager-database-select the corresponding database-right-click all tasks-generate SQL scripts-General-show all-write all object scripts -- Confirm (remember the storage location ). 5. Use notepad to open the generated SQL script. In the editing bar -- replace -- Replace "datetime" with "smalldatetime" -- replace all; Edit column -- replace the content with "nvarchar" with "varchar" -- replace all, save and exit. 6. Open the SQL Enterprise Manager-database-click the database to create a new dynamic network database, and then go to the toolbar-SQL query analyzer-file-open-"just now The generated SQL script "-- Query -- execution, and then close the window. (Some friends say that you can use the SQL script that comes with the SQL version. You may try it) 7. Return to SQL Enterprise Manager -- database -- click the database to create a new dynamic network database, and then open the toolbar -- database conversion service -- import data -- next Step -- Data Source "Microsoft Access" file name "old dynamic network database" -- next -- Copy table and view from source data -- next -- select all- -Next -- run now -- next -- complete. 8. Modify the two files conn. asp and INC "Const. asp in the dynamic network folder. Notes for converting access to SQL To add fields automatically, rewrite them. The automatically numbered field that is frequently used in access. After being imported to MSSQL, it is not an auto-incrementing int and needs to be set manually, change the "no" of the Imported Automatic number field to "yes", and the "Seed" and "incremental" are both "1" to be automatically numbered. All default values are lost. Mainly numeric and date types All now (), time (), date () must be changed to getdate () Change all datediff ('D', time1, time2) to datediff (day, time1, time2) Change all datediff ('ww ', time1, time2) to datediff (Week, time1, time2) Change all datediff ('D', time1, time2) to datediff (day, time1, time2) In MSSQL Server, there are many reserved words that are not available in access. When you import data to MSSQL, the problem arises. When MSSQL imports these fields (Including the table name in the database) and "[field name]", you must modify your script and enclose the corresponding field name (or table name) with brackets, or change the field name to be not guaranteed by MSSQL. Reserved Words When using access, you like to use "select * From AAAA while For SQL statements such as time = "& now ()", however, "Now ()" is not used in MSSQL, but "getdate ()" is used. Therefore, all In the SQL statement, "Now ()" must be replaced with "getdate ()". The date functions are different. In the ACCESS database processing, functions such as date () and time () are available, In SQL Server database processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time. Transfer For date-related fields, SQL Server is of the smalldatetime type by default. We 'd better convert it to the datetime type, because the range of the datetime type is larger than that of the smalldatetime type. Large. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful. Change isnull (rowname) to rowname = NULL The value of cursortype must be changed to 1, that is, when the database is opened, the first numeric parameter must be set to 1. Otherwise, the record may Incomplete display The remarks type should be used by cast (column as varchar ). The value of true or false type cannot be used. The value must be set to 1/0. 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 ". In the process of Access database, some VB functions can be used in SQL statements, such as the CSTR () function, but cannot be used in the process of SQL Server database. In access When the time in the SQL statement is queried using variables, you generally use "select * From AAAA while Time = # "& variable name &" # ", which cannot be used in MSSQL. Its syntax is" select * From AAAA while Time = '"& variable name &"'"". (This means that you can use the date and time variables as strings ~~~) The original "delete * from..." In ASP ......" To change to "delete from ......" It is possible that Rs. Update fails and is changed to the update table name set field = 'value'. (in this case, the prompt is: Microsoft ole db provider for SQL Server Error '80040e38' Optimistic concurrency check failed. This row has been modified beyond this cursor. /Admin_classorder.asp, row 164) The Division in access can be "or"/". Only"/"can be used in MSSQL "/" 1. to automatically add fields, rewrite them. The automatically numbered field that is frequently used in access. After being imported to MSSQL, it is not an auto-incrementing int and needs to be set manually, change "no" of the Imported Automatic number field to "yes", and "Seed" and "incremental" to "1" to be automatically numbered. 2. All default values are lost. Mainly numeric and date types 3. Change all now (), time (), date () to getdate () 4. Change all datediff ('D', time1, time2) to datediff (day, time1, time2) 5. Change all datediff ('ww ', time1, time2) to datediff (Week, time1, time2) 6. Change all datediff ('D', time1, time2) to datediff (day, time1, time2) 7, In MSSQL Server, there are many reserved words that are not available in access. When you import data to MSSQL, the problem arises. When MSSQL imports these fields (Including the table name in the database) and "[field name]", you must modify your script and enclose the corresponding field name (or table name) with brackets, or change the field name to be not guaranteed by MSSQL. Reserved Words 8. When using access for time usage, you like to use "select * From AAAA while For SQL statements such as time = "& now ()", however, "Now ()" is not used in MSSQL, but "getdate ()" is used. Therefore, all In the SQL statement, "Now ()" must be replaced with "getdate ()". 9. Date functions are different. functions such as date () and time () can be used in Access database processing, In SQL Server database processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time. 10, Date-related fields during conversion, SQL Server is of the smalldatetime type by default. We 'd better convert it to the datetime type, because the range of the datetime type is larger than that of the smalldatetime type. Large. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful. 11. Change isnull (rowname) to rowname = NULL. 12. Change cursortype to 1, that is, when you open the database, the first numeric parameter must be set to 1. Otherwise, the record may be incomplete. 13. Use cast (column as varchar) as the comment type. 14. The true/false type cannot be used. The value must be set to 1/0. 15, The SQL statements used to perform operations on the two databases are not all the same. For example, when deleting a record on the Access database, use: "delete * from user where Id = 10 ", while the SQL Server database is deleted using:" delete user where id = 10 ". 16. In Access database processing, some VB functions can be used in SQL statements, such as CSTR () functions, but not in SQL Server database processing. 17, When you use a variable to query the time in an access SQL statement, you generally use "select * From AAAA while Time = # "& variable name &" # ", which cannot be used in MSSQL. Its syntax is" select * From AAAA while Time = '"& variable name &"'"". (This means that you can use the date and time variable as a string) 18. "Delete * from…" In ASP ......" To change to "delete from ......" 19. It is possible that Rs. Update fails. modify it to the update table name set field = 'value '. 20. "" or "/" can be used for Division in access. "/" can only be used in MSSQL "/" 21. Create a primary key in sqlserver 22. Use Rs. Open SQL, Conn, 3, 2, or 3, 1 if any problem persists.