[AccessD] MDB to SQL Server

Gustav:  I fully expect that this is part of the problem and I am certainly 
not afraid of temp tables.  You can create a temp database for this purpose 
on the fly and that is my preferred approach for temp tables.  Part of the 
problem with the flexible queries is that I need to report fields from some 
of the various tables.  I generally export these kinds of report directly to 
Excel rather than using Access reporting.

I had previously mentioned the 'too many databases' error and am looking at 
ways to cut the number of connections even further.  At one point I had 3 
forms that could not be opened at the same time but I resolved that issue by 
adding callbacks for all combos that had rowsources with fewer than 100 
records.  No doubt such a message would be an indication that it is time to 
remove some information from those screens and maybe move some of the data 
to more tabbed sub forms.

I doubt that much optimization can be done by JET or whatever database 
engine if a variety of queries are joined ad hoc in a mix of equal and 
unequal joins.  I can do some playing with using temp tables and will work 
with that.  A temp table that just contains active projects and current 
active related companies could provide a massive boost in performance.  The 
problem is that if I bind to that and fire updates with a dbExecute, I'm 
effectively running unbound and need to manage record locking.  I will 
definitely give this some thought.

Of course reporting (to screen in a form) and adding/updating data are two 
different issues.  Up to this point, I've not needed to distinguish much 
between the two.  I've always tried to make things as flexible and 
convenient as possible in terms of allowing data to be located and updated 
no matter where it is seen.  I generally allow related data to be edited in 
subforms without the need to open a parent form viewing only the related 
data.

I just received a forwarded email from IT to my boss that SQL Server will be 
an option, but that with the move from 2 to 4 newer and faster servers, we 
would adopt a wait and see attitude.  For the time being, I will do a number 
of minor things to fine tune performance.


Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Gustav Brock" <Gustav at cactus.dk>
>
>Hi Jürgen
>
>Maybe that is part of your problem. I often use one a main query joined 
>with different subqueries, each saved as a new query that the report pulls 
>data from - which query to be used is set by the code than runs the report.
>
>However, often this can be boiled down to 1) define the selection of ID to 
>pull, 2) pull the "real" data for that selection of IDs.
>A lot of time can be saved if the selection if IDs is written to a temp 
>table which then is inner joined to select and extract the real data.
>
>I remember on example on reporting taxes to the authorities on liquors 
>delivered, based on both percentage of alcohol and the type of liquors as 
>well as rates varying over time. A nice setup of queries fixed it perfectly 
>but it took "ages" to run. By using one temp table I cut minutes to 
>seconds.
>I know many refrain from temp tables because they are scared of bloating 
>the mdb (avoid that by using a separate local temp database you recreate 
>each time the user launches the app) but most (all?) SQL server engines 
>feature automatic maintenance of temp tables.
>
>/gustav
>
> >>> jwelz at hotmail.com 22-02-2007 04:05:23 >>>
>
>..  Same report but involving joins, and sometimes equal and other times
>unequal joins to an indeterminate variety of tables.  Sometimes 1 table,
>sometimes 5 or 6, sometimes with parameters on some fields, at other times
>with parameters on other fields.  I couldn't imagine doing this with saved
>queries.

_________________________________________________________________
Don’t waste time standing in line—try shopping online. Visit Sympatico / MSN 
Shopping today! http://shopping.sympatico.msn.ca