Solved

MSAccess2003 ODBCDirect call should return recordset but always get -1 recordcount

Posted on 2008-10-23
6
389 Views
Last Modified: 2011-09-20
I am attempting to use MS Access 2003 to execute a ODBCDirect call to a stored proc on a DB2 back end running on AIX.  

My environment is:
Windows XP Pro Sp2
MS Office Access 2003 SP3
MS DAO 3.6
IBM DB2 ODBC DRIVER Version 8.01.14.292 configured as a System DSN

I have included two function examples of my code (test code so pls no comments on coding ettiquite!)

The DB2 Stored Proc in the following examples simply takes as input a single parameter and echoes back that parameter value as a multie record recordset containing a single field.
 
The first example is the function ParamSPT, which has the input to the stored proc coded directly into the SQL call (e.g., the generated SQL is "{ call prc_test( 'abc123' ) }".  The result of my OpenRecordset() method is as expected, I am able to see that my Recordset has multple records in it.

However, The DB2 engineer wants to, in addition to the recordset return, pass back output parameters and MS Access does not seem to want to allow me to configure anything like { call prc_test2('abc123', ?, ?, ?) } as it generates an exception stating I have given an incorrect number of arguments.  More on this in a moment.

The second function I have created, ParamSPTODBC, uses ODBCDirect to create the Query Def.  I (more appropriately) define the signature of the stored proc call, then specify what each of the parameters are.  In the code given in the example, I have only one input parameter.  The OpenRecordset() command completes successfully, however, I never receive back the Recordset (or don't appear to).  While MyRs.RecordCount = -1, MyRS.EOF and .BOF are both FALSE.  Calling MyRs.MoveNext results in a "Runtime Error 3219 - Invalid Operation".

Interestingly enough, I am able to configure both input and output paramters, and I do see the return values for those when I call the prc_test2 stored proc.

So!  I am at an impasse.  I would prefer to call the stored procs in the standard way using the methodology that the DB2 Engineer would like me to use (having both input and output params) but am unable to do that and also retrieve recordsets.

Any help you can provide here is greatly appreciated!
Function ParamSPT(strVal As String)

    

    Dim MyDb As Database, MyQry As QueryDef, MyRS As Recordset

    Set MyDb = CurrentDb()

    Set MyQry = MyDb.CreateQueryDef("")

    

    ' Type a connect string using the appropriate values for your

    ' server.

    MyQry.Connect = "ODBC;DSN=<snip>;UID=<snip>;PWD=<snip>;DBALIAS=<snip>;PATCH1=131072;LOBMAXCOLUMNSIZE=1048575;LONGDATACOMPAT=1;"

    

    ' Set the SQL property and concatenate the variables.

    MyQry.sql = "{ call ttndbo.prc_test ( '" & strVal & "' ) }"

    

    MyQry.ReturnsRecords = True

    

    Set MyRS = MyQry.OpenRecordset()

    MyRS.MoveLast

    Debug.Print MyRS.RecordCount

        

    MyQry.close

    MyRS.close

    MyDb.close

    

End Function
 
 
 

Function ParamSPTODBC(strVal As String)

    

    Dim wrkMain As Workspace, MyQry As QueryDef, MyRS As Recordset

    Dim conMain As Connection

    Dim strSql As String

         

    ' Create ODBC workspace and open a connection

    Set wrkMain = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)

    

    Set conMain = wrkMain.OpenConnection("", dbDriverComplete, True, "ODBC;DSN=<snip>;UID=<snip>;PWD=<snip>;DBALIAS=<snip>;PATCH1=131072;LOBMAXCOLUMNSIZE=1048575;LONGDATACOMPAT=1;")

    

    ' Set SQL string to call the stored procedure

    strSql = "{ call ttndbo.prc_test ( ? ) }"
 

    Set MyQry = conMain.CreateQueryDef("", strSql)

    

    With MyQry

        ' Indicate that the two query parameters will only

        ' pass information to the stored procedure.

        .Parameters(0).Direction = dbParamInput

        

        ' Assign initial parameter values.

        .Parameters(0) = strVal

        

        Set MyRS = .OpenRecordset()

        

        Debug.Print MyRS.RecordCount

        MyRS.MoveLast   ' <-- Generic Error as MyRs.RecordCount = -1

        Debug.Print MyRS.RecordCount

        MyRS.MoveFirst

        Debug.Print .Fields(0).Name & " = " & .Fields(0)

        

    End With

    

    MyRS.close

    MyQry.close

    conMain.close

    wrkMain.close

    

End Function

Open in new window

0
Comment
Question by:MAGRATTON
  • 3
  • 3
6 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 250 total points
ID: 22788763
maybe this sample will help you
http://www.experts-exchange.com/Database/DB2/Q_20951083.html
i didn't understand what happened when you tried using multiple parameters, which error did you receive?
0
 

Author Comment

by:MAGRATTON
ID: 22788819
Reviewing your link in a moment.  

To answer your question, if I specify the call in my function ParamSPT as

{call prc_test2('abc213", ?, ?, ?) }  

instead of {call prc_test('abc123') }

I get a generic error ("ODBC -- call failed.").  Using the query editor in MS Access I get a slightly more meaningful error ("OCBD -- Call Failed.  [IBM][CLI Driver] CLI0100E Wrong number of paramters.  SQLSTATE=07001 (#-99999)")

In the second method, I am able to specify any number of input and output paramters, receive results for them, but do not receive back the expected recordset.
0
 

Accepted Solution

by:
MAGRATTON earned 0 total points
ID: 22789534
OKAY...
I noticed in your linked example that they were using ADO, whereas I am using DAO.

So I coded a new version of my function using ADO.  Executed the function with anticipation of success and ....  !!  same result.  ResultCount = -1.  No Records returned.

For some reason ( I cannot recall why, but think it was due to an "invalid operation" error I had received on the previous pass through the function) in the next pass through the function I changed "myRs.MoveLast" to "myRs.MoveNext" as I was stepping through the code.  Much to my surprise... IT DID!?!

Huh? are there records there?  In my immediate window -> ?MyRs.Fields(0) <enter> and out pops the expected return value.   I check the .RecordCount again and still -1.  Tried .MoveNext again and it worked, etc. all the way to the end of the expected return result.  In other words, I got back all of the expected records!

Went back to my original DAO version of the code and Surprise! Surprise!, it works exactly the same as the ADODB version.  RecordCount = -1, but the records are actually there if I .MoveNext through them.  BTW: the .EOF method returns TRUE and FALSE expectedly, so even through you do not initially know how many records are returned, you can still iterate through the recordset result until .EOF = TRUE.

So, where are we:
- DAO and ADO both work, though RecordCount always seems to = -1.  
- MoveFirst and MoveLast.. frankly anything other than MoveNext will not work.  You must MoveNext through the returned RecordSet.

Outstanding issue (if anyone feels like commenting)
- ResultSets are ReadOnly.  If I attach them to a MS Access Form, I will not (likely) be able to edit.
- Is there a way to have the ResultSet based off of a Dynamic Cursor, that I can move around in (first, last, next, prev, etc.)

Thanks for you help!
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22793804
i believe the same reason causes the result set to be read only and to the resultcount property to be -1
this is caused since you don't work with a disconnectede result set (which might be the default with other database vendors) which means that the driver does not pull all the results from the database to a local cache,
every time you perform moveNext you are accessing the database, so infact, the driver can't know how many records were returned and that is why resultCount=-1
0
 

Author Comment

by:MAGRATTON
ID: 22795519
So, how do I process this as a disconnected result set?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22803825
i'm not sure that you can
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
Multiple queries for a form 14 22
Set Listbox Selected Items by Bound Column 6 13
select over clause 1 0
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now