John Colby
jwcolby at ColbyConsulting.com
Thu Jan 26 00:30:34 CST 2006
Cool, thanks Stuart.
I created a select SP out on the server, with two parameters (people
filtered by state ID and therapist discipline ID). Tested it out there. It
looks like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spFrmCallListFiltered]
-- Add the parameters for the stored procedure here
@STID int,
@DIID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT tblPeople.PE_ID, tblPeople.PE_IDCRF, tblPeople.PE_Specialty,
tblPeople.PE_LName, tblPeople.PE_FName, tblPeople.PE_MName,
tblPeople.PE_Addr1, tblPeople.PE_Addr2, tblPeople.PE_City,
tblPeople.PE_Zipcode1, tblPeople.PE_Zipcode2, tblPeople.PE_IDST,
tblPeople.PE_PhoneH, tblPeople.PE_PhoneW, tblPeople.PE_Currempl,
tblPeople.PE_Contact1, tblPeople.PE_Licotherstates, tblPeople.PE_RateInfo,
tblPeople.PE_Worktime, tblPeople.PE_Email, tblPeople.PE_BusAddress,
tblPeople.PE_Certification, tblPeople.PE_LIC_Licno, tlkpState_1.ST_Code AS
LicenseState, tblPeople.PE_DateStampName, tblPeople.PE_DateStampAddress,
tblPeople.PE_DateStampPhoneH, tblPeople.PE_PhoneCell,
tblZipCntydistinct.CountyName, tblPeople.PE_IDDNC, tblPeople.PE_IDIWH,
tblPeople.PE_MBIF, tblPeople.PE_MBIFFollowUpDate, tblPeople.PE_HotLead,
tblPeople.PE_HotLeadNotes, tblPeople.PE_Inactive,
tblPeople.PE_NoHomePhoneInfo, tblPeople.PE_FOE, tblPeople.PE_LIC_IDST,
tblPeople.PE_LIC_IDDI
FROM tlkpState RIGHT JOIN (tlkpCRF INNER JOIN (tblZipCntydistinct
RIGHT JOIN (tlkpState AS tlkpState_1 INNER JOIN tblPeople ON
tlkpState_1.ST_ID = tblPeople.PE_LIC_IDST) ON tblZipCntydistinct.ZipCode =
tblPeople.PE_Zipcode1) ON tlkpCRF.CRF_ID = tblPeople.PE_IDCRF) ON
tlkpState.ST_ID = tblPeople.PE_IDST
WHERE (((tblPeople.PE_LIC_IDST)=@STID) AND
((tblPeople.PE_LIC_IDDI)=@DIID))
ORDER BY tblPeople.PE_LName, tblPeople.PE_FName;
END
This query was updateable back in the MDB version but does not appear to be
in SQL Server though I am not entirely (or even marginally) familiar with my
tool yet (SQL Server Management Studio Express - for SQL Server 2005
Express), so I can't really say that it is not.
At any rate, I then created a pass through query back in Access per your
instructions, which worked wonderfully thanks very much. I hard coded
values for the state and discipline ID and can run the query and get data
from the query. It definitely is NOT updateable back in Access. Sigh.
But hey, I am waaaay further along the path than I was at the beginning of
the night. When I started I had to download (over the internet) the upsized
SQL Server database tables from the client's machine to my laptop, go get
and install management studio express on my laptop, "mount" the database,
relink the FE to the SQL Server db on my laptop, build my very first SP, and
build my very first pass through query. I am able to see my FE work using
the "linked" odbc connections to the SQL Server BE, and I am able to at
least view the data from my first pass through query. I just placed the
pass through query in place of the original query.
spFrmCallListFiltered 9,5
and went from 10 second loads to about 1 second load. I am not yet actually
modifying the IDs dynamically, i.e. the PT query has the values hard coded
in the "SQL" in the querydef, but this is still amazing to me. It seems
like I could open the query def and replace the 9,5 with whatever the user
selected from the state and discipline combos, save the query def and
requery the form. Perhaps not too efficient but still...
Thanks again Stuart for stepping up with a "step by step". That was enough
to get me going at least.
John W. Colby
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Wednesday, January 25, 2006 11:47 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com