I have a custom query form that I am in the process of revamping. Currently, the results displayed on the form are the results of a query that pulls from three different tables. After the user enters parameters and presses the command button the query is created and appended to the queries collection. I then populate the listboxes on the form based on assigning the rowsources property a Select Distinct from the query I just created. The three tables Im pulling data from are now in Access, but will soon be part of a separate db on SQL server.
What I want to do, is recreate the same thing in ADO without the reliance on linked tables or stored queries. I will create this in Access and then when the tables migrate to sql server, the only thing that I will need to change is the connection string.I have a fairly good working knowledge of using ADO, but there are some things Im not so sure about. For instance when I start this procedure and begin to populate listboxes, is is possible to assign an ADO recordset to the rowsource property of a listbox. Also, since I have many different select satements I need to get and assign the values to the textboxes and listboxes on the form, should I use the same rst variable and just continue to open and close it with a new select statement every time, or does this degrade performance. Or should I just open up a larger recordset and somehow run select statements against this recordset (if thats even possible). Just to give you some semblance of the size of the recordset, currently in Access the query thats created pulls from three tables, contains about 15 colums, and anywhere between 2 and 60 records depending on the user parameters.
Performance is definitely an issue, I want it to be fast. Although I know that when its in SQL server, using a stored procedure is the way to go, Im going to try to go with something that will work in Access too for now.
Basically, Im looking for some general guidance on strategies to use ADO effectively in this manner?