Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 30th, 2005, 04:19 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default populating cboBox with remote table data

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



 
Old October 30th, 2005, 05:09 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
 
Old October 30th, 2005, 05:23 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old October 30th, 2005, 05:39 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
 
Old October 30th, 2005, 05:44 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.

 
Old October 30th, 2005, 05:50 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
 
Old October 31st, 2005, 10:38 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

Thanks, it works.

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

Thank you, Loralee

 
Old October 31st, 2005, 12:06 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
 
Old October 31st, 2005, 11:24 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Populating an unbound form from a table Luis Access VBA 8 November 21st, 2008 08:37 AM
populating table from a subform based on a query bjcountry Access 1 April 11th, 2008 06:35 AM
Chapter 4 "Populating the Table" Chudz BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 4 August 24th, 2007 01:31 PM
Populating new table young20 Access VBA 1 November 15th, 2006 08:20 AM
Generate unique ID when populating a table sroman SQL Server 2000 2 August 5th, 2003 03:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.