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

Here is a code snippet that will connect you to sql server and run a
sproc using a parameter that is fed in from a control on the form.  HTH.
D. Lewis


Dim intBrewid  As Integer
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim rstHop As ADODB.Recordset
Set rstHop = New ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.spBrewMaltValues"
intBrewid = lstBrews.Value

Dim prm As ADODB.Parameter
Set prm = cmd.CreateParameter("Brewid", adInteger, adParamInput, ,
intBrewid)
cmd.Parameters.Append prm
DoCmd.Hourglass True

Set rst = cmd.Execute
cmd.CommandText = "dbo.spBrewHopValues"
Set rstHop = cmd.Execute 
DoCmd.Hourglass False


At 11:09 PM 1/24/2006, you wrote:
>Date: Wed, 25 Jan 2006 00:09:47 -0500
>From: "John Colby" <jwcolby at ColbyConsulting.com>
>Subject: [dba-SQLServer] Moving some object's recordsource to SQL
>         Server
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>, 
><dba-sqlserver at databaseadvisors.com>
>Message-ID: <00b201c6216d$9085fd90$647aa8c0 at ColbyM6805>
>Content-Type: text/plain;       charset="us-ascii"
>
>I need a "step by step" if you will of moving some object to SQL Server

>- combo or form - where the Access query was filtered and the filter 
>won't work in SQL Server.  For example a reference to a control on a
form.
>
>I know that I have to create a SP in SQL Server that accepts a
parameter.
>How do I "connect" to that query from Access?  Is it a n ODBC (or 
>other?) link, just like a table?  Then how do I feed the parameters to 
>the SP out in SQL server.
>
>I am getting ready to do this and am way under prepared for this.
>
>Environment:
>
>SQL Server Express 2005
>Access (Office) XP or 2003
>Fes now talking to the SQL Server BE using the standard (ODBC I think) 
>"links" that the upsize wizard created when it moved the tables.
>
>The FE seems to work as it did before except slower.  Now if I can 
>start replacing the slow "Access Queries over ODBC" with "SQL Server 
>SPs with passed params????" I might be able to see what this is capable

>of.  I just haven't a clue how to move to that "SP with Params".
>
>John W. Colby