Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 30th, 2005, 04:19 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
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



Reply With Quote
  #2 (permalink)  
Old October 30th, 2005, 05:09 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
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
Reply With Quote
  #3 (permalink)  
Old October 30th, 2005, 05:23 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
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

Reply With Quote
  #4 (permalink)  
Old October 30th, 2005, 05:39 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
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
Reply With Quote
  #5 (permalink)  
Old October 30th, 2005, 05:44 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
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.

Reply With Quote
  #6 (permalink)  
Old October 30th, 2005, 05:50 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
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
Reply With Quote
  #7 (permalink)  
Old October 31st, 2005, 10:38 AM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
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

Reply With Quote
  #8 (permalink)  
Old October 31st, 2005, 12:06 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
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
Reply With Quote
  #9 (permalink)  
Old October 31st, 2005, 11:24 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:08 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.