ADO Recordset populate combo box (1 Viewer)

[hi there]

hi all,

not sure if this is the correct forum but here goes. is it possible to have a disconnected ADO recordset populate a combo box in an Access Project (.adp)? i have a Access XP front end piping back to a SQL Server 2k dbase. i'm using a stored procedure to handle my insert, however i have a couple of combo boxes on my data entry form that i need to populate on the form's "OnLoad" event. i was wondering if i can populate these using a disconnected recordset (similar to functionality of the dataset object in ADO.NET)?

if not any ideas of best practices to populate combo boxes and limit connection resources?

many thanks for any suggestions.

 

[pono1]

Assuming the data in the combo box doesn't change often, if at all...

Create a table in the back-end to hold the combo data. On the form's load event connect to the combo data table using ADO, opening a recordset and looping through it to add the items to the combo box (configured as a value list). Close the recordset and connection.

You could also create a text file with combo data and copy it to the local (front-end) PCs, extracting the data from the text file and pouring it into the combo when the form opens. No network traffic at all. Along the same lines you could copy an MDB file with combo data tables to each front end, connecting to it and then throwing the data into the combos. Again, no network traffic.

Regards,
Tim

 

[hi there]

Hi Tim,

thanks for the response. i was thinking about trying your second solution and downloading the lookup tables from the BE locally to the FE and then basing the combo boxes on local tables in the FE. how would i go about doing this from within the FE? i was thinking about using a make table query, however i don't this as an option in an .adp. what event would you use to launch the code?

i'm also interested in your first suggestion of populating the combo box using a recordset as a value list. what exactly do you mean as a value list? using the .AddItem method? i think a tried at least some version of this. i created a ADO recordset and then bound it to the combo box using the .recordset property and everything worked fine, however does this create a persistent connection? i read somewhere that even if you close the ADO connection Access performs some magic to keep it open while the form is loaded?

many thanks for all of your help

 

[pono1]

I didn't realize VBA combo boxes had an AddItem method so, sorry, I'm not sure how to comment on that one. Is that in one of the newer versions of Access? To learn more about Value List search on RowSourceType in the Help file.

I think there is at least one code example on this forum of how to create a disconnected recordset (from way back when) and I'm sure msdn.com would have one or two, though I'm also relatively sure they relate to Access forms. Again, though, you can connect to the DB and open your recordset and then add the items to a Value List combo by looping through the recordset (grabbing each item along the way) before disconnecting from the data source and closing the recordset. Yes, you would have to write some code to do this, adding commas between items before copying the string to the combo's rowsource.

About putting the file on the front-end: I was thinking you can just copy the same file to each front-end's current directory, writing code that gets the current directory and points to it and then retrieves the data from the file. You would also probably want to write code to copy the file from the back-end to the front-end if it doesn't already exist at the front-end. A little bit of work, but not impossible.

Regards,
Tim

 

[Khalid_Afridi]

hi there

this might help

Populate combo box with disconnected recordset

 

[LPurvis]

Read that article, wherein the method Susan shows is what you would use.
There's no need to use the AddItem method at all. Indeed IMO that's wasteful.

>> "i read somewhere that even if you close the ADO connection Access performs some magic to keep it open while the form is loaded?"

No. If you disconnect the recordset then the connection can be closed.
That's the point of it.
However... there is a bug whereby a connection to a Jet (or ACE AFAIK) database will not be dropped if you opened the recordset using a Shape provider - even when you explicitly close the connection object.
You will likely be using a Shape provider as you're in an ADP, but you're then clearly not hitting a Jet database!

You're equally very likely not to be closing the connection but using the application's default connection - which is the responsibility of Access to maintain (and drop and open as it sees fit).

Cheers.