Option Compare Database 'This example Demo the Transaction statement in Access How to use' Please copy the following code COPY to a new "Module" 'Press F5 to view the running effect
'This example assumes that "Table 1" and "Table 2" have contacts on the data relationship,' After "Table 1" is deleted, you must add a line record in "Table 2", 'If the record is unsuccessful, then " Data of Table 1 cannot be deleted
Sub TransactionTest1 () 'When there is an error code, use Rollback Transaction Roll Roll Guaranteed Data Integrity' The following code adds some demo data to Table 1 AddValues Dim Strsql as string strsql = "delete from Table 1" on Error Resume Next CurrentProject.Connection. Execute "Begin Transaction" CURRENTPROJECT.CONNECTION.EXECUTE STRSQL 'Note that since the "Numbe" is "Auto Number" type, people do not allow people to add data,' So the following SQL statement will generate an error strsql = "INSERT INTO Table 2 (ID) values ( '2') "CurrentProject.Connection.Execute strSQL If Err <> 0 Then Debug.Print Err.Number &" -> "& Err.Description CurrentProject.Connection.Execute" ROLLBACK TRANSACTION "Else CurrentProject.Connection The number of records of "commit transaction" end if debug.print "" Table 1 "is:"; DCOUNT ("*", "Table 1") & _ ". Note:" Table 1 "Record" & _ IIF (DCOUNT ("*", "Table 1") = 0, "has been deleted", "unstead") End Sub
Sub TransactionTest2 () 'when there is no error code End with COMMIT TRANSACTION' the following code to add some demo data in Table 1 AddValues Dim strSQL As String strSQL = "delete from Table 1" On Error Resume Next CurrentProject.Connection.Execute "begin TRANSACTION "CurrentProject.Connection.execute strsql 'is compared with TransactionTest1, the row SQL code is correct. Strsql = "INSERT INTO Table 2 ([LOG]) VALUES ('already in" & now () & "successfully deleted table 1 data')" CurrentProject.Connection.execute strsql if Err <> 0 Then Debug.print Err.Number & "->" & Err.Description CurrentProject.Connection.Execute "ROLLBACK TRANSACTION" Else CurrentProject.Connection.Execute "COMMIT TRANSACTION" End If Debug.Print "number of records" table 1 "as:"; DCount ( "*" , "Table 1") & _ ". Note:" Table 1 "record" & _ IIF (DCOUNT ("*", "Table 1") = 0, "has been deleted", "unleave") end Subsub TransactionTest3 () 'No transaction mechanism guarantee, data integrity will be destroyed
'The following code add some demo data to Table 1 AddValues
DIM strsql asse string strsql = "delete from table 1" ON Error Resume Next CurrentProject.connection.execute strsql 'Note An error strsql = "INSERT INTO Table 2 VALUES ('2')" CurrentProject.Connection.execute strsql if Err.Number & "->" & Err.Description endiff DEBUG.PRINT "Table 1" The number of records is: "; DCOUNT (" * "," Table 1 ") & _". Note: "Table 1" Record "& _ IIF (DCOUNT (" * "," Table 1 ") = 0," has been deleted "," unstead ") End Sub
'The following two processes are used to prepare for demos, if you are running this example in a new database,' first run the following two processes.
Sub CreateTable () 'The following code has created two tables in your database, ready to demonstrate DIM strsql as string strsql = "CREATE TABLE Table 1 (No. AutoInCrement (1, 1), start_date text (50))" CurrentProject. Connection.execute strsql strsql = "CREATE TABLE Table 2 (No. AutoInCrement (1, 1), [log] text (50))" CurrentProject.Connection.execute strsqlend subnect Sub
