Solved

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

Posted on 2008-10-23
6
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

617 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