Solved

Is there anyway of setting a combobox recordsource to an adodb recordset?

Posted on 2007-03-21
7
1,211 Views
Last Modified: 2013-11-27
I am using access 2003 and linking to SQL Server 2000 via linked tables using OLEDB datasources.
I am finding that combo box population is very slow on large datasets. I suspect that this is because access is dragging the table over and processing it locally.
Is there anyway of setting a combobox recordsource to an adodb recordset?

ADO retrieval of the data is ligntning fast so I was hoping to be able to do something like
myCombobox.recordset = new adodb.recordset
myCombobox.recordset = rst                    'where rst is a previously populated ado recordset.

but access complains that the combobox recordset isn't a proper ado one
Thanks for your help.
Andy
0
Comment
Question by:ieg
  • 3
  • 3
7 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 18763486
I *think* you need to set the recordset locks and cursors differently ...

For a form you need to set cursor to be client

Private Sub cmdBind_Click()
    Set rst = New ADODB.Recordset
    rst.Open Source:="SELECT * FROM tblTeam", _
             ActiveConnection:=CurrentProject.Connection, _
             CursorType:=adOpenKeyset, _
             LockType:=adLockOptimistic
    Set Me.Recordset = rst
End Sub
0
 

Author Comment

by:ieg
ID: 18764633
Steve,
Thanks for coming back so quickly.
I have tried your code and a number of variations.
The code I currently have is this
--------------------------------------------------
Private Sub btnPopulate_Click()
Dim rst As ADODB.Recordset
Dim strsql As String
strsql = "select CallNumber from Calls"
ConnectToSupportDatabase 'sets global varfiable g_objcon
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.ActiveConnection = g_objCon
rst.Open Source:=strsql, _
         CursorType:=adOpenKeyset, _
         LockType:=adLockOptimistic
Set cmbCalls.Recordset = New ADODB.Recordset
cmbCalls.Recordset = rst
End Sub
----------------------------------------------------------------
This creates an error
"The object you entered is not a valid Recordset property"
and the debug stops as the Set cmbCalls.Recordset = New ADODB.Recordset line

If I comment the line out then I get the error
"Object variable or With block variable not set"
and the debug stops at the cmbCalls.Recordset = rst line

Any ideas?
Andy

0
 
LVL 39

Expert Comment

by:stevbe
ID: 18765475
what version of ADODB are you using?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:ieg
ID: 18765509
Microsoft Active X Data Objects 2.8 Library
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 18765563
you don;t need to Set = New for the combobox, just Set = rst

--------------------------------------------------
Private Sub btnPopulate_Click()
Dim rst As ADODB.Recordset
Dim strsql As String
strsql = "select CallNumber from Calls"
ConnectToSupportDatabase 'sets global varfiable g_objcon
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.ActiveConnection = g_objCon
rst.Open Source:=strsql, _
         CursorType:=adOpenKeyset, _
         LockType:=adLockOptimistic
Set cmbCalls.Recordset = rst
End Sub
----------------------------------------------------------------
0
 

Author Comment

by:ieg
ID: 18765858
Steve,
That worked a treat many thanks

Andy
0
 

Expert Comment

by:PauL-J
ID: 23537161
I tried using this code in an Access 2007 data project. It works except the data does not appear in the combo box drop down list. Is there a change to display the result set in 2007?
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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