[AccessD] Moving some object's recordsource to SQL Server

Short answer: Pass Through query.

I havent used 2005 yet, but using SQL 2000 BE's, I have had success with
using pass-thru queries as record source for Access forms.  Primary
advantage is that the returned recordset is DAO and form behaves as any
Access form.

1. Define an SP:
CREATE PROCEDURE sp_cust_order
@custid int
AS
SELECT * FROM tblOrder
WHERE customerid=@custid
GO 

2. Create an Access PT query "qry_pt_CustOrder", whose sql is
"sp_cust_order 13"
(You will be prompted for ODBC connect info) 

3. Use VBA code in calling/menu form to redefine the sql of that query, as
needed, to change the parameter, before opening form.
Currentdb.querydefs("qry_pt_CustOrder").sql="sp_cust_order 14"
Docmd.openform "frmCustOrder"

The qdef will have a connect property that should be refreshed whenver you
refresh the linked tables.

Hth
Steve


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Wednesday, January 25, 2006 12:10 AM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [AccessD] Moving some object's recordsource to SQL Server

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
www.ColbyConsulting.com 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com