Summary of differences between Access and SQL Server databases in asp

[1] connection problems (example)

[Microsoft Access]

Constr = "DBQ = c: \ daTa \ clwz. mdb; DRIVER = {Microsoft Access Driver (*. mdb )}"

[Microsoft SQL Server]

Constr = "DRIVER = {SQL Server}; SERVER = host; DATABASE = mydata; uid = sa; pwd ="

[2] similar functions(Example)

[1] DATEDIFF (datepart, startdate, enddate)
The "datepart" parameter has the following options:
Set description
------------
[Microsoft Access]
Yyyy
Quarterly q
Month m
Day of the year y
D
The number of days per week is w
Zhou ww
Hour h
Minute n
S

[Microsoft SQL Server]
Year yy, yyyy
Quarter qq, q
Month mm, m
Dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
Minute mi, n
Second ss, s
Millisecond MS

Basically, but note that when writing

[Microsoft Access] is enclosed in quotation marks, for example, datediff ('D', enddate, '2014/1/01') [Microsoft SQL Server]. This parameter is not required, for example, datediff (d, enddate, '2014/1/01 ')

[2] [Microsoft Access] can be used to convert data type functions such as cstr, while [Microsoft SQL Server] uses convert or cast functions such as convert (varchar, [amount])..

[3] [Microsoft SQL Server] getdate for getting the current time...

[3] statement [Microsoft SQL Server] available

CASE
WHEN THEN
WHEN THEN
...
ELSE
END

Statement, which is not supported by [Microsoft Access.

[Microsoft Access] does not support between statements.

[Microsoft SQL Server] can be written as follows:

[Date] between @ date1 and @ date2

[4] querying a table

[Microsoft SQL Server] supports join queries for three or more tables, while [Microsoft Access] seems to have only two tables for join queries (to be confirmed by the Authority ), in addition, [Microsoft SQL Server] can use "* =" and "= *" connectors.

[5] division by zero

[Microsoft Access] When the divisor is zero, related records are automatically lost. [Microsoft SQL Server]
An error is reported and the query is aborted. Delete Code: [Microsoft Access] can be written as follows:

Delete * from [table]

[Microsoft SQL Server]

Write as follows:

Delete from [table]

More than * will report an error

Current date: [Microsoft Access] use date () [Microsoft SQL Server] Use getdate (). If the database type may change, add the following in ASP code:

If inStr (constr, "Microsoft Access")> 0 then
Sqlstr = [Microsoft Access] [SQL code]
Else
Sqlstr = [Microsoft SQL Server] [SQL code]
End if

(Constr -- connection string)

In this way, even if you change the database, you do not need to modify the database query and update code. In addition, access records contain "true" and "false" fields, while SQL records only contain "smallint". If "field name = true" exists in access, in SQL, you need to change it to "field name = 1". 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, rewriting SQL Server should be the most effort-saving method.

I. Prerequisites for rewriting:

The system has installed SQL Server and installed the SP3 patch. The Access in the Office suite is installed. You can use an editor that supports plain text editing and displays row numbers. We recommend that you use Ultra Edit, 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 SQL Server 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 an mdf file exists, you can use the Enterprise Manager to directly attach it. If an SQL script file is provided, create an 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, there is basically no need to rewrite the database.

2. If SQL database or script files are not provided, you have to do it yourself. This is also the main solution of our 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 are no automatic fields in the SQL database table. Therefore, the original Automatic fields in access are converted to common fields. You need to manually change them to the identification type and the increment is 1.

(2) If the default value is defined for all time fields, it must have been 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, the Field Types of access and SQL change after many conversions. 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, and 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
'The first time a free user uses the database, modify the address of the database and change the da accordingly.Name of the database in the ta directory. 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

If you use SQL, you can delete the access statement, that is, before the on error resume next in else:

Dim ConnStr
'SQL database connection parameters: Database Name, user password, user name, and 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 &";"
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 = dvbbs7; DaTa Source = (local );"

The database name, data source, user, and password are rewritten 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, and if there is an error, 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) 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. These quotation marks must be removed from SQL, at the same time, # may be added before and after the time format, which also needs to 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 some of them are not very common. If you encounter them, you can reply here to discuss them.

V. 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 general method I used is as follows: after the database is rewritten, the program sequence 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.

After the database is imported, fields that need to be automatically added must be overwritten, and the length of all numeric types must be increased. It is best to use decimal.

All default values are lost. It mainly belongs to the numeric type and date type.

All now (), time (), date () must be changed to getdate ().

Change all datediff ('D', time1, time2) to datediff (day, time1, time2)

Some types of true/false may not be available and must be changed to 1/0.

The remarks type must be cast (column as varchar.

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 be incomplete.

Change isnull (rowname) to rowname = null

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!

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. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.

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 ".

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.

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.

The following table compares the collection of data and objects (such as tables, queries, or forms) in a Microsoft Access database (such as a Microsoft Access database), which is related to a specific subject or purpose. The Microsoft Jet Database Engine is used to manage data .) And MicrosoftAccess Project (MicrosoftAccess project: Access files used to connect to the MicrosoftSQLServer database and create client/server applications. The project file does not contain any data or data-defined objects (such as tables or views ).) Data Type (Data Type: determines the field features of the data type that a field can possess. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default )).

Microsoft Access Data Type SQLServer Data Type

Yes/No ("yes/no" Data Type: a field data type, used for fields with only two possible values (If yes or no, True, or False. Null values are not allowed .) Bit (bit data type: In an Access Project, a data type with a storage value of 1 or 0. Accept integers other than 1 and 0, but always interpret them as 1 .)

Number ("Number" Data Type: a field data type in the Microsoft Access database, used for the numerical data used in mathematical operations. However, if you want to display or calculate the currency value, you should use the "currency" data type .) (Byte) tinyint (tinyint data type: a data type that occupies one byte (8 bits) in the Access Project, used to store integers ranging from 0 to 255 .)

Integer smallint (smallint data type: A 2-byte (16-bit) data type in the Access Project, stored in-2 ^ 15 (-32,768) the number between 2 ^ 15-1 (32,767 .)

Number (long integer) int (int data type: A 4-byte (32-bit) data type in the Access Project, stored in-2 ^ 31 (-2,147,483,648) the number between 2 ^ 31-1 (2,147,483,647 .)

Real (single-precision floating point type) real (real data type: In an Access Project, an approximate numerical data type with a precision of 7 bits, the value range of positive values is roughly from 1.18E-38 to 3.40E + 38, and the value range of negative values is roughly from-1.18E-38 to-3.40E + 38, or 0 .)

(No equivalent data type) bigint (bigint Data Type: an 8-byte (64-bit) data type in the Access Project, stored in-2 ^ 63 (-9,223,372,036,854,775,808) A number between 2 ^ 63-1 (9,223,372,036,854,775,807 .)

Float data type: In an Access project, it is an approximate numeric data type with a precision of 15 bits. It stores a positive value range from 2.23E-308 to 1.79E + 308, and a negative value range from-2.23E-308 to-1.79E + 308, or 0 .)

Currency .) Money (money data type: In an Access Project, the data type used to store the currency value. The value range is-922,337,203,685,477.5707 to 922,337,203,685,477.5807, and the precision is one thousandth of the currency units .)

Smallmoney. When smallmoney values are displayed, they are rounded to two decimal places .)

Decimal data type (Access Database): exact numeric data type, used to store values ranging from-10 ^ 38-1 to 10 ^ 38-1. You can specify the value range (the maximum number of digits) and precision (the maximum number of digits on the right of the decimal point ).) Decimal (decimal data type (Access Project): exact numeric data type, used to store values ranging from-10 ^ 38-1 to 10 ^ 38-1. You can specify the value range (maximum total digits) and precision (maximum digits on the right of the decimal point ).)

Numeric (numeric data type: In an Access Project, a precise value data type, ranging from-10 ^ 38-1 to 10 ^ 38-1. You can specify the value range (maximum total digits) and precision (maximum digits on the right of the decimal point ).)

Date/time ("date/time" Data Type: an Access database data type used to store date and time information .) Datetime (datetime data type: In the Access project, the Data Type of date and time, ranging from January 1, January 1-9, 1753 to January 1, December 31, 999, with an accuracy of 1/300 seconds, that is, 3.33 milliseconds .)

Smalldatetime (smalldatetime data type: a date and time data type in the Access project. The precision is not as good as that of the datetime data type. The data value range is from January 1, January 1-20, 1900 to January 1, June 6. The accuracy is one minute .)

Automatic numbering ("automatic numbering" Data Type: a field data type in the Microsoft Access database. When a new record is added to the table, this type of data automatically stores a unique number for each record. Three numbers can be generated: sequence number, random number, and synchronous copy ID .) (Incremental) int (int data type: A 4-byte (32-bit) data type in the Access Project, stored in-2 ^ 31 (-2,147,483,648) the number between 2 ^ 31-1 (2,147,483,647 .) (Defined the Identity attribute)

Text ("text" Data Type: a field data type in the Microsoft Access database. The "text" data type can contain a maximum of 255 characters, or a smaller number of characters specified by the FieldSize attribute .) (N) varchar (n) (varchar (n) Data Type: a variable-length data type in the Access project. The maximum length is 8,000 ANSI characters .)

Nvarchar (n) (nvarchar (n) Data Type: In an Access Project, a variable-length data type can contain up to 4,000 Unicode characters. The Unicode Character occupies two bytes and supports all international characters .)

Remarks ("Remarks" Data Type: a field data type in the Microsoft Access database. The remarks field can contain a maximum of 65,535 characters .) Text (text Data Type: a variable-length data type in the Access project. It can store up to 2 ^ 31-1 (2,147,483,647) characters. The default length is 16 .)

OLE object ("OLE object" Data Type: One of the Data Types of fields, used for creating, linking, or embedding (inserting) objects in the Access database in other applications .) Image (image data type: In an Access Project, a variable-length data type can store up to 2 ^ 31-1 (2,147,483,647) bytes of binary data. The image data type is used to store BLOB (Binary Large Object), slices, documents, sounds, and compiled code .)

Synchronous replication ID (also known as globally unique identifier (GUID: In the Access database, a 16-byte field used to create a unique identifier for Synchronous replication. GUID is used to identify copies, child sets, tables, records, and other objects. In the Access database, GUID refers to the synchronous replication ID .) (GUID) uniqueidentifier (uniqueidentifier data type: A 16-byte GUID In the Access project ).) (Applicable only to SQLServer7.0 or later)

Hyperlink: the data type of the Access database field that stores the hyperlink address. The address can contain up to four parts, and is written in the following syntax format: displaytext # address # subaddress #.)

Char (char data type: In an Access Project, a fixed-length data type can contain a maximum of 8,000 ANSI characters .), Nchar (nchar data type: In an Access Project, a fixed-length data type can contain up to 4,000 Unicode characters. The Unicode Character occupies two bytes and supports all international characters .), Varchar and nvarchar (set the Hyperlink attribute to Yes)

(No equivalent data type) varbinary (varbinary data type: a variable-length data type in the Access project, which can store up to 8,000 bytes of binary data .)

(No equivalent data type) smallint (smallint data type: A 2-byte (16-bit) data type in the Access Project, stored in-2 ^ 15 (-32,768) the number between 2 ^ 15-1 (32,767 .)

(No equivalent data type) timestamp (timestamp data type): In an Access Project, a data type that is automatically updated every time a row is inserted or updated. The value in the timestamp column is not datetime data, but binary (8) or varbinary (8), indicating the order of data modification .)

(No equivalent data type) charnchar

(No equivalent data type) SQL _variant (SQL _variant data type: a data type in the Access Project, stores values of different data types except text, ntext, image, timestamp, and SQL _variant. Used in the return values of columns, parameters, variables, or user-defined functions .)

(No equivalent data type) User-Defined (user-defined data type: In the MicrosoftSQLServer database, the type definition of data contained in a column is allowed, defined by users using existing system data types. Rules and default values can only be bound to user-defined data types .)

Note in an Access project or SQLServer database, the prefix "n" indicates "country/region", meaning that Unicode is enabled for this data type. In the Access database, Unicode is enabled for all text columns by default.