Differences between Microsoft Access Data Types and SQL Server Data Types

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, to delete an SQL Server database, use: "delete user where.

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 microsoftaccess database), which is related to a specific topic or purpose. The Microsoft Jet Database Engine is used to manage data .) And microsoftaccess Project (microsoftaccess project: connected to the microsoftsqlserver database and used to create customer/server applicationsProgram. 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, to display or calculate the currency value, the "currency" data type should be used .) (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 (maximum total digits) and precision (maximum 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 .) (Identity attribute defined)
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 field data types, used for creation, linking, or embedding (insertion) in other applications) objects in the ACCESS database .) 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, replica 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 ("HYPERLINK" data type: 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.