Microsoft Access
--
Questions
--
Followers
Top Experts
Is there anyway of setting a combobox recordsource to an adodb recordset?
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
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
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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:=CurrentP
       CursorType:=adOpenKeyset, _
       LockType:=adLockOptimistic
  Set Me.Recordset = rst
End Sub
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:=CurrentP
       CursorType:=adOpenKeyset, _
       LockType:=adLockOptimistic
  Set Me.Recordset = rst
End Sub
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
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
what version of ADODB are you using?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Microsoft Active X Data Objects 2.8 Library
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Steve,
That worked a treat many thanks
Andy
That worked a treat many thanks
Andy
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?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Microsoft Access
--
Questions
--
Followers
Top Experts
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.