Martin Reid
mwp.reid at qub.ac.uk
Sun Feb 1 11:35:56 CST 2004
John
Tha main drawback you face moving to SQL Server is your use of the
framework. Continued reliance of this tool will place you at a disadvantage
when working with for example an ADP file.
Of course you could consider over time rewriting the tool for use with SQL
Server.
Moving to an SQL Server backend generally gives you a few options
Use ODBC and stay with DAO
Rewrite from DAO to ADO using OLEDB
Use an ADP
USee Pass Through
etc etc
You could also consider bring the data local on login , work with it local
and then update the server with changes.
I am currently working on rewriting a complex app from DAO to ADO. Taking a
lot longer than I assumed but almost done now.
The licence issue is OK. Usually licenced on a per processor basis now so
connections dont matter.
The framework you use is I assume dictating the path you can take??
Martin
----- Original Message -----
From: "John W. Colby" <jwcolby at colbyconsulting.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Sunday, February 01, 2004 4:27 PM
Subject: RE: [AccessD] was SQL Server queries - appending strings
> I am being paid by the hour, but they are never going to go for that kind
of
> massive rewrite.
>
> John W. Colby
> www.ColbyConsulting.com
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Lawrence
> (AccessD)
> Sent: Sunday, February 01, 2004 11:01 AM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] was SQL Server queries - appending strings
>
>
> 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
>
> _______________________________________________
> 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
>