[AccessD] was SQL Server queries - appending strings

Jim Lawrence (AccessD) accessd at shaw.ca
Sun Feb 1 10:00:38 CST 2004


Hi John:

There is a lot of work in creating the 'data-interface' to MSSQL and ADO-OLE
is the only option (ODBC is too slow....) Stored Procedures are programs,
subroutines and functions more than just queries. No 'query' import tools
can work because though SQL SP work similar they are really quite different
in concept.

I suspect that everything will have to be completely hand coded at the
query/recordset backend but your frame-work should basically be able remain
in place.

Another thing to note is that the MSSQL license may not be 'unlimited' but
scaled on 'connections' not users. One user of a 'bound' application could
grab as many as 20 plus connection or the whole license. The licensing may
have changed (I have been working with Oracle for the last year or so) but
some of our SQL Gurus may know the finer points.

Hope you are being paid by the hour.

Jim


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John W. Colby
Sent: Sunday, February 01, 2004 5:20 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] was SQL Server queries - appending strings


>It's really about the right tool for the job, although speculation would
deem that an ADP would be better suited.

Converting to an ADP may or may not even be possible.  The system uses my
framework which does a TON of rather fancy stuff like swapping combo / form
row/recordsources out on the fly, assigning the recordsource after the
framework class instantiates etc.  Not to mention linked spreadsheets that
feed queries for data importation.  LOTS of reasons why ADPs may never be
possible.

>No, Views do not take parameters, that's what Stored Procedures (aka
sprocs) are for..

Yes, but Stored procedures are non-editable, correct?  Remember, these are
bound forms needing editable recordsets.

>you are mistaken.  you can assign the recordset to the recordsource, but
because the data is from sql server (in an mdb) it becomes read only.

Yes, but as I said in the sentence before, this MUST BE EDITABLE!  Which
means I can't assign a recordset to the recordsource.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Francisco H
Tapia
Sent: Sunday, February 01, 2004 1:40 AM
To: Access Developers discussion and problem solving;
dba-SQLServer at databaseadvisors.com
Subject: Re: [AccessD] was SQL Server queries - appending strings


John W. Colby wrote:
> Thanks both of you.
>
> I have a client that purchased SQL Server and so it's time to get off the
> dime and learn this stuff.
>
> The client uses A2K.
>
> Now that I am changing to SQL Server I have to get the server doing more
of
> the work - the whole point is of course to speed things up.  For technical
> reasons (a whole SLEW of reasons) going to an ADP is NOT an option any
time
> in the near future, if at all.

It's really about the right tool for the job, altho speculation would
deem that an ADP would be better suited.

> I have a main tabbed form which uses a multi-table join to get live data
> from a client / claim .  The client wanted it that way so that the user
> could edit fields in either table.
>
> Views appear to be similar to tables, i.e. I can link to them and they
show
> up in the table window.  Is there a way to pass parameters to them?
"Where
> LastName like col*" etc?

No, Views do not take parameters, that's what Stored Procedures (aka
sprocs) are for..

> ATM, I pull the whole recordset and then filter down to a single record.
> Not fast, but after the initial pull the filter proceeds at a reasonable
> rate.  I can then "filter" to a specific claim reasonably fast.  I would
> like to change this to actually ask SQL Server for a view of exactly one
> record each time they want to see a claim.  Thus avoiding the "pull the
> entire recordset across the net, now pull the index, etc.

That would be effective.. again... Sprocs, think Pass-Through Queries..
generally you'd just type into the SQL window (technically not the qbe
window), EXEC stp_MyFavoriteSproc Param1, Param2

I'll take this quote from David McAfee's post over on Access-L on
exactly this issue

/QUOTE/
I couldn't get it working that way after all. I cant remember how I used
to do that.

but...

In one of my samples (A97 db connected to A97 BE with an ODBC link to
SQL 2K)

I created a pass-through query called "_BrettsPassThru"

The SQL inside the query is:

         EXEC devREDe.dbo.BrettsPOQuery '1/1/2004', '1/22/2004'


So I created a command button and placed the following code behind it:

CurrentDb.QueryDefs("_BrettsPassThru").SQL = "EXEC
devREDe.dbo.BrettsPOQuery '" & Forms![Monthlyreport]![StartDate] & "',
'" & Forms![Monthlyreport]![EndDate] & "'" 'strSQL
DoCmd.OpenQuery "_BrettsPassThru"

Let me know how it works for you.

David
/END QUOTE/

> This must be editable so AFAIK I cannot use stored procedures which would
> allow passing parameters.  Because this is A2K AFAIK I cannot assign a
> recordset to the form's recordsource property.

you are mistaken.  you can assign the recordset to the recordsource, but
  because the data is from sql server (in an mdb) it becomes read only.

> What are my options here?  Do I have any?

Pass Through, convert the mdb to an ADP, Stored procedures.  and Yes you
do :)


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



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



More information about the AccessD mailing list