An automatic unique ID [synchronous replication ID] Using GUID globally unique identifier in ACCESS

Source: Internet
Author: User
Background: During this time, I will create some website programs for a tourism website. The data table is roughly as follows: the general situation of the database table is the Access database. There are two data tables: TC_TourCompany and TC_SubDetail. The former is the relevant information of the travel agency list (for the convenience of description, for the time being, it is also called the head office table). The latter is a subordinate business.

Background: During this time, I will create some website programs for a tourism website. The data table is roughly as follows: the general situation of the database table is the Access database. There are two data tables: TC_TourCompany and TC_SubDetail. The former is the relevant information of the travel agency list (for the convenience of description, for the time being, it is also called the head office table). The latter is a subordinate business.

Background:

During this time, I made some website programs for a tourism website. The data table is roughly as follows: the general situation of the database table is the Access database. There are two data tables: TC_TourCompany and TC_SubDetail. The former is the relevant information of the travel agency list (for the convenience of description, for the time being, it is also called "Head Office table". The latter is the relevant information of the subordinate business department (if any) (For the convenience of description, it is also called "subsidiary table ").

Due to business needs, I want to extend it to data tables applicable to all "company types" (such as hotels, scenic spots, scenic spots, drifting companies, entertainment and catering, air ticket agencies, transportation companies, etc, because hotels, restaurants, entertainment, air ticket agencies, and other companies may have branches or subsidiaries, the table data structure is similar. Therefore, we can use such a data table design to simplify future program development. Of course, we need to add a new column in the data table to describe the types of companies such as travel agencies, hotels, scenic spots, and entertainment companies. It is not in the scope of this article.

To facilitate future classification search and query and ensure the uniqueness of the company (including its subsidiaries), I want to add a column in the above two tables. The column name is GUID. Each record is a unique and non-repeating value, similar to {9E4038C8-E965-45B1-BDE1-9F06E6B280A3.. Net. guid. value generated by NewGuid () and included in braces.

Practice:How does one automatically generate these values for each record when there is a database table record?

At first, I took a detour. When adding a GUID column, I select the Data Type of this column as "Number" and select "synchronous copy ID" in "field size" on the General tab below ", "Yes (no duplicates)" is selected in the index )". I thought it would be fine to save the structure in this way. When opening all the records in the table, I found that the GUID column was completely empty and there was no value! So I tried some ways to insert a unique GUID value. One of the solutions is to use SQL statement updates in ACCESS, and later found that this path is not available. Solution 2 is to use ADO.net programming to update table records, and the workload is not small.

Is there a better way? An occasional thought helps me find a faster and better solution, that isWhen a GUID column is created in the design view, the data type is automatically numbered instead of a number! At the same time, select "synchronous replication ID" in "field size" on the General tab below, and select "Yes (no duplicates)" in the index )".

For example, add a GUID column to the home office directory table (TC_TourCompany table ).

Add the GUID column to the company table (TC_TourCompany table) and then automatically generate the GUID record value to the branch TC_SubDetail table to add the GUID column.

The GUID record value is automatically generated after the GUID column is added to the TC_SubDetail table of the branch office (segment ).

What will happen when new records are added in the future? After testing, it is found that ACCESS will automatically generate the GUID record value. OK, perfect!

For more information:When an SQL statement is generated from Access, the Guid query problem occurs. The string format used in SQL Server cannot query any data.

SELECT * FROM tableName WHERE [GUID] = '2017-90AB-CDEF-1234-567890ABCDEF'

If the column referenced by the condition string is of the GUID type, the syntax used by the condition expression is slightly different: WHERE [GUID] = {GUID {12345678-90AB-CDEF-1234-567890ABCDEF} ensure that the nested braces and font size shown above are included. Note that the embedding of braces is only used for the Where statement. single quotation marks must be used in the Insert statement. Otherwise, the malformed guid in query error will be generated.

For more information, see ASP. NET development experience (3 )---Use the GUID value as the database row IDHttp://blog.joycode.com/moslem/archive/2004/03/23/16930.aspx

Others:Export/print Access database structureHttp://blog.csdn.net/johnsuna/archive/2008/05/05/2393664.aspx

Appendix:

The correspondence between the Access data type and the. net OleDbType Enumeration type

The most common data type ing list

Access type name Database Data Type OLEDB type . NET Framework type Member name
Text VarWChar DBTYPE _ WSTR System. String OleDbType. VarWChar
Memorandum LongVarWCha R DBTYPE _ WSTR System. String OleDbType. LongVarWChar
Number of bytes: UnsignedTinyInt DBTYPE _ UI 1 System. Byte OleDbType. UnsignedTinyInt
Yes/No Boolean DBTYPE_BOOL System. Boolean OleDbType. Boolean
Date/time DateTime DBTYPE _ DATE System. DateTime OleDbType. date
Currency Decimal DBTYPE_NUMERIC System. Decimal OleDbType. numeric
Decimal number: Decimal DBTYPE_NUMERIC System. Decimal OleDbType. numeric
Double Precision Number: Double Precision number DBTYPE_R8 System. Double OleDbType. Double
Automatic number (copy ID) GUID DBTYPE_GUID System. Guid OleDbType. guid
Copy (ID) number: GUID DBTYPE_GUID System. Guid OleDbType. guid
Automatic number (long integer) Integer DBTYPE_I4 System. Int 32 OleDbType. integer
Quantity: (long integer) Integer DBTYPE_I4 System. Int 32 OleDbType. integer
OLE object LongVarBinary DBTYPE_BYTES Array System. Byte OleDbType. LongVarBinary
Single precision number: Single precision number DBTYPE_R4 System. Single OleDbType. single
Integer: SmallInt DBTYPE_I2 System. Int 16 OleDbType. SmallInt
Binary VarBinary * DBTYPE_BYTES Array System. Byte OleDbType. binary
Hyperlink VarWChar DBTYPE _ WSTR System. String OleDbType. VarWChar

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.