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

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