Use Stored Proc As Recordset For Listbox

[jaydwest]

I'm working on and Access 2003 front end with a SQL Server Back End. I would like to take advantage of the speed of Stored Procs to populate a list box on a form. Here's the code I'm trying to use.

Set cmd = MakeStoredProc("StoredProcName")
Set prm1 = cmd.CreateParameter("ParamName", adInteger, adParamInput, , Me![ID])
cmd.Parameters.Append prm1
rstSource.CursorLocation = adUseClient
rstSource.CursorType = adOpenKeyset
rstSource.LockType = adLockOptimistic
Set rstSource = cmd.Execute

Me![ListBox].Recordset = rstSource


Access doesn't like this. What is wrong. I get the message "Object doesn't support the property or method"

Thanks for your help.

 

[Pat Hartman]

I don't know if you can assign a recordset this way. It sounds like you can't unless you convert to an .adb (which I would NOT recommend for a lot of reasons).

Depending on what the stored procedure does, a standard Access query against a linked table may not take any longer. I rarely use stored procedures and I don't have any timing issues. Just make sure that your queries contain selection criteria where ever possible to reduce the number of rows returned by the server and you'll be just fine.

 

[jaydwest]

Pat,

In reading some of your recent responses, I have noted that you feel that queries on the front end Access database are not significantly slower than Stored Procs running on the SQL Server Backend.

Over the years, I have developed many Access Frontend with SQL Server back ends and the performance difference has been very dramatic. In the best case ( or worst case) senario querying a table with 140,000 records over a communications line, the Query version took 2.5 minutes, the Stored Proc took 5 seconds. This was with a MS Access 20000 database and SQL Server 2000.

Have things changed in MS Access 2002 +?

Thanks

 

[jaydwest]

Pat,

I know I couldn't get the recordset property to work, but the following article seems to state that it is doable.


http://msdn.microsoft.com/library/d...us/vbaac11/html/acproRecordset_HV05251093.asp

Any thoughts?

 

[RoyVidar]

Have you tried

set Me![ListBox].Recordset = rstSource

 

[RoyVidar]

Sorry, I forgot, when using set rs = cmd.execute, you reinstantiate the recordset to a readonly, forwardonly recordset, which will probably bomb. Try

Set cmd = MakeStoredProc("StoredProcName")
Set prm1 = cmd.CreateParameter("ParamName", adInteger, adParamInput, , Me![ID])
cmd.Parameters.Append prm1
set rstSource = new adodb.recordset
rstSource.open cmd,,adopenstatic, adopenforwardonly

set Me![ListBox].Recordset = rstSource

 

[Pat Hartman]

In reading some of your recent responses, I have noted that you feel that queries on the front end Access database are not significantly slower than Stored Procs running on the SQL Server Backend.

It depends on what the Stored procedure is doing. Jet attempts to "pass through" all queries against ODBC data sources. If you do something in the Jet query that can't be translated by the ODBC driver, Jet will need to request all rows from the ODBC table and will process the query locally. Obviously if that is happening, the jet query will be slower. But baring that, the difference should simply be one of overhead - ie the difference between running static and dynamic SQL. The size of the recordset is immaterial since it would be constant for both queries. The stored procedure is static since it is "compiled" on the server when it is saved and an execution plan is calculated and stored. The query passed by Jet is the equivalant of dynamic SQL created in code. It needs to be "compiled" and then run so the "compile" time is overhead.

Most of my back end databases have been Oracle and DB2. I've only had to use stored procedures for a few queries with complicated criteria and many joins. If I had to guess, I would say that less than 1% of my queries need to be stored procedures. Sometimes making a view solves the problem.

Interestingly, subselects are less efficient in many cases when the tables are Jet so I tend to break queries up into multiple pieces to better control how they are processed but with ODBC data sources subselects are usually better.

 

[jaydwest]

I have tried every permutation of code to get this to work but it doesn't seem to like it. Here is the latest.


Set cmd = New ADODB.Command
cmd.CommandText = "{ Stored Proce Name(" & Me![Parameter1] & ", " & Me![Parameter2] & ") }"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cnn
Set rstSource = New ADODB.Recordset
rstSource.CursorLocation = adUseClient
rstSource.Open cmd, , adOpenForwardOnly, adLockOptimistic, dbSeeChanges

Me![ListBox].Recordset = rstSource

The error message is "Table does not exist." It doesn't seem to be able to parse the Stored Proc and create the necessary command text.

Any suggestions?

Thanks,

 

[RoyVidar]

This doesn't seem to be a matter of assigning a recordset to a listbox, but getting a recordset from a stored procedure? Or am I misunderstanding?

I use the following syntax (with a typo, disclaimer, that is)

Set cmd = new adodb.command
with cmd

    .activeconnection = cnn
    .commandtype = adcmdstoredproc
    .commandtext = "sp_NameOfSP"

    Set prm1 = cmd.CreateParameter("@ParamName1", adInteger, adParamInput)
    cmd.Parameters.Append prm1
    prm1.value = Me!Parameter1.value

    Set prm2 = cmd.CreateParameter("@ParamName2", adInteger, adParamInput)
    cmd.Parameters.Append prm2
    prm2.value = Me!Parameter2.value

end with

set rstSource = new adodb.recordset
with rstsource

    .cursorlocation = aduseclient
    .cursortype = adopenstatic
    .locktype = adlockreadonly
    .open cmd

end with

debug.print rstSource.getstring
' set Me![ListBox].Recordset = rstSource

Where @ParamName1 and 2 is defined as parameters in the SP with the same name and datatype, and the controls contain a value

First - ensure you get a recordset, use your original code, and do a

debug.print rstSource.getstring

Does it print a recordset to the immediate pane (ctrl+g)? Is the connection OK? Do you need owner prefix on the SP name? ...

When you get a recordset, try altering the lock/cursor types. It should normally work with those settings.

 

[jaydwest]

For Pat Hartman,

Per our emails yesterday, my experience is that Stored Procs are significantly faster than queries. After your email, I didn't sleep all night. So when I got back to my computer this morning, I thought I would do a benchmark to compare speeds. I created 2 procs, the first uses a Stored Proc to get a record of Employee Data based on the Employee Table Primary Key. The second does the same thing with an Access Query on the form. To make things relatively equal I used a parameter Query. Below you'll find my code.

CODE WITH STORED PROC

Dim rstTest As DAO.Recordset
Dim rstTest2 As New ADODB.Recordset

Dim lngEmployeeID As Long

Dim timStart As Date
Dim timEnd As Date

Dim prmEmployeeID As Variant

Set dbsUser = CurrentDb()
Set rstTest = dbsUser.OpenRecordset("Employee", dbOpenDynaset, dbSeeChanges)

timStart = Now()
DoCmd.Hourglass True
Do Until rstTest.EOF
lngEmployeeID = rstTest!EmployeeID

Set cmd = MakeStoredProc("sp_Employee_GetRec_ForEmployeeID")
Set prmEmployeeID = cmd.CreateParameter("EmployeeID", adInteger, adParamInput, , lngEmployeeID)
cmd.Parameters.Append prmEmployeeID
Set rstTest2 = cmd.Execute
rstTest.MoveNext
Loop
timEnd = Now
DoCmd.Hourglass False

MsgBox "Total Seconds = " & DateDiff("s", timStart, timEnd)

CODE WITH PARAMETER QUERY


Dim rstTest As DAO.Recordset
Dim rstTest2 As DAO.Recordset

Dim lngEmployeeID As Long

Dim timStart As Date
Dim timEnd As Date

Dim prmEmployeeID As Variant

Set dbsUser = CurrentDb()
Set rstTest = dbsUser.OpenRecordset("Employee", , dbSeeChanges)

timStart = Now()
DoCmd.Hourglass True
Do Until rstTest.EOF
lngEmployeeID = rstTest!EmployeeID

Set qry = dbsUser.QueryDefs("Employee_GetRec_ForEmployeeID_SelQry")
qry.Parameters("prmEmployeeID") = lngEmployeeID
Set rstTest2 = qry.OpenRecordset(adOpenDynamic, dbSeeChanges)
rstTest.MoveNext

Loop
timEnd = Now
DoCmd.Hourglass False
MsgBox "Total Seconds = " & DateDiff("s", timStart, timEnd)


The Employee Table has 6,709 records. Here are the results

Using Parameter Query = 247 seconds
Using Stored Proc = 114 seconds

Please let me know if there are some errors in my coding or methodology. According to your response, I believe this is a Query that should be able to be passed through to SQL Server.

 

[jaydwest]

For RoyVidar,

I tried the code block you sent and I was hoping and praying that it would work. I even offered by first born child to the stored proc gods. It looked so promising but then when it tries to set the List Box's recordset property I get the error message:

Object doesn't support this property or method

Are you actually using this capability or is it just another of Microsoft's coding myths (known here as MCMs)? I checked the getstring and it is correct.

If you have any suggestions, let me know.

Thanks,

Resigned to Queries Forever! (AKA Jay)

 

[RoyVidar]

"Are you actually using this capability" - not in production, but I've tested it, and the below code, is copy/pasted from form load in one of my testdbs (just removed some unrelated stuff), and it does work.

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=sqloledb;" & _
            "Data Source=(local);" & _
            "Initial Catalog=NYBC_MSDE;" & _
            "Integrated Security=SSPI"
    cn.Open

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = "dbo.sp_TestDeltakere"
        .CommandType = adCmdStoredProc
        Set prm = .CreateParameter("@MyStart", adInteger, adParamInput)
        .Parameters.Append prm
        prm.Value = 5
    End With

    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open cmd
    End With
    Set Me!lstSok.Recordset = rs

The following is the SP

CREATE PROCEDURE dbo.sp_TestDeltakere 
     (@MyStart int)
AS
     Select startnr, fornavn + ' ' + etternavn
     from dbo.tblDeltaker
     where startnr > @MyStart
GO

I've seen more than once problems with these attempts, though, locktype, cursor are among the possible culprits, perhaps also MDAC version, not that I've bothered to test all of them, but it worked both with 2.1 and 2.7. Which version are you using? I'm testing on 2002 (xp). One of the reasons I haven't put anything like this in production, is that my customers have the rather annoying habit of demanding that it works in 2000-2003 versions, and the recordset property of combos/lists (as with the .AddItem/.RemoveItem methods), as far as I know, was introduced in the 2002 version...

 

[jaydwest]

Almost

For Roy Vidar,

Thanks for all your help with getting a listbox's recordset from a stored procedure. I finally got back to this issue and was able to get it working. Thanks again.

I also tried to set a form's recordset to a stored proc. This worked great and is blazingly fast. There's only two problems (so far)

1) Recordsetclone of the form produces a ADODB recordset on which you cannot execute a findfirst method.

2) Cannot seem to use the frm.OrderBy method.

I have tried different cursors including adopendynamic and adopenkeyset.

Any suggestions.

 

[RoyVidar]

Use the .Find method of ADO recordset (or for multicolumn find, the .Filter property).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima00/html/find.asp

For ordinary sort on ADO recordsets, you'd use the .Sort property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprosortpropertyado.asp

I'm just not sure if it'l work, and I haven't the time currently to do any testing, I'm afraid. I think the .Sort property requires a client side cursor, while ADO form recordset, I think need server side cursor (not sure though).

I have somewhere an SP accepting column number and sort order ('ASC'/'DESC') as parameters, returning a recordset ordererd by the selected column. Let me know if that's interesting, though it may take a day or two before I'm back.

 

[mrhutch]

thanx

Hey guys, thanx a load for this.. I was getting the same issue, and had actually never managed to get VBA to speak to a SP before... Good work