Could someone please steer me in the right direction?

I have a form in (mdb)database A which I want to populate from a table in (mdb) database B. I just was able to get into the target table in database B via DAO workspace. Now I want to populate my combo box in database A.

Where do I put the code with the connection to the remote table and SQL to set the rowsource of the cbobox to populate the box?

Does it go in the form_Load, form's on current? cbobox Oncurrent?

Or somewhere else?

Thank you,

Loralee

Hi Loralee,

I would think that the form_load would be the best place. Oncurrent is triggered each time the record is changed, and would make a pretty significant influence on the performance of your form, and would be mostly redundant.

Hope that help,

Mike

Mike
EchoVue.com

Thanks, Mike,
I just tried the form load, and it makes contact. (and your point make sense). But my SQL is bombing out- I created a SELECT statement (for the cboBox) and tried referencing it via the recordset I created from the remote table, put THAT into a form level variable and set the cbobox's rowsource to it. It complains the reference (FROM) is misspelled or exists in another database. Yep, it is in another db.
So I'm stuck again. Anyone know (if) how to reference a remote table using SQL SELECT statement?

Thanks,
Loralee
Loralee

Would it be feasible to just link the table? This way you could reference the table as though it were local. Depending on the amount of data you are bringing in, it can also speed it up.

I don't know if that is any help, but you never know!

Mike

Mike
EchoVue.com

That's what I'm trying to do, programatically and temporarily.

Is there another way to do this? With current use we will only need to use the data maybe 20 times a year.

All you would need to do, is go to File -> Import -> Link Table, and then go out and find your table. I use this a lot in a set of databases that all access common data in addition to unique data. This way, I only have to update the data in one place.

Let me know if that works for you,

Mike

Mike
EchoVue.com

Thanks, it works.

I was hoping to do something more temporary as we will only use it occasionally.

Thank you, Loralee

Hi Loralee,

Let me play and see if I can come up with a way to create a linked table using VBA, so that it appears as a local for purposes of your form - Unless of course one of the great 'Geniuses' out there know how to do it?

Thanks


Mike
EchoVue.com

I can't believe it. I got it to work. I created the SQL Select, then passed it as the argument to open the recordset, and it makes contact! And I didn't have to go any higher than local with the variable!

Thanks for you help!
********************************************
(In case you're interested, here is what worked)

Private Sub Form_Load() ' 10-30-05 attempt to populate cboCHDPVendor with remote data
    Dim wrk As DAO.Workspace
    Dim dbCHDP As DAO.Database ' remote db
    Dim rstCHDPVendor As DAO.Recordset
    Dim strPracticenum As String
    Dim strSELECT_CHDP As String
    Dim strFROM_CHDP As String
    Dim strORDERBY_CHDP As String
    Dim mstrSQL_CHDP As String ' this will become module level
    Dim strSQL_chdp As String

    Set wrk = DBEngine(0)
    Set dbCHDP = wrk.OpenDatabase("C:\Documents and Settings\Loralee\My Documents\CHDP Database\CHDP Database 10272005 1456.mdb", False, True)
    'Set rstCHDPVendor = dbCHDP.OpenRecordset("tblpractice", dbOpenTable)

    strSELECT_CHDP = "SELECT p.practicenum, p.legalname, p.address, p.cityid, p.zip, p.fax, p.phone1 "
    strFROM_CHDP = "FROM tblpractice as p "
    strORDERBY_CHDP = "ORDER BY p.legalname "
    strSQL_chdp = strSELECT_CHDP & strFROM_CHDP & strORDERBY_CHDP

    Set rstCHDPVendor = dbCHDP.OpenRecordset(strSQL_chdp, dbOpenDynaset)
    rstCHDPVendor.MoveFirst
    strPracticenum = rstCHDPVendor!practicenum


    Me.cboCHDPVendor.RowSource = strSQL_chdp


    rstCHDPVendor.Close

    wrk.Close
    Set rstCHDPVendor = Nothing '
    Set dbCHDP = Nothing
    Set wrk = Nothing

End Sub