Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1599
  • Last Modified:

Set listbox.recordset to ADODB Recordset from Sybase Stored Proc

Having a bit of trouble getting something to work that "apparently" according to Microsoft should work.

With AC2002 you can now fill a list box using VBA code and the recordset property.

Fantastic I can fill all those list boxes without having to write a table.

Basically I create an ADODB connection (ADO 2.6) and connect to a sybase server (12.5) and run a stored procedure which returns a recordset.

At this point all is well, so I take said recordset and try to set it to a list box and access doesn't like it.

    Dim ADO_Conn As New ADODB.Connection
    Dim ADO_Cmd As New ADODB.Command
    Dim ADO_Rst As ADODB.Recordset
    ADO_Conn.ConnectionString = "Driver={SYBASE SYSTEM 11};Srvr=ServerName;Uid=user;Pwd=password"
    ADO_Conn.Open
    ADO_Cmd.ActiveConnection = ADO_Conn
    ADO_Cmd.CommandText = "SelectClient"
    ADO_Cmd.CommandType = adCmdStoredProc
    ADO_Cmd.Parameters.Refresh
    ADO_Cmd.Parameters.Append ADO_Cmd.CreateParameter("org_id", adInteger, adParamInput, 4, RapportIDClient)
    Set ADO_Rst = ADO_Cmd.Execute
    Set ListBox.recordset = ADO_Rst


Last line blows out with

Error '7965' The object you entered is not a valid recordset property.

Now I've tried playing around with the recordset cursor type, lock type and cursor location to no luck.

I know setting the list box works if I chuck a DAO recordset at it.

I know I can write the recordset to a local table and set the listbox to that instead (which works but is not the solution we want as it's too slow)

So a bit stuck and hoping that someone can confirm this works with a SYBASE server or at least can confirm MS SQL works when you use that.

Stuck in ADO limbo....


0
fabalou
Asked:
fabalou
  • 7
  • 6
1 Solution
 
zuijdhoekCommented:
Maybe the GetString method offers you a usefull workaround:
...
    Set ADO_Rst = ADO_Cmd.Execute
    Set ListBox.RowSource = ADO_Rst.GetString(ColumnDelimeter:=";", RowDelimeter:=";")
...

You have to change the RowSourceType property of the listbox to 'Value List'

Good luck,

Mark

0
 
fabalouAuthor Commented:
zuij  :  Won't work the recordset as a list is way over the maximum for the value list property. Thanks for the suggestion anyway.

Oh and we tried using additem and that seems to be about as slow as can be possible!!
0
 
stevbeCommented:
here is code I am using in an application ... you need to make sure to get the recordset arguments set correctly, I *think* the CursorType is the one that really matters. Change the properties before you Set = cmd.Execute

    'initialize the club list recordset
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open Source:=ClubListSQL, _
             ActiveConnection:=ECEConn, _
             CursorType:=adOpenStatic, _
             LockType:=adLockReadOnly, _
             Options:=adCmdText
   
    'bind the recordset to the club list listbox
    Set Me.lstClub.Recordset = rst
    Me.lstClub.Value = Me.lstClub.ItemData(0)

Steve
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
fabalouAuthor Commented:
Based on your code I tried

ADO_Rst.CursorLocation = adUseClient
ADO_Rst.CursorType = adOpenStatic
ADO_Rst.LockType = adLockReadOnly

still the same error message. I'm wondering if this just doesn't work with sybase!! I've not seen a confirmed example working.
0
 
stevbeCommented:
hmmm ... each OLEDB provider will return *something* not always what you ask for ... here is the complete error description ...

?AccessError(7965)
The object you entered is not a valid Recordset property.@For example, you may have used a forward-only recordset, or tried to set it to null.@@1@607393@1


I think this is saying it is the CursorType, see what sybase is returning and try all of the diifferent types to see if you can get anything other than Read-Only. Somtimes you can get different CursorTypes depending on the CursorLocation. I know this is not a definative answer but I do not have sybase to test against and hopefully it points you in the correct direction to figure out how/if it can be done with sybase.

ADO_Rst.CursorLocation = adUseClient
ADO_Rst.CursorType = adOpenStatic
ADO_Rst.LockType = adLockReadOnly
Set ADO_Rst = ADO_Cmd.Execute
Set ListBox.recordset = ADO_Rst
    Msgbox "CType= " & ADO_Rst.CursorType
    Msgbox "CLocation= " & ADO_Rst.CursorType
    Msgbox "Lock= " & ADO_Rst.CursorType

Steve
0
 
fabalouAuthor Commented:
Steve

ADO_Rst.CursorType is returning 0 no matter what I set it to. So it appears sybase / ADO is returning a forward only recordset irrespective of what I ask for.

Not entirely sure what this means

Anyone got a clue what parameters I can set in the execute method, that may affect this?
0
 
fabalouAuthor Commented:
Just as a side thought can I script through the ADO recordset and write it directly to a DAO recordset (without writing it to a table or file) That would probably solve my problem. Us writing it to the table is taking too long and causes a few seconds of pause for each time (which all adds up to a very unresponsive application)
0
 
stevbeCommented:
If the sybase OLEDB provider does not supply anything other than a Read-Only recordset then you will have to resort to another process.

Have you tried writing the values to a table in the local copy of the app instead of to the database that lives on the network?

If you want to use an in-memory recordset you will have to use ADO because you can not create a DAO recordset in memory only. You will still have to create/define the ADO recordset object including fields and their properties and then loop through the sybase recordset object to populate the ado recordset.

Need some code for ADO in-memory?

Steve
0
 
stevbeCommented:
this should get you started ...

    'create blank recordset for storing takeoff items
    Set rstADO = New ADODB.Recordset
    rstADO.CursorType = adOpenStatic
    rstADO.CursorLocation = adUseClient
    rstADO.LockType = adLockOptimistic

    rstADO.Fields.Append "TakeoffNo", adVarChar, 255
    rstADO.Fields.Append "ItemNo", adVarChar, 255
    rstADO.Fields.Append "ItemRev", adVarChar, 255
    rstADO.Fields.Append "ItemQty", adInteger
    rstADO.Fields.Append "ItemType", adVarChar, 255
    rstADO.Open
   
    'add items to recordset
    Do While Not(rstSYB.EOF)
        rstADO.AddNew
        rstADO.Fields("ItemNo").Value = rstSYB.Fields("ItemNo").Value
        rstADO.Fields("ItemRev").Value = rstSYB.Fields("ItemRev").Value
        rstADO.Fields("ItemQty").Value = rstSYB.Fields("ItemQty").Value
        rstADO.Fields("ItemType").Value = rstSYB.Fields("ItemType").Value
        rstADO.Update
    Loop
0
 
stevbeCommented:
oops ... forgot the last rstSYB.MoveNext just before the Loop ... I HATE infinite loops :-)

Steve
0
 
fabalouAuthor Commented:
I was halfway through testing it when I had to shoot out the office. The "memory recordset" had bound to the listbox ok and was displaying reocrds but they were blank, so I think we are there bar a bit of formatting...will update in the morning when I'm sure.

Thanks for the help so far.
0
 
stevbeCommented:
sounds like column count and widths ... post back when you get it working ... how is performance?
0
 
fabalouAuthor Commented:
Yay finally got there....needed to add the cursortype as static (why would that mean the list box was blank)

Final code

Public Sub FillList(Lbox As ListBox, rst As ADODB.Recordset, Optional ColumnCount As Long = 2)
    'Takes a sybase ADO recordset and applies it too a two column list box (typically ID and a text field)
    Dim x As Long
    Dim rstNEW As New ADODB.Recordset
    Lbox.RowSource = ""
    Lbox.Requery
    Dim fld As ADODB.Field
    Dim newfld As New DAO.Field
    Set rstNEW = New ADODB.Recordset
    Set rstNEW.ActiveConnection = Nothing
    rstNEW.CursorLocation = adUseClient
    rstNEW.LockType = adLockOptimistic
    rstNEW.CursorType = adOpenStatic
    'recreate the columns
    For x = 1 To ColumnCount
        rstNEW.Fields.Append rst.Fields(x - 1).Name, rst.Fields(x - 1).Type, rst.Fields(x - 1).DefinedSize
    Next
    rstNEW.Open
    Do While Not rst.EOF
            rstNEW.AddNew
            For x = 1 To ColumnCount
                rstNEW.Fields(x - 1).Value = rst.Fields(x - 1).Value
            Next
            'rstnew.Update
        rst.MoveNext
    Loop
    Set Lbox.Recordset = rstNEW
    rst.Close
   
err_skip:
   
End Sub
0
 
stevbeCommented:
Glad to hear you have it working.

<why would that mean the list box was blank>
    Not sure, I do know that I have run into that myself and only know that adOpenStatic is the *magic* setting.

Steve
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now