Convert Access database to sqlserver Database

-- Convert access to SQL (theoretically feasible, but only a few successful programs can be modified by the author)
: Most free ASP programs on the Internet use access databases. However, as a small and medium-sized standalone database system, the ACCESS database is often overwhelmed when it handles website applications with a large volume of traffic and data. It is generally believed that the performance of access databases exceeding 50 m will start to drop significantly. After exceeding M, errors and slow running will become more prominent. Although it can be optimized as much as possible from the perspective of the program to improve performance, it cannot fundamentally solve the problem.
: At this time, it may be the most possible way to use Microsoft's SQL Server database. Of course, you can also use other SQL Server databases, such as Oracle and MySQL. However, for rewriting, since they are both Microsoft products, rewriting SQL server should be the most effort-saving method.
:
: 1. Prerequisites for rewriting:
:
: The system has installed SQL Server and installed the SP3 patch. install access in the office suite. Use an editor that supports plain text editing and displays row numbers. Ultra edit is recommended, you can also use frontpage2003, but the line numbers in earlier versions are not very useful.
:
: Personal capability requirements: Basic ASP syntax, access database operations, and basic operations of sqlserver Enterprise Manager.
:
: 2. Database preparation
:
: Generally, there are two situations:
1. The program provides the SQL database format: an MDF file or an SQL script file (suffixed with. SQL) for creating the SQL database ).
: If the MDF file exists, you can use the Enterprise Manager to directly attach it. If the SQL script file is provided, create a SQL database by yourself using the Enterprise Manager, then the database uses the query analyzer in the Enterprise Manager to run this script to create a database table.
: In this way, you do not need to rewrite the database.
: 2. The SQL database or script file is not provided. In this case, you have to do this on your own. This is also the main solution of this post. Generally, this program provides an Access database, so that you can use the Enterprise Manager to import the ACCESS database. After the import, You need to rewrite the following:
: Compare with the original access, rewrite the following part:
(1) There is no automatic field in the SQL database table. Therefore, the original Automatic field in access is converted to a common field. You need to manually change it to the ID type and the increment is 1.
(2) If the default value is defined for all time fields, it must be now () and must be changed to getdate ()
(3) The default values of the original fields are generally not automatically introduced. You must manually add them according to the fields in the original table.
(4) due to different databases, many types of access and SQL fields change after conversion. For example, the original "whether" field will be converted to bit or Int, the remarks field is converted to longtext, text field to varchar, and so on. In general, the program will not be affected. If there is a problem, we will rewrite it in the following program section.
(5) If you want to use a program for SQL and the stored procedure is used in it, you should have the method for establishing the SQL database of the program itself: there is its own SQL database file, or an SQL script. If no, you cannot create a stored procedure by importing an Access database. In this case, you 'd better discard the program version of this for SQL statement, use the for Access Program of the same version to import the ACCESS database, and then use the following rewrite method to change the program to the SQL version.
:
: 3. Rewrite the connection string
:
: Refer to the section on the Internet, which is for access and SQL respectively.
: Dim connstr
: If issqldatabase = 1 then
:/'SQL database connection parameters: Database Name, user password, user name, connection name (local for local use, IP for foreign use)
: Dim sqldatabasename, sqlpassword, sqlusername, sqllocalname
: Sqldatabasename = "dvbbs7"
: Sqlpassword = ""
: Sqlusername = "dvbbs"
: Sqllocalname = "(local )"
: Connstr = "provider = sqloledb; user id =" & sqlusername & "; Password =" & sqlpassword & "; initial catalog =" & sqldatabasename &"; data Source = "& sqllocalname &";"
: Else
:/'For free users, modify the address of the local database and change the name of the database in the data directory for the first time. For example, change dvbbs6.mdb to dvbbs6.asp.
: DB = "Data/fengerqingqing. mdb"
: Connstr = "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath (db)
: End if
: On Error resume next
: Set conn = server. Createobject ("ADODB. Connection ")
: Conn. Open connstr
:
: Of course, if you use SQL statements, the access statement can be deleted, that is, else is followed by on error resume next, which becomes like this:
:
: Dim connstr
:/'SQL database connection parameters: Database Name, user password, user name, connection name (local for local use, IP for foreign use)
: Dim sqldatabasename, sqlpassword, sqlusername, sqllocalname
: Sqldatabasename = "databasename"
: Sqlpassword = ""
: Sqlusername = "sa"
: Sqllocalname = "(local )"
: Connstr = "provider = sqloledb; user id =" & sqlusername & "; Password =" & sqlpassword & "; initial catalog =" & sqldatabasename &"; data Source = "& sqllocalname &";"
: On Error resume next
: Set conn = server. Createobject ("ADODB. Connection ")
: Conn. Open connstr
:
: It can also be concise and written as follows:
: Set conn = server. Createobject ("ADODB. Connection ")
: Conn. Open "provider = sqloledb; user id = sa; Password = 1234567; initial catalog = databasename; Data Source = (local );"
: Change the database name, data source, user, and password based on your actual situation.
:
: 4. Program Rewriting
:
: There are two cases.
1. If you are lucky enough to get the for SQL program, if the above database creation process is not in trouble, the program can basically run, if the error occurs, it's just a bug in the program itself. I will not go into details about how to modify it if it is not discussed in this post.
2. In most cases, the program itself is for access. The difference from the for SQL program is mainly the SQL query statement used in the program. Note: SQL query statements are indispensable for database applications. The syntax used by both for SQL and for aceess programs is similar, but there are some subtle differences, the program is not universal, and it is also the main content we need to modify. In this case, the following sections are generally to be modified:
(1) question about time functions: the time functions of the SQL database are different from those of access. The most common is the current time function. Access is now (), and SQL is getdate (). Therefore, if you use now () in the WHERE clause, you must change it to getdate (). Note that the now () function must also be used in the ASP program, do not change the now () function that is not used in database queries or execution statements.
(2) time comparison function: datediff (/'d/',/'time 1/', 'Time 2'). This is the format used for access query, in SQL, the quotation marks must be removed, and the time format may be prefixed with #, which must also be removed. This also means that the SQL statement must be kept as is in the ASP statement.
(3) expression of null values: in access, null values are usually expressed by "=" ", but errors often occur in SQL. If an error occurs or the program runs abnormally, you can change it to the following judgment: Where (name is null)
(4) True/false value judgment: You can use = true or = false in access, but an error occurs in SQL, therefore, in SQL queries or execution statements, such judgments should be changed to = 1 and = 0 respectively. Note: although some programs are written as = "true", this field is of the character type due to quotation marks. You cannot change it to = 1 to be the same.
:
: The above are some of the most common rewrites and are not very common. You can reply here to discuss them.
:
5. program debugging
:
: We recommend that you use an editor with a line number because the above rewrite is unlikely to be done by directly searching the program source code, which is hard to find.
: The method I used is generally as follows: after the database is rewritten, the program is directly debugged. After an error occurs, check the error prompt and find the code line of the corresponding file, but the root cause is often not that line, for example, the wrong statement is conn.exe cute (SQL), but this statement is correct. The error is caused by the SQL string in it, then let's look up how the SQL string is generated and modify it according to the above mentioned program modification method.
:
:
: The above are some superficial insights and many omissions. You can practice them.
: You can use the oblog blog program to rewrite the program. The changes in the program are not large, so you can train your trainer.
:
:
: An absolutely successful way to convert the Internet database access to the SQL Server2000 Database:
:
: 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, so ignore it (security warning ), press the open key and press the toolbar> database utility> convert database to 2002-2003 format to convert the database to 2003 format.
:
: 2. Enable access 2003 after the conversion is complete, go to the toolbar, choose database utility> --> sqlsql--> enter the sqldatabase logon name, password, and the database to be created (to be converted to a new database), and click Next, press the "" key, then press next, select all options, then press next, select "do not make any changes to the application", and then press finish.
:
: 3. Open SQL Enterprise Manager-database> and you will see the newly created dynamic network database? Click the database, and then in the tool SQL script-General-show all-write all object scripts-OK (remember the storage location ).
:
4. Use notepad to open the generated SQL script. In the editing bar -- replace -- Replace "smalldatetime" with "datetime" -- replace all; then, in the edit column -- replace the content with "nvarchar" and "varcha" -- replace all, save and exit.
:
: 5. Open SQL Enterprise Manager-database-click the database to create a new dynamic network database, then, in the toolbar -- SQL query analyzer -- file -- open -- "generated SQL script" -- Query -- run, and close the window.
:
6. Return to SQL Enterprise Manager> database> click the database to create a new dynamic network database, then open the toolbar -- database conversion service -- import data -- next -- 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.
:
: 7. Modify the two files conn. asp and INC/Const. asp in the dynamic network folder.