Unanswered: Populate Combobox from ADODB Recordset
I have an access project that is set up on a database located on an SQL 2000 Server. It all works just grand.
Part of my project references 2 separate databases, strictly to source data - no updates to those databases are needed. For example I have an employee combobox that lists the employees on an other database which is maintained separately from my project, but I need the employee numbers, so this is a convenient way to list them because it is always up to date with terminations and new hires etc.
I would like to be able to populate my comboxes from VBA because sometimes variables are applied to restrict the list in the comboboxes. Also, I have a few comboboxes that require me to JOIN 2 separate tables on the separate databases. Again VBA is the easiest way I can think of changing the variables.
Does anyone have some advice on the most efficient and effective way to do this?
If this is posted in the wrong place... I'm sorry, I'm never sure what is the best place for a question that bridges several forums
I am not certain what your question is I am afraid. Are you asking how to populate an ADO recordset or how to assign the populated recordset to the combo?
Anyway - the (a?) key to retrieving data from disperate, unlinked data sources is a heterogeneous query (a bit like this one). The example is for an access table - I can't find my example for viewing SQL Server but the path is replaced with a connection string instead.
I do know how to create the recordset with ADODB. However, I do not know how to populate my combobox with the results of the recordset. Also, I'm really nott sure how to create a JOIN between two recordsets from separate databases. Of course, in nutshell I'm not even sure this is the best way to go - but it seems to be the best solution I can come up with.
I hope this helps clarify my problem.
And THANK YOU VERY MUCH for taking the tiem to help
However, I do not know how to populate my combobox with the results of the recordset.
Ok - example to populate a combo box (GetADORecSet just returns a populated recordset):
Private Sub Form_Open(Cancel As Integer)
Dim adoR As ADODB.Recordset
Dim sList As String
Set adoR = GetADORecSet
sList = adoR.GetString(adClipString, , ";", ";")
Me.Combo0.RowSource = sList
Set adoR = Nothing
I've only ever used this for smallish recordsets - I would exopect performance to degrade if it was large and I am sure I have seen a 2000 char limit to the value list in the past but can't find mention of it now.
Originally Posted by Larry Dumoulin
Also, I'm really nott sure how to create a JOIN between two recordsets from separate databases.
The simple answer is that you don't although I believe you can purchase third party products that will do this. Example below of joining data from disperate sources:
FROM Products IN '' [ODBC;DRIVER=SQL Server;SERVER=(local);DATABASE=Northwind]) AS SQL_Products
(SELECT * FROM Products IN 'C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb') AS AccessProducts
ON [SQL_Products].ProductName = AccessProducts.ProductName;
how often is this requiremnt to joining tables from 2 separatedb's, live (ie always available) or periodic?
an option would be to do do some preprocessing (reduce the amount of data coming accross) on one table / view possibly store into a local table and then perform your join to the other data. does it need to be an immediate snapshot of the data or is it something that rarely changes.
Actually, another possibility is linking (easy) or clustering (not so easy and not necessarily advised) the two SQL Servers. Have a chat with your DBA about this possibility - check out sp_AddLinkedServer in BoL for more info. You could then draw your information from a single SQL Server only - you would need to use a fully qualified four part name for the "remote" data (server.database.owner.objectname).