Database Search

match allmatch any Look in all areasLook in current areas Check all datesPrevious dayPrevious 3 daysPrevious weekPrevious fortnightPrevious monthPrevious 2 monthsPrevious 3 monthsPrevious half-yearPrevious year

TechHome

• Access 2000 Advanced DML Transactions
• Access Recordset to CSV File UDF
• Access RunCommand Constants
• ACCESS Table or Query Exists
• Access to Excel via SQL
• Access Visible Problem
• Adding Fields to an Existing Access Table
• Advanced DML Transactions
• Advanced SQL in the Jet 4.0 Provider Example
• Another OLE with Access Example
• Automating Access Tutorial
• Check if Access Table Exists
• Commit Rollback Begin Trans Updatebatch
• Control Access Forms
• Count Access Records
• Csv to Access
• Dealing with ACCESS 97 Field Types
• execute a macro in Access
• Execute a Public function in Access
• Export Access Report in Choice of Formats
• Export MDB to Flat File
• Find Read and Insert Data in Access
• Gather ACCESS Table and Column Information Into Excel
• Get Access Version That Created MDB File
• Get Column Data From an Access Database
• Get Record Count
• How to Use the OpenTable method
• Launch Access Macro from Commandline
• Loop thru a recordset in Access
• Office 2003 Macro Security Issue
• OLE and Access
• OLE Import a table into Access
• OLE Import Table into Access
• Open Access DB with DAO
• Pack an Access Database
• Query and Export Data from Microsoft Access
• Read Write Delete Tables from Access DB
• Repair MDB
• Retrieve Data in Access
• SQL UPDATE Fails with Access
• UDF to Return Record Count
• Updating Table From Filtered Recordset
• Ways to Get Data from and Access file without Access installed

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Winbatch - Access 2000 Advanced DML Transactions - This Example Demonstrates the Advanced SQL in the Jet 4.0 Provider

Keywords: 	 Advanced SQL in the Jet 4.0 Provider example

---

Winbatch - Access 2000 Advanced DML Transactions

The sample cty.mdb includes two tables, Codes and Codesbackup (which are identical). The script below illustrates the Advanced SQL in the Jet 4.0 Provider, specifically to BEGIN, Execute, then either COMMIT or ROLLBACK Transactions

NOTE: these commands are not available in Access or DAO, and should not be mixed with ADO Transaction functions.

Stan Littlefield December 20, 2002

---

; ///////////////////////////////////////////////////////
; Winbatch - Access 2000 Advanced DML Transactions
; The sample cty.mdb includes two tables, Codes and Codesbackup
; (which are identical). The script below illustrates the
; Advanced SQL in the Jet 4.0 Provider, specifically to BEGIN,
; Execute, then either COMMIT or ROLLBACK Transactions
;
; NOTE: these commands are not available in Access or DAO, and
;       should not be mixed with ADO Transaction functions.
;
; Stan Littlefield December 20, 2002
; ///////////////////////////////////////////////////////

src   = StrCat( DirGet(),"CTY.MDB" )
If ! FileExist( src ) Then Exit
IntControl( 72,1,0,0,0 )

BoxOpen("Performing Access Transaction Processing","Opening %src%")
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%src%"
DB    = ObjectOpen("ADODB.Connection")
DB.Open(cConn)


cSQL = "BEGIN TRANSACTION;"
DB.Execute(cSQL)

cSQL = "DELETE * FROM Codes WHERE Dialcode>'59999';"
DB.Execute(cSQL)

; o.k. so the delete command has been issued
; do we want to change our mind?

result = AskYesNo( "Rows Have Been Deleted", "Do You Want To Permantly Remove Them?")

If result == @YES
   ; data is gone per query.
   cSQL = "COMMIT TRANSACTION;"
   DB.Execute(cSQL)
   goto end
Endif

:oops
; data will not be permanently deleted
cSQL = "ROLLBACK TRANSACTION;"
DB.Execute(cSQL)

:end
DB.Close()
ObjectClose( DB )
BoxShut()
Exit

:Cancel
Goto oops

---

Download city.mdb and zip file:

access2000trans.zip

---

Article ID:   W15616

File Created: 2017:08:29:11:58:44

Last Updated: 2017:08:29:11:58:44