Transaction in MS Access

[John]

I am using ADO .Net and Access in a VB .Net app. Does MS Access support
Transaction(BeginTransaction, Commit, Rollback)?

Thanks.

[Ken Tucker [MVP]]

Hi,
Dim ds As New DataSet

Dim da As OleDbDataAdapter

Dim strConn As String

Dim strSQL As String

Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = Northwind.mdb;"

conn = New OleDbConnection(strConn)

da = New OleDbDataAdapter("Select * From Categories", conn)

da.Fill(ds, "Categories")

DataGrid1.DataSource = ds '.Tables("Categories")

DataGrid1.DataMember = "Categories"

Dim tr As OleDbTransaction

Dim cmdBuild As New OleDbCommandBuilder(da)

Dim cmd As OleDbCommand = cmdBuild.GetUpdateCommand

da.UpdateCommand = cmd

conn.Open()

tr = conn.BeginTransaction

cmd.Transaction = tr

Dim dr As DataRow = ds.Tables("Categories").Rows(0)

Debug.WriteLine(da.UpdateCommand.CommandText)

dr.BeginEdit()

dr.Item("CategoryName") = "Drinks"

dr.EndEdit()

da.Update(ds, "Categories")

tr.Commit()

conn.Close()


Ken
-------------------------

"John" <Jo**@discussions.microsoft.com> wrote in message
news:76**********************************@microsof t.com...
I am using ADO .Net and Access in a VB .Net app. Does MS Access support
Transaction(BeginTransaction, Commit, Rollback)?

Thanks.

[Monthep]

Hi Ken,

I had tried "dim as OleDbDataAdapter" and "dim as OleDbConnection", but for
some reason they are not recognized as valid. When I hover the mouse over
the statement, it shows for example "Type OleDbConnection is not defined".
Is there anything I need to do prior to this? What could I be missing? Thank
you very much for your help!
"Ken Tucker [MVP]" wrote:

Hi,
Dim ds As New DataSet

Dim da As OleDbDataAdapter

Dim strConn As String

Dim strSQL As String

Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = Northwind.mdb;"

conn = New OleDbConnection(strConn)

da = New OleDbDataAdapter("Select * From Categories", conn)

da.Fill(ds, "Categories")

DataGrid1.DataSource = ds '.Tables("Categories")

DataGrid1.DataMember = "Categories"

Dim tr As OleDbTransaction

Dim cmdBuild As New OleDbCommandBuilder(da)

Dim cmd As OleDbCommand = cmdBuild.GetUpdateCommand

da.UpdateCommand = cmd

conn.Open()

tr = conn.BeginTransaction

cmd.Transaction = tr

Dim dr As DataRow = ds.Tables("Categories").Rows(0)

Debug.WriteLine(da.UpdateCommand.CommandText)

dr.BeginEdit()

dr.Item("CategoryName") = "Drinks"

dr.EndEdit()

da.Update(ds, "Categories")

tr.Commit()

conn.Close()


Ken
-------------------------

"John" <Jo**@discussions.microsoft.com> wrote in message
news:76**********************************@microsof t.com...
I am using ADO .Net and Access in a VB .Net app. Does MS Access support
Transaction(BeginTransaction, Commit, Rollback)?

Thanks.

[james]

Be sure you have this statement above the beginning of your Class:

Imports System.Data.OleDb

Then your Dim statements should work..............

james

"Monthep" <Mo*****@discussions.microsoft.com> wrote in message news:82**********************************@microsof t.com...

Hi Ken,

I had tried "dim as OleDbDataAdapter" and "dim as OleDbConnection", but for
some reason they are not recognized as valid. When I hover the mouse over
the statement, it shows for example "Type OleDbConnection is not defined".
Is there anything I need to do prior to this? What could I be missing? Thank
you very much for your help!
"Ken Tucker [MVP]" wrote:

Hi,
Dim ds As New DataSet

Dim da As OleDbDataAdapter

Dim strConn As String

Dim strSQL As String

Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = Northwind.mdb;"

conn = New OleDbConnection(strConn)

da = New OleDbDataAdapter("Select * From Categories", conn)

da.Fill(ds, "Categories")

DataGrid1.DataSource = ds '.Tables("Categories")

DataGrid1.DataMember = "Categories"

Dim tr As OleDbTransaction

Dim cmdBuild As New OleDbCommandBuilder(da)

Dim cmd As OleDbCommand = cmdBuild.GetUpdateCommand

da.UpdateCommand = cmd

conn.Open()

tr = conn.BeginTransaction

cmd.Transaction = tr

Dim dr As DataRow = ds.Tables("Categories").Rows(0)

Debug.WriteLine(da.UpdateCommand.CommandText)

dr.BeginEdit()

dr.Item("CategoryName") = "Drinks"

dr.EndEdit()

da.Update(ds, "Categories")

tr.Commit()

conn.Close()


Ken
-------------------------

"John" <Jo**@discussions.microsoft.com> wrote in message
news:76**********************************@microsof t.com...
I am using ADO .Net and Access in a VB .Net app. Does MS Access support
Transaction(BeginTransaction, Commit, Rollback)?

Thanks.

[Pete Wright]

Make sure that at the very top of your class you have the line

imports System.Data.OleDb

That will solve the problem.
--
Pete Wright
Author of ADO.NET Novice to Pro for Apress
www.petewright.org

"Monthep" <Mo*****@discussions.microsoft.com> wrote in message
news:82**********************************@microsof t.com...

Hi Ken,

I had tried "dim as OleDbDataAdapter" and "dim as OleDbConnection", but
for
some reason they are not recognized as valid. When I hover the mouse
over
the statement, it shows for example "Type OleDbConnection is not defined".
Is there anything I need to do prior to this? What could I be missing?
Thank
you very much for your help!
"Ken Tucker [MVP]" wrote:

Hi,
Dim ds As New DataSet

Dim da As OleDbDataAdapter

Dim strConn As String

Dim strSQL As String

Dim conn As OleDbConnection

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn &= "Data Source = Northwind.mdb;"

conn = New OleDbConnection(strConn)

da = New OleDbDataAdapter("Select * From Categories", conn)

da.Fill(ds, "Categories")

DataGrid1.DataSource = ds '.Tables("Categories")

DataGrid1.DataMember = "Categories"

Dim tr As OleDbTransaction

Dim cmdBuild As New OleDbCommandBuilder(da)

Dim cmd As OleDbCommand = cmdBuild.GetUpdateCommand

da.UpdateCommand = cmd

conn.Open()

tr = conn.BeginTransaction

cmd.Transaction = tr

Dim dr As DataRow = ds.Tables("Categories").Rows(0)

Debug.WriteLine(da.UpdateCommand.CommandText)

dr.BeginEdit()

dr.Item("CategoryName") = "Drinks"

dr.EndEdit()

da.Update(ds, "Categories")

tr.Commit()

conn.Close()


Ken
-------------------------

"John" <Jo**@discussions.microsoft.com> wrote in message
news:76**********************************@microsof t.com...
I am using ADO .Net and Access in a VB .Net app. Does MS Access support
Transaction(BeginTransaction, Commit, Rollback)?

Thanks.