Solved

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

Posted on 2007-03-21
7
1,200 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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 …

785 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