[dba-SQLServer] Moving some object's recordsource to SQL Server

On 25 Jan 2006 at 23:08, John Colby wrote:
> 
> I am getting a bunch of different answers from build "pass through queries"
> to "open ADO record sets" (with painfully little detail).
> 
> If I were to do a pass through query, what in the heck is a pass through
> query?  These are the kinds of answers that I find frustrating, because they
> just don't provide enough substance to do anything with.  If I knew what a
> pass through query was, then I would not even be having this conversation.
> How do I build one?  
> 
>From Access Help:
<quote>
In the Database window, click Queries  under Objects, and then click New on 
the Database window toolbar. 
In the New Query dialog box, click Design View, and then click OK. 
Without adding tables or queries, click Close in the Show Table dialog box. 

On the Query menu, point to SQL Specific, and then click Pass-Through. 
On the toolbar, click Properties  to display the query property sheet. 
In the query property sheet, set the ODBCConnectStr property to specify 
information on the database to which you want to connect. You can type the 
connection information, or click Build , and then enter information about 
the server you're connecting to. 
When you are prompted to save the password in the connection string, select 
Yes if you want the password and logon stored in the connection string 
information. 
</quote>

> What does it look like?  

In the query window, when you open it or when you edit it? :-)
It looks like any othe SQL query in the QueryDefs collection
The SQL would just be "sp_MyStoredProcedure 1" assuming 1 was the value of 
the parameter you want to pass.

> How do I feed it parameters?

Ah-ha, that's the tricky bit - you need to rewrite the querydef before 
executing it.

> How do I reference it in a form or combo?

Here's an example if you are using DAO:

Function GetDataDAO() As Boolean
Dim qry As DAO.QueryDef
Dim rs As DAO.Recordset
Set qry = CurrentDb.QueryDefs("myQuery")
Set qry.SQL = "select * from mytable where id = " & cboIDSelector
Set rs = CurrentDb.OpenRecordset("myQuery")
...
End Function

> If I am going to use a recordset, I at least understand this idea, but can I
> (for example) build a function that returns an ADO recordset, then set the
> rowsource of the form / combo to "=MyADORs()"?  Do I need to set a "form
> global" recordset object, get that set, then poke that RS object into the
> form/combo's property?  
>

If you are using ADO, you don't need to use a passthrough query, you use an 
ADODB.Command:

Function GetDataADO() As Boolean
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
    
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_MyStoredProcedure"
'....
Set prm = cmd.CreateParameter("myParam", adInteger, adParamInput, 4, 
cboIDSelector)
cmd.Parameters.Append prm
Set rs = cmd.Execute
....
End Function

-- 
Stuart