[AccessD] Terrible performance like I have never seen before

Hi Doug:

I would probably get a test server with a SQL server on it and try an upsize
and see how it works... queries and tables. 

I would recommend building the BE first and then you can test the queries/SP
and see if they are producing the correct results.

...Then build the connection tier/module as updates and functionality could
be tested in really time.

...And finally tackle the FE.

That is the way I have always done it... foundation first before building
the upper floors. ;-)

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 9:11 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Terrible performance like I have never seen before

Hi Jim:

Thanks for the details.  All my forms (300+) are bound, and I have a couple
of thousand queries :(   I guess the logical first step is to start
unbinding the forms...

Doug

On Thu, Jul 8, 2010 at 8:11 AM, Jim Lawrence <accessd at shaw.ca> wrote:

> 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
>
>
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com