Re: HELP: Auto commit with ODBC Oracle & MS ACCESS

gensbit_at_club-internet.fr (Philippe GENSBITTEL) wrote:

>[Question on how to get rid of autocommit]

Hi,

If you are using Access, why don't you link the Oracle-Tables into your Access database and then use a Transaction like it is shown in the online help (hope it works in v2 too since I'm using v7) - I have translated the important comments into English:

Sub PositionÄndern()

	Dim wsp As Workspace, dbs As Database, rst As Recordset
	Dim strName As String, strMeldung As String, strEingabe As String

	strEingabe = "Position ändern zu Sachbearbeiter - Kundenwerbung?"
	' Standardarbeitsbereich abrufen.
	Set wsp = DBEngine.Workspaces(0)
	' Aktuelle Datenbank abrufen.
	Set dbs = CurrentDb
	' Recordset-Objekt vom Typ Tabelle erstellen.
	Set rst = dbs.OpenRecordset("Personal", dbOpenTable)

	' BEGIN OF THE TRANSACTION
	wsp.BeginTrans
	rst.MoveFirst
	Do Until rst.EOF
		If rst![Position] = "Verkaufsrepräsentant" Then
			strName = rst![Nachname] & ", " & rst![Vorname]
			strMeldung = "Mitarbeiter: " & strName & vbCRLF & vbCRLF
			If MsgBox(strMeldung & strEingabe, vbQuestion + vbYesNo, _
			"Position ändern") = vbYes Then
				' Bearbeitung ermöglichen.
				rst.Edit
				rst![Position] = "Sachbearbeiter - Kundenwerbung"
				' Änderungen speichern.
				rst.Update
			End If
		End If
		' Zum nächsten Datensatz wechseln.
		rst.MoveNext
	Loop
	If MsgBox("Alle Änderungen speichern?", vbQuestion + vbYesNo, _
	" Änderungen speichern") = vbYes Then
		wsp.CommitTrans			' APPLY CHANGES.
	Else
		wsp.Rollback			' DISCARD CHANGES.
	End If

Bye,
  Robert Beicht
  beicht_at_sulzer.de
  http://www.lrz-muenchen.de/~p7003ot
  +498931607649 Received on Tue Jul 09 1996 - 00:00:00 CEST