Solved

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

Posted on 2007-03-21
7
1,250 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
[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
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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