Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 listBox.rowSource = ado rs?

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-02-17 : 13:56:52
I have tons of code blocks in an Access 97 app that bind the row source to a select statement. Now I am in a process of replacing embedded sql with stored Procs. I am able to call many type of sps from it using ado. The only thing I have trouble with are these combo/listboxes. How could I bind them to a recordset/dataset dynamically?

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-17 : 20:01:03
It might be better to leave the combobox record source as Access query, but change it to a pass-through that calls your stored procedure. You can pass parameters to it with code like this:

Dim qry as QueryDef
Set qry=CurrentDb.QueryDefs("MyPassThrough")
qry.SQL="EXECUTE myStoredProcedure @p1='" & parameter1 & "', @p2='" & parameter2 & "'"
qry.Close
Me.ComboBox1.Requery


The main things to watch are setting the SQL property to a valid SQL Server query/stored procedure call, and that the pass-through query's ODBC settings are correct.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-02-18 : 09:43:23
Thank you Robvolk. That is something I am testing. At the meantime, I want to do it under ado instead of dao, since I just converted most of the dao. Do you have an example of how it will look like in the ado world? I did not find one on the search engine.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-18 : 21:11:18
I don't think the stock MS Access forms will work with ADO as a record source. The last version of Access that I used extensively was '97, I'm sure with 2000 and after they've integrated ADO into it, so it may work. I know that QueryDefs are NOT an ADO feature.

Best thing to do is check the MS Access VBA reference, it's buried somewhere in the help file, but they usually have excellent examples of how to do things in code.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-18 : 22:40:29
Because you are using Access, the best way to manipulate the objects in the DB is using DAO. YOu can still open ADO recordsets in your VBA code as needed and otherwise use ADO, but to manipulate tables and queries and such in Access, DAO is the way to go.

- Jeff
Go to Top of Page
   

- Advertisement -