stored procedure and recordset

[George]

Hi to everybody....(sorry fore the code example...i assume that it will make
understanding easier)
I have an application in Access97 wich is connected
via ODBC to a SQL-Server 7.0 Database (the tables are linked)
My problem is that if i make a call to a stored procedure (to add f.e some
data to a table)
within a WorkSpace and then trying to open a recordset,to the same
table,within the same workspace,
i get the message (after 60sec) "ODBC-Call failed".(I know that 60 sec are
the ODBC time out.)
The same 'thing' works perfect IF the database is a standard .mdb file.
My question is:
Why can't i open a recordset to a table, when few lines before,
a stored procedure is called to make some changes to the same table
(always within the same Workspace) when i am connected via ODBC to the
Database ???
Is there any way to resolve this?
Example Code:
============================================================================
===============================
On Error GoTo errZ
Dim myws As Workspace, mydb As Database, mydef As QueryDef
Dim myset1 As Recordset, myset2 As Recordset, mysql As String
Set myws = DBEngine.Workspaces(0)
myws.BeginTrans
mysql = "sp_insert_TABLE1 'George'"
Set mydb = CurrentDb()
Set mydef = mydb.CreateQueryDef("")
mydef.ReturnsRecords = True
mydef.Connect = "ODBC;DATABASE=unidata;UID=sa;PWD=;DSN=unidata"
mydef.SQL = mysql
Set myset1 = mydef.OpenRecordset() 'execute the stored
procedure

Set myset2 = mydb.OpenRecordset("table1", dbOpenDynaset,
dbSeeChanges) '**here the code fails**

myws.CommitTrans

Exit Sub
errZ_nxt:
myws.Rollback
Exit Sub
errZ:
If Err = 3325 Then 'Error handling of ...."Pass_through Query did not
return any records...."
Resume Next
Else
MsgBox Error$ & Err
Resume errZ_nxt
End If
============================================================================
===============================
I appreciate any help......
Many Thanks in advance!!!
George!

 

[Vadim Rapp]

G> Hi to everybody....(sorry fore the code example...i
G> assume that it will make understanding easier)
G> I have an application in Access97 wich is connected
G> via ODBC to a SQL-Server 7.0 Database (the tables
G> are linked) My problem is that if i make a call to
G> a stored procedure (to add f.e some data to a
G> table) within a WorkSpace and then trying to open a
G> recordset,to the same table,within the same
G> workspace, i get the message (after 60sec)
G> "ODBC-Call failed".(I know that 60 sec are the ODBC
G> time out.) The same 'thing' works perfect IF the
G> database is a standard .mdb file. My question is:
G> Why can't i open a recordset to a table, when few
G> lines before, a stored procedure is called to make
G> some changes to the same table (always within the
G> same Workspace) when i am connected via ODBC to the
G> Database ???
G> Is there any way to resolve this?

1. The workspace is not the same. You create mydef from currentdb indeed,
but it's empty and has no reference to the workspace (open Locals in VBE and
explore the querydef - you will see no references to the database or
workspace). Then you specify separate connect string, making your query
pass-through and independent from currentdb.When you execute the query,
Access opens up new separate ODBC connection. Therefore, myset1 and myset2
are on different connections and block each other.


Since I don't know what's in your sp_insert stored procedure (which appears
to insert records and returns records at the same time, so it's something
non-trivial), here's an example of what might work:

mysql = "insert into table1(c1) values('george')"
Set mydb = CurrentDb()
Set mydef = New QueryDef
mydef.Name = "Def1"
mydb.QueryDefs.Append mydef
mydef.ReturnsRecords = False
mydef.SQL = mysql
myws.BeginTrans
mydef.Execute
Set myset2 = CurrentDb.OpenRecordset("table1", dbOpenDynaset,
dbSeeChanges)
myws.CommitTrans


or, easier,

CurrentDb.Execute "insert into table1(c1) values('george')"
Set myset2 = CurrentDb.OpenRecordset("table1", dbOpenDynaset,
dbSeeChanges)


2. This is wrong newsgroup for your question: it's about Access Projects -
ADP's - straightforward like Notepad, no engines, no workspaces, no
transactions, no querydefs. You are using MDB with ODBC. I think the most
appropriate group for your question is
microsoft.public.access.odbcclientsvr.


Vadim

 

[George]

Many thanks.........
I'll try and inform you about the results......
Thanks again.......

 

[George]

You were 100% right !!!
Thank you very very much !!!
I didn't see that myset1 and myset2 were just like 2 different
connections that block each other.....
Many thanks again!!!