Within the same Workspace

[George]

Hello to everybody....
After hours of searching (and a lot of headache)
i really would appreciate some help....

In the following code example anything works
properly ONLY IF Table1 is a table in a
..mdb file (local or attached).
If Table1 is an ODBC-Linked Table programm
execution stops at line (11) and after the ODBC-
timeout expires i get the message:
"ODBC-Call failed."
Why is this happening only with
ODBC-Linked tables ????


1 Dim myws As Workspace, mydb As Database, mydef As QueryDef
2 Dim myset1 As Recordset

3 Set myws = DBEngine.Workspaces(0)
4 Set mydb = CurrentDb()
5 Set mydef = mydb.QueryDefs("query1")
6 '===activate the next line only if table1 is an ODBC-Linked Table
7 '===mydef.Connect =
"ODBC;DATABASE=test_unidata;UID=sa;PWD=;DSN=test_unidata"

8 myws.BeginTrans
9 mydef.SQL = "INSERT INTO table1 (c1) VALUES ('George')"
10 mydef.Execute
11 Set myset2 = mydb.OpenRecordset("table1", dbOpenDynaset, dbSeeChanges)
12 myset2.MoveLast
13 MsgBox "" & myset2("c1")
14 myws.CommitTrans

Thousand Thanks in advance
George

 

[Sylvain Lafontaine]

Maybe you should commit your transaction before opening the second
recordset, as the synchronisation of linked tables to a SQL-Server doesn't
follow the same pattern as table located inside the Access database.
(Access doesn't open the transaction itself but ask SQL-Server to open one.
As this traffic is done over one or multiple connection, some of your
requests will be made on another connection, which means outside the opened
transaction.

The opening of a linked table ask for more than one connection: the first is
used to retrieve the primary keys and the other to retrieve others values
for each record.

Your code is very unusual and, unless you are an expert, you shouldn't mix
with transactions made by Access when dealing with a SQL-Server; otherwise
you will finish by hitting one or more insoluble bugs.

S. L.

 

[George]

Dear Sylvain,
I can not commit the transaction before opening the second
recordset because they have to be completed both.
(Stored procedure and Recordset).
If one of them fails then the other should not be executed.
I know...in my example, i am just showing some value with Msgbox,
from the recordset but in the real code i have to do several updates
and insertions to the same table and they are so complex that i can not use
other stored procedures.Only with recordsets.........

 

[Sylvain Lafontaine]

Using an open transaction outside a stored procedure and over a connection
from a linked table in Access is not going to be an easy task, in my humble
opinion. You are also using old technologie here.

Maybe opening yourself a direct connection to SQL-Server with OLEDB and
doing all of your stuff over this connection - instead of using ODBC and a
linked table - could give you a better chance of success. If you try this,
use the real OLEDB provider for SQL-Server, not the strange mix OLEDB/ODBC
which is called the Microsoft OLEDB provider (note: pure oledb providers are
without DSN, this latter being ODBC stuff). Full details here:
http://www.able-consulting.com/ADO_Conn.htm

Regards,
S. L.