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.
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 QueryDefSet qry=CurrentDb.QueryDefs("MyPassThrough")qry.SQL="EXECUTE myStoredProcedure @p1='" & parameter1 & "', @p2='" & parameter2 & "'"qry.CloseMe.ComboBox1.RequeryThe 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. |
 |
|
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. |
 |
|
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. |
 |
|
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 |
 |
|
|
|
|
|
|