Web Reports

[Melody]

Hi,

Is it advisable to generate web reports against
an Access database linked to a web application already in
production stage ? The database is 30-40 Mb in size and
the application is being used by 3 users for data entry.
Some of the web reports use quite complex queries (multiple
crosstabs and joins).

Really appreciate any help or advice on this.

Thanks in advance,
Melody

[Bob Barrows]

Melody wrote:

> Hi,
>
> Is it advisable to generate web reports against
> an Access database linked to a web application already in
> production stage ? The database is 30-40 Mb in size and
> the application is being used by 3 users for data entry.
> Some of the web reports use quite complex queries (multiple
> crosstabs and joins).
>
> Really appreciate any help or advice on this.
>
> Thanks in advance,
> Melody

It's hard to say without knowing how many new users will be using the
database, but it should not be a problem with only 3 users, especially if
good coding practices were used in the creation of the current web
application:

-connections opened late and closed early
-recordset loops avoided (GetRows and GetString used instead)
-SQL statements used for data maintenance rather than updatable recordsets

But even if these practices were not used in the current application, you
may be able to mitigate this by using goood practices in your new
application. The idea is to minimize the time the users are actually
connected to the database: connect, get your data, disconnect, use the data.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[melody]

Thanks for the advice :-) But I think I need some more
explanation on these two points:

-connections opened late and closed early
-SQL statements used for data maintenance rather than
updatable recordsets

Thanks in advance,
Melody

>-----Original Message-----
>Hi,
>
>Is it advisable to generate web reports against
>an Access database linked to a web application already in
>production stage ? The database is 30-40 Mb in size and
>the application is being used by 3 users for data entry.
>Some of the web reports use quite complex queries

(multiple

>crosstabs and joins).
>
>Really appreciate any help or advice on this.
>
>Thanks in advance,
>Melody
>
>.
>

[Bob Barrows]

melody wrote:

> Thanks for the advice :-) But I think I need some more
> explanation on these two points:
>
> -connections opened late and closed early

opened late:
Never open a connection until the line immediately preceding the line where
you intend to use it.

closed early:
Always close your connection immediately after the last line in your code
that does something with the connection.

As a corrolary to "closed early", avoid recordset loops. Use GetRows do
stuff your data into an array (I just posted an example of this today in the
"SQL Query" thread) and loop through the array instead. Array loops can be
thousands of times faster than recordset loops. In addition, as soon as the
data is in the array, you can close and destroy your recordset and
connection before you start processing the data (close early). Even faster
is GetString, but GetString is limited.

> -SQL statements used for data maintenance rather than
> updatable recordsets
>

Use SQL UPDATE, INSERT and DELETE statements, hopefully encapsulated in
saved parameter queries, instead of using the equivalent recordset updates,
addnews and deletes. I have posted many examples of using saved parameter
queries in this newsgroup. A Google search
([url]http://www.google.com/advanced_group_search?hl=en[/url]) should get you going.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[melody]

I spent some time going through the posts just to be sure
I understood what you were talking about :-). I'll be
trying out these techniques in my new apps. Thanks for the
advice and help.

Melody

>-----Original Message-----
>melody wrote:

>> Thanks for the advice :-) But I think I need some more
>> explanation on these two points:
>>
>> -connections opened late and closed early

>
>opened late:
>Never open a connection until the line immediately

preceding the line where

>you intend to use it.
>
>closed early:
>Always close your connection immediately after the last

line in your code

>that does something with the connection.
>
>As a corrolary to "closed early", avoid recordset loops.

Use GetRows do

>stuff your data into an array (I just posted an example

of this today in the

>"SQL Query" thread) and loop through the array instead.

Array loops can be

>thousands of times faster than recordset loops. In

addition, as soon as the

>data is in the array, you can close and destroy your

recordset and

>connection before you start processing the data (close

early). Even faster

>is GetString, but GetString is limited.
>
>

>> -SQL statements used for data maintenance rather than
>> updatable recordsets
>>

>
>Use SQL UPDATE, INSERT and DELETE statements, hopefully

encapsulated in

>saved parameter queries, instead of using the equivalent

recordset updates,

>addnews and deletes. I have posted many examples of using

saved parameter

>queries in this newsgroup. A Google search
>([url]http://www.google.com/advanced_group_search?hl=en[/url])

should get you going.

>
>HTH,
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>