Solved

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

Posted on 2007-03-21
7
1,178 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now