Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    40

    Unanswered: Populate Combobox from ADODB Recordset

    Hello All;

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    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.

    HTH and I will post again when I find my example
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2005
    Posts
    40

    Clarification on my question

    Hi;

    SOrry I was not a bit clearer.

    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Larry Dumoulin
    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):

    Code:
    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
    	adoR.Close
    	Set adoR = Nothing
     
    End Sub
    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.

    Quote 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:
    Code:
     SELECT [SQL_Products].ProductID
    FROM 
    (SELECT *
    FROM Products IN '' [ODBC;DRIVER=SQL Server;SERVER=(local);DATABASE=Northwind]) AS SQL_Products 
    LEFT JOIN 
    (SELECT * FROM Products IN 'C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb') AS AccessProducts 
    ON [SQL_Products].ProductName = AccessProducts.ProductName;
    HTH
    Last edited by pootle flump; 12-15-05 at 05:47.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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).

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    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?
    Duh - I only posted one of your qustions. Apols - I lose track very easily.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •