[AccessD] Terrible performance like I have never seen before

Hi Doug:

My approach was rather straight forward but there is not real quick way to
do it right hence the nearly month long odyssey to get the system moved.
Fortunately the client was on a monthly contract so I could spread the work
out over a couple of months as they are not likely to just accept a 10K
bill...but as they are on a monthly contract that is exactly what they will
pay. ;-)

I installed a MS SQL on the client's site and used the Upsizing wizard to
construct all the tables on the SQL and rebuilt all the queries in Stored
Procedures. (I have done this before so I have all basics are pre-built
like: Add, Delete, Update, first, next, previous, last, goto-record... with
their associated UDF (User defined function... same as a function in
Access)) The queries have to be rebuilt to compile with SP standards.

The application usually has to under go a few changes. 
No bound... that is what a SQL server is for; it does the data management. I
populate all the forms, reports, list and combo boxes via recordsets. I
usually populate a form one record at a time as it is usually so fast that
the client doesn't notice any delays.

Those recordsets are assembled through a SQL interface module. Rather than
go into a page by page explanation suffice to say its sole purpose is to
connection with the SQL, retrieve and update recordsets through an ADO OLE
interface. The only tricky parts can be from dealing with combo boxes and
reports but newer versions of Access take a lot of the grunt work out.

If you want to know more I will answer you questions one at a time.

Jim

  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Steele
Sent: Thursday, July 08, 2010 7:07 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Terrible performance like I have never seen before

Jim, BE conversion to SS is something I have been planning to start doing
with my clients.  Can you give a bit more detail on how you proceeded?  Did
you convert the BE totally to SS, then debug the FE until it was working,
then tune it?  Or did you have 2 BE and gradually convert table by table?
Or other??

Thanks,

Doug

On Wed, Jul 7, 2010 at 7:43 PM, Jim Lawrence <accessd at shaw.ca> wrote:

> I have heard your story before and have held a couple of clients back from
> upgrading until I can transfer their BE to MS SQL or SQL Express. The last
> app that I migrated from a MDB BE to SQL Express, took about a month, in
> dribs and drabs, to re-write, with SPs and using ADO-OLE connection. (Real
> men and women do not use ODBC so we will not use that four letter
> euphemism,
> in polite company, again.) ;-)
>
> The new app now runs about twice as fast as the old 2003 FE/BE even using
> the retarded Access2007 FE. This fall or early spring I will be moving the
> FE to .Net and that will be an end to it.
>
> Jim
>
>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com