Hi:
I'm trying to use Access 2003 database in a WCF transaction. I got the error message
"The ITransactionLocal interface is not supported by the 'Microsoft.Jet.OleDB.4.0' provider. Local transactions are unavailable with the current provider.
I guess that Jet provider doesn't support System.Transactions. But is there a way to make Access 2003 database coorporate with WCF transactions Thank you!
The ITransactionLocal interface is not supported...
Brennon
After a bit of digging, we've been able to come up with a bit more information to try to help you out here.
First a couple links that may help you and your app along (mostly Access specific):
An overview of what’s new in Jet 4.0, which has a link for Transactions: http://support.microsoft.com/kb/275561
Advanced Microsoft Jet SQL for Access 2000, which also has a link for Transactions: http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx
Import or link tables from another Microsoft Access database or Microsoft Access project: http://msdn2.microsoft.com/en-us/library/aa831310(office.10).aspx
Rename and set properties for linked tables in a Microsoft Access database: http://msdn2.microsoft.com/en-us/library/aa170300(office.10).aspx
Link tables in an Access project by using the Link Table Wizard (ADP): http://office.microsoft.com/en-us/access/HP030893711033.aspx pid=CH063655171033
The Access Community page: http://msdn2.microsoft.com/en-us/office/aa905410.aspx
If we understand your design from above, the links dealing with “link tables” may be of most use to you. This allows you to link tables across multiple Access db’s and access them under 1 Access transaction.
From the above discussion, it looks as though you are only dealing with data within Access databases, if this is the case then TransactionScope and the various transactional attributes in WCF won’t be of much help to you directly.
AlexBB
wow... that's great information. Thanks.
However I may didn't make myself clear - I'm trying to achieve this in an internet environment. The link table feature requires a file share, which is not available to me.
Christie Myburgh
Unfortunately I'm bound to Access 2003 by customer's requirement. Let me describe my scenario:
A worker can enter work items offline on a PC. These work items are saved on local database (Access 2003 as well). Then when connected, the worker can send local work items to the server database, which is also an Access 2003 database.
On the client PC we need to record which data has been synchronized so we don't repeat the operation (the records get new ids in server database during synchronization, so it's hard to find previously synchronized records)
so the client method is like this
sync()
{
using (TransactionScope myTransaction = new TransactionScope())
{
try
{
serverproxy.Sync();
local sync method();
myTransaction.Complete();
}
catch (Exception ex)
{MessageBox.Show(ex.Message);
}
}
}
Bothe serverproxy.sync() method and local sync method() use OleDbConnection to operate on two Access databases.
As Access 2003 doesn't seem to allow ditributed transaction, now I'm thinking to abadon the transaction all together, but do the synchornization on a record-by-record basis. This means:
syn()
{
foreach records to be synced
{
if (serviceproxy.sync_the_record())
local record->set as "synchronized";
else
local record->set as "synchoronzation failed";
}
}
Since we don't have a transaction scope now, bad things can happen - the record is saved on the server, but the service call failed due to conneciton failure. I think for this we have to maintain a synchronization log on the server so we can save which work item record is mapped to which record on the server --- the logging and record inserting can be put in a local OleDb transaction. Later on, when client try to re-submit the record (as client thinks it's not synchronized), sync_the_record can just turn back and say "hey, this is already done".
I'm almost positive this would work, but i have some doubts:
1) is this per-record service call too expensive
2) is there any bad scenario that I failed to consider
Any help is greatly appreciated. Thanks again.
Kennon2005
in WCF,we just pause transaction context in the soap message,so receiving app can take transaction context and join distributed transaction (This is not different from COM+/MSDTC model)
As i know,Access can't partcipate in distrubuted transaction(I am not very sure,but i never seen anybody doing distributed transaction in Access from long time),so this must be Acces 2003 limitation.
-Thank you
Madhu