TROUBLE EXECUTING INFORMIX STORED PROCEDURE

It looks like you forgot to set the .Connect property of the querydef. How can this code know what server you want to query from? You need to set the .Connect property before you set the .SQL property or Access will assume the query is not pass-through, and will generate an error when you set the SQL property if it can't understand the query. Patrick Dwyer wrote in message <3948B79C.871ACB77@ixpres.com>... >I am trying to execute an INFORMIX STORED PROCEDURE and return a value >from the procedure. I am opening the recordset with dbSQLPassThrough >set. >However, I am getting an error from ACCESS. (3129) > >Has anyone done this? > >Here's the code that I'm using: > >Dim wrk As Workspace > >Dim db As Database >Dim qdf As DAO.QueryDef >Dim rs_ser_num As DAO.Recordset >Dim rs_lock_table As DAO.Recordset >Dim new_ser_num As Integer >Dim wrk1 As Workspace > >Set db = CurrentDb >Set wrk1 = DBEngine(0) >Set qdf = db.CreateQueryDef("") >qdf.SQL = "EXECUTE PROCEDURE get_next_number()" >qdf.ReturnsRecords = True > >Set rs_ser_num = qdf.OpenRecordset(dbOpenDynaset, dbSQLPassThrough) > >Let new_ser_num = rs_ser_num.Fields("f1_ser") > >MsgBox "new_ser_num" & " " & new_ser_num > >Any Ideas? > >patrick >

I am trying to execute an INFORMIX STORED PROCEDURE and return a value from the procedure. I am opening the recordset with dbSQLPassThrough set. However, I am getting an error from ACCESS. (3129) Has anyone done this? Here's the code that I'm using: Dim wrk As Workspace Dim db As Database Dim qdf As DAO.QueryDef Dim rs_ser_num As DAO.Recordset Dim rs_lock_table As DAO.Recordset Dim new_ser_num As Integer Dim wrk1 As Workspace Set db = CurrentDb Set wrk1 = DBEngine(0) Set qdf = db.CreateQueryDef("") qdf.SQL = "EXECUTE PROCEDURE get_next_number()" qdf.ReturnsRecords = True Set rs_ser_num = qdf.OpenRecordset(dbOpenDynaset, dbSQLPassThrough) Let new_ser_num = rs_ser_num.Fields("f1_ser") MsgBox "new_ser_num" & " " & new_ser_num Any Ideas? patrick

That's what a pass-through query is for, to circumvent DAO and send SQL directly to the server that DAO would not understand (like a server stored procedure call). Having a linked table does not tell Access what DSN you want to use since you coould just as well have table links to several different servers. BTW: A pass-through query is identified by the fact that it has a value in its connection property. Otherwise, Access tries to process it through DAO. Patrick Dwyer wrote in message <39499AEC.ABC3EFFA@ixpres.com>... >The access database has a linked table that connects to informix already via >odbc. >Does it not already use this? I wonder if Mr. Microsoft is saying that >if I want to do dbSQLPassThrough, I need another connection that goes around >DAO? Does this second connection go around DAO. > >TIA >Patrick > >Steve Jorgensen wrote: > >> It looks like you forgot to set the .Connect property of the querydef. How >> can this code know what server you want to query from? You need to set the >> .Connect property before you set the .SQL property or Access will assume the >> query is not pass-through, and will generate an error when you set the SQL >> property if it can't understand the query. >> >> Patrick Dwyer wrote in message <3948B79C.871ACB77@ixpres.com>... >> >I am trying to execute an INFORMIX STORED PROCEDURE and return a value >> >from the procedure. I am opening the recordset with dbSQLPassThrough >> >set. >> >However, I am getting an error from ACCESS. (3129) >> > >> >Has anyone done this? >> > >> >Here's the code that I'm using: >> > >> >Dim wrk As Workspace >> > >> >Dim db As Database >> >Dim qdf As DAO.QueryDef >> >Dim rs_ser_num As DAO.Recordset >> >Dim rs_lock_table As DAO.Recordset >> >Dim new_ser_num As Integer >> >Dim wrk1 As Workspace >> > >> >Set db = CurrentDb >> >Set wrk1 = DBEngine(0) >> >Set qdf = db.CreateQueryDef("") >> >qdf.SQL = "EXECUTE PROCEDURE get_next_number()" >> >qdf.ReturnsRecords = True >> > >> >Set rs_ser_num = qdf.OpenRecordset(dbOpenDynaset, dbSQLPassThrough) >> > >> >Let new_ser_num = rs_ser_num.Fields("f1_ser") >> > >> >MsgBox "new_ser_num" & " " & new_ser_num >> > >> >Any Ideas? >> > >> >patrick >> > >

The access database has a linked table that connects to informix already via odbc. Does it not already use this? I wonder if Mr. Microsoft is saying that if I want to do dbSQLPassThrough, I need another connection that goes around DAO? Does this second connection go around DAO. TIA Patrick Steve Jorgensen wrote: > It looks like you forgot to set the .Connect property of the querydef. How > can this code know what server you want to query from? You need to set the > .Connect property before you set the .SQL property or Access will assume the > query is not pass-through, and will generate an error when you set the SQL > property if it can't understand the query. > > Patrick Dwyer wrote in message <3948B79C.871ACB77@ixpres.com>... > >I am trying to execute an INFORMIX STORED PROCEDURE and return a value > >from the procedure. I am opening the recordset with dbSQLPassThrough > >set. > >However, I am getting an error from ACCESS. (3129) > > > >Has anyone done this? > > > >Here's the code that I'm using: > > > >Dim wrk As Workspace > > > >Dim db As Database > >Dim qdf As DAO.QueryDef > >Dim rs_ser_num As DAO.Recordset > >Dim rs_lock_table As DAO.Recordset > >Dim new_ser_num As Integer > >Dim wrk1 As Workspace > > > >Set db = CurrentDb > >Set wrk1 = DBEngine(0) > >Set qdf = db.CreateQueryDef("") > >qdf.SQL = "EXECUTE PROCEDURE get_next_number()" > >qdf.ReturnsRecords = True > > > >Set rs_ser_num = qdf.OpenRecordset(dbOpenDynaset, dbSQLPassThrough) > > > >Let new_ser_num = rs_ser_num.Fields("f1_ser") > > > >MsgBox "new_ser_num" & " " & new_ser_num > > > >Any Ideas? > > > >patrick > >

The point of a pass through query is that it is passed through <g>. In other words Access just passes the SQL string to the server and let's the sever worry about it. From this you can see that it would be anomalous if Access checked to see if anything else was connected to the server or tried to interpret the SQL string in anyway. You have to tell Access which server/databse etc. it should pass the query string to and you do this in the Connect property of the querydef. Patrick Dwyer <paddydd@ixpres.com> wrote in message news:39499AEC.ABC3EFFA@ixpres.com... > The access database has a linked table that connects to informix already via > odbc. > Does it not already use this? I wonder if Mr. Microsoft is saying that > if I want to do dbSQLPassThrough, I need another connection that goes around > DAO? Does this second connection go around DAO. > > TIA > Patrick > > Steve Jorgensen wrote: > > > It looks like you forgot to set the .Connect property of the querydef. How > > can this code know what server you want to query from? You need to set the > > .Connect property before you set the .SQL property or Access will assume the > > query is not pass-through, and will generate an error when you set the SQL > > property if it can't understand the query. > > > > Patrick Dwyer wrote in message <3948B79C.871ACB77@ixpres.com>... > > >I am trying to execute an INFORMIX STORED PROCEDURE and return a value > > >from the procedure. I am opening the recordset with dbSQLPassThrough > > >set. > > >However, I am getting an error from ACCESS. (3129) > > > > > >Has anyone done this? > > > > > >Here's the code that I'm using: > > > > > >Dim wrk As Workspace > > > > > >Dim db As Database > > >Dim qdf As DAO.QueryDef > > >Dim rs_ser_num As DAO.Recordset > > >Dim rs_lock_table As DAO.Recordset > > >Dim new_ser_num As Integer > > >Dim wrk1 As Workspace > > > > > >Set db = CurrentDb > > >Set wrk1 = DBEngine(0) > > >Set qdf = db.CreateQueryDef("") > > >qdf.SQL = "EXECUTE PROCEDURE get_next_number()" > > >qdf.ReturnsRecords = True > > > > > >Set rs_ser_num = qdf.OpenRecordset(dbOpenDynaset, dbSQLPassThrough) > > > > > >Let new_ser_num = rs_ser_num.Fields("f1_ser") > > > > > >MsgBox "new_ser_num" & " " & new_ser_num > > > > > >Any Ideas? > > > > > >patrick > > > >