When can I use transactions in VB6 / ADO 2.8?

From: Jim Rodgers (noway_at_jose.Net)
Date: 12/19/04 

Date: Sun, 19 Dec 2004 11:14:48 -0500

Hello!

I am using .BeginTrans, .CommitTrans, etc., extensively in an application.
Works great. Now I am trying to run the application with connections to
different data providers. So far, it works great with a local MSJet4 file
(.mdb) or with the file on another Windows server on the LAN.

Now I want to use the "MS Remote" (RDS) connection provider to that same
file on another computer. It seems that transactions are not supported with
MS Remote when I use an ODBC Data Source (in this case, a System DSN to an
Access 2000 file). Everything else works great except for the error
"Transactions are not supported by the Remote Provider."

Is this an issue with MS Remote (RDS)? Or might this work with a remote
provider other than ODBC? Do any of the other OLEDB providers support
transactions through the "MS Remote" connection provider? I tried the Jet
OLEDB provider as the remote provider, but the app did not work at all
because the SQL command strings of type adStoredProc no longer worked with
the stored queries in the mdb file. If I had figured that one out, would
the transactions have worked then?

What other options might I consider? Should I learn to write a custom
business object that acts like the default data factory, but which supports
transactions as a method somehow? (I've never written a business object
before.) Is there an excellent tutorial somewhere? Sometimes I think
Microsoft is intent upon suppressing productivity in this regard. Does
anyone have a "Hello World" for a custom business object?

(Rewriting the whole app for .NET is not an option: It work too well to
screw it up now. Besides, it's just way too big and uses every
"VB6-but-not-VB.NET" technique in the book.)

Any ideas? I would be especially grateful!

 - Jim Rodgers

Relevant Pages

  • Re: Rows not transferring
    ... Both source and destination are 'Microsoft OLE DB Provider for SQL Server' ... So I fixed that by adding the connection ...
    (microsoft.public.sqlserver.dts)
  • Re: simple question re DELETEing in linked DB
    ... > actually, in this particular case, a simple remote procedure call would> suffice - I don't much care what the return value is ... ... > I hadn't had any luck using openquery to execute a remote procedure, but am> still looking into this seems as it should work. ... If you only want SQL Server>> to issue the delete query, and you don't care what happens, how many ... >> transactions, etc., you don't need SQL Server and an ODBC or OLE DB>> provider - you only need a command-line query tool for the remote ...
    (microsoft.public.sqlserver.programming)
  • When can I use transactions in VB6 / ADO 2.8?
    ... Now I want to use the "MS Remote" connection provider to that same ... "Transactions are not supported by the Remote Provider." ...
    (microsoft.public.data.odbc)
  • Unknown provider error on a simple insert
    ... On Windows Server 2000 with SQL 2000 SP3 I opened a Query ... This remote table is on SQL 2000 SP3 on Windows ... OLE DB provider 'SQLOLEDB' could not INSERT INTO ...
    (microsoft.public.sqlserver.connect)
  • When can I use transactions in VB6 / ADO 2.8?
    ... Now I want to use the "MS Remote" connection provider to that same ... "Transactions are not supported by the Remote Provider." ...
    (microsoft.public.vb.controls.internet)