Rollback doesn't work in Linked Table??..urgent..

T

TJ

Hi,

Here is my brief code for transaction testing on linked-table.
I am using MS-ACCESS 2003
TargetTable is linked table, which from sharepiont list.

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
cnn1.BeginTrans

Set rsTo = New ADODB.Recordset
Set rsTo.ActiveConnection = cnn1
rsTo.CursorLocation = adOpenKeyset
rsTo.LockType = adLockOptimistic
rsTo.Source = "TargetTable"
rsTo_Open

rsTo.AddNew
rsTo![A] = "Test1"
rsTo! = "Test2"
rsTo.Update

cnn1.RollbackTrans

rsTo.Close
Set rsTo = Nothing
cnn1.Close
Set cnn1 = Nothing

When I tested this code on local table, it worked. In other words, by
roll-back, new record wasn't added in the local table. However, when I tested
this code on the linked table, roll back didn't work. Though RollbackTrans
was executed, new record was still added.

How am I supposed to write for transaction code on linked-table?

Any suggesstion would be greately appreciated.

Thanks.

....................................................................TJ
 
D

Douglas J. Steele

Don't know for certain, but it makes sense to me that you need to specify a
connection to the actual table, not to the linked table.
 
D

david epsom dot com dot au

DAO transactions include linked tables.

Jet transactions work on 'Workspaces'. It doesn't
make sense that ADO would spawn a separate workspace
for a linked table, but testing always beats logic...

(david)
 
T

TJ

Hi,

Thanks for the answer.
However, are you sure that dao transactions include linked tables?
After I changed the ado code to dao, I've tested it forthe linked
tables.However,
it didn't work either. New record was still added even though rollback was
executed.

Thanks again.

.................................................................TJ

david epsom dot com dot au said:
DAO transactions include linked tables.

Jet transactions work on 'Workspaces'. It doesn't
make sense that ADO would spawn a separate workspace
for a linked table, but testing always beats logic...

(david)

TJ said:
Hi,

Here is my brief code for transaction testing on linked-table.
I am using MS-ACCESS 2003
TargetTable is linked table, which from sharepiont list.

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
cnn1.BeginTrans

Set rsTo = New ADODB.Recordset
Set rsTo.ActiveConnection = cnn1
rsTo.CursorLocation = adOpenKeyset
rsTo.LockType = adLockOptimistic
rsTo.Source = "TargetTable"
rsTo_Open

rsTo.AddNew
rsTo![A] = "Test1"
rsTo! = "Test2"
rsTo.Update

cnn1.RollbackTrans

rsTo.Close
Set rsTo = Nothing
cnn1.Close
Set cnn1 = Nothing

When I tested this code on local table, it worked. In other words, by
roll-back, new record wasn't added in the local table. However, when I
tested
this code on the linked table, roll back didn't work. Though RollbackTrans
was executed, new record was still added.

How am I supposed to write for transaction code on linked-table?

Any suggesstion would be greately appreciated.

Thanks.

...................................................................TJ
Click to expand...
Click to expand...
 
D

david epsom dot com dot au

DAO did work for linked tables, and does still work
for linked MDB tables. But it seems that every time
they bring out a new version of Jet, they break something.

Perhaps a SQL Server person would be able to help? You
could try asking in an ODBC/OLEDB group?

(david)



TJ said:
Hi,

Thanks for the answer.
However, are you sure that dao transactions include linked tables?
After I changed the ado code to dao, I've tested it forthe linked
tables.However,
it didn't work either. New record was still added even though rollback was
executed.

Thanks again.

................................................................TJ

david epsom dot com dot au said:
DAO transactions include linked tables.

Jet transactions work on 'Workspaces'. It doesn't
make sense that ADO would spawn a separate workspace
for a linked table, but testing always beats logic...

(david)

TJ said:
Hi,

Here is my brief code for transaction testing on linked-table.
I am using MS-ACCESS 2003
TargetTable is linked table, which from sharepiont list.

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
cnn1.BeginTrans

Set rsTo = New ADODB.Recordset
Set rsTo.ActiveConnection = cnn1
rsTo.CursorLocation = adOpenKeyset
rsTo.LockType = adLockOptimistic
rsTo.Source = "TargetTable"
rsTo_Open

rsTo.AddNew
rsTo![A] = "Test1"
rsTo! = "Test2"
rsTo.Update

cnn1.RollbackTrans

rsTo.Close
Set rsTo = Nothing
cnn1.Close
Set cnn1 = Nothing

When I tested this code on local table, it worked. In other words, by
roll-back, new record wasn't added in the local table. However, when I
tested
this code on the linked table, roll back didn't work. Though
RollbackTrans
was executed, new record was still added.

How am I supposed to write for transaction code on linked-table?

Any suggesstion would be greately appreciated.

Thanks.

...................................................................TJ
Click to expand...
Click to expand...
Click to expand...
 
T

TJ

Thanks.

You seems to right when I tested it...
Thanks for your answer again..

BTW, do you have any ideas how to solve this issue?

.......................................................TJ


david epsom dot com dot au said:
DAO did work for linked tables, and does still work
for linked MDB tables. But it seems that every time
they bring out a new version of Jet, they break something.

Perhaps a SQL Server person would be able to help? You
could try asking in an ODBC/OLEDB group?

(david)



TJ said:
Hi,

Thanks for the answer.
However, are you sure that dao transactions include linked tables?
After I changed the ado code to dao, I've tested it forthe linked
tables.However,
it didn't work either. New record was still added even though rollback was
executed.

Thanks again.

................................................................TJ

david epsom dot com dot au said:
DAO transactions include linked tables.

Jet transactions work on 'Workspaces'. It doesn't
make sense that ADO would spawn a separate workspace
for a linked table, but testing always beats logic...

(david)

Hi,

Here is my brief code for transaction testing on linked-table.
I am using MS-ACCESS 2003
TargetTable is linked table, which from sharepiont list.

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
cnn1.BeginTrans

Set rsTo = New ADODB.Recordset
Set rsTo.ActiveConnection = cnn1
rsTo.CursorLocation = adOpenKeyset
rsTo.LockType = adLockOptimistic
rsTo.Source = "TargetTable"
rsTo_Open

rsTo.AddNew
rsTo![A] = "Test1"
rsTo! = "Test2"
rsTo.Update

cnn1.RollbackTrans

rsTo.Close
Set rsTo = Nothing
cnn1.Close
Set cnn1 = Nothing

When I tested this code on local table, it worked. In other words, by
roll-back, new record wasn't added in the local table. However, when I
tested
this code on the linked table, roll back didn't work. Though
RollbackTrans
was executed, new record was still added.

How am I supposed to write for transaction code on linked-table?

Any suggesstion would be greately appreciated.

Thanks.

...................................................................TJ
Click to expand...
Click to expand...
Click to expand...
 
D

david epsom dot com dot au

I haven't seen this problem. We have seen problems
with transaction blocking on ODBC, but no problems
at all with rollback.

(david)

TJ said:
Thanks.

You seems to right when I tested it...
Thanks for your answer again..

BTW, do you have any ideas how to solve this issue?

......................................................TJ


david epsom dot com dot au said:
DAO did work for linked tables, and does still work
for linked MDB tables. But it seems that every time
they bring out a new version of Jet, they break something.
TargetTable is linked table, which from sharepiont
Click to expand...

Perhaps a SQL Server person would be able to help? You
could try asking in an ODBC/OLEDB group?

(david)



TJ said:
Hi,

Thanks for the answer.
However, are you sure that dao transactions include linked tables?
After I changed the ado code to dao, I've tested it forthe linked
tables.However,
it didn't work either. New record was still added even though rollback
was
executed.

Thanks again.

................................................................TJ

:

DAO transactions include linked tables.

Jet transactions work on 'Workspaces'. It doesn't
make sense that ADO would spawn a separate workspace
for a linked table, but testing always beats logic...

(david)

Hi,

Here is my brief code for transaction testing on linked-table.
I am using MS-ACCESS 2003
TargetTable is linked table, which from sharepiont list.

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
cnn1.BeginTrans

Set rsTo = New ADODB.Recordset
Set rsTo.ActiveConnection = cnn1
rsTo.CursorLocation = adOpenKeyset
rsTo.LockType = adLockOptimistic
rsTo.Source = "TargetTable"
rsTo_Open

rsTo.AddNew
rsTo![A] = "Test1"
rsTo! = "Test2"
rsTo.Update

cnn1.RollbackTrans

rsTo.Close
Set rsTo = Nothing
cnn1.Close
Set cnn1 = Nothing

When I tested this code on local table, it worked. In other words,
by
roll-back, new record wasn't added in the local table. However, when
I
tested
this code on the linked table, roll back didn't work. Though
RollbackTrans
was executed, new record was still added.

How am I supposed to write for transaction code on linked-table?

Any suggesstion would be greately appreciated.

Thanks.

...................................................................TJ
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rollback not working in linked table??? 0
Access Transaction Rollback problem 12
building a linked table in VBA 2

Top