Append queries over WAN...

Hey all.

I've been working with this for some time, and a lot of the responses
have been very helpful. The latest issue is the result of the back
end being deployed on a server in another office about two hours from
where we are. Because of the nature of our connection (DSL),
communication between the front / back end is slow (sometimes
painfully so).

Here's a little background of the database:

The system was developed to manage call information and take orders.
When a person calls, we enter the information into a call log via
form; if they need to place an order, we open another continuous form
showing the parts (separated by company; the parts tables are located
on the front end to allow for quicker searching) where we adjust the
quantity column to reflect the amount ordered. The form is continuous
to allow for different parts to be ordered.

When this is completed, a button is clicked and the following code is
executed.

If the "To order" check box is clicked, it executes the first portion
of the code, otherwise it skips the append queries (ussc_Order,
imp_Order, etc.).

My question is this: what is the most efficient way to speed up this
process? Would it be better to combine all parts into one table
(thus, only one append query) or is there a way to evaluate if a
particular table has a QTY > 0 anywhere in it and avoid executing the
other append queries?

I have tried:

If [ussc_Parts].[ussc_Qty] > 0 Then
DoCmd.OpenQuery "ussc_Order"
ElseIf [cfm_Parts].[cfm_Qty] > 0 Then
DoCmd.OpenQyery "cfm_Order"
etc....

To no avail.

Any ideas?

Thanks for your help,

Sam

CODE:
************************************************************ *************************************

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

'*************
'Saves call.
'*************
DoCmd.Save

'********************
'For order placement.
'********************
If Me.tsclog_ToOrder = True Then

DoCmd.GoToRecord , , acNewRec
DoCmd.SetWarnings False
DoCmd.Close acForm, "PartSearch_cfm"
DoCmd.Close acForm, "PartSearch_imp"
DoCmd.Close acForm, "PartSearch_sdv"
DoCmd.Close acForm, "PartSearch_mrh"
DoCmd.Close acForm, "PartSearch_ussc"
DoCmd.OpenQuery "ussc_Order"
DoCmd.OpenQuery "imp_Order"
DoCmd.OpenQuery "mrh_Order"
DoCmd.OpenQuery "sdv_Order"
DoCmd.OpenQuery "cfm_Order"
DoCmd.OpenQuery "updqry_Warranty"
DoCmd.SetWarnings True

ElseIf Me.tsclog_ToOrder = False Then
DoCmd.GoToRecord , , acNewRec
DoCmd.Close acForm, "PartSearch_cfm"
DoCmd.Close acForm, "PartSearch_imp"
DoCmd.Close acForm, "PartSearch_sdv"
DoCmd.Close acForm, "PartSearch_mrh"
DoCmd.Close acForm, "PartSearch_ussc"

End If

Re: Append queries over WAN...

Some practical approaches to speed up the application over a wan is
mentioned here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Re: Append queries over WAN...

On Oct 18, 10:57 am, Sam wrote:
> Hey all.
>
> I've been working with this for some time, and a lot of the responses
> have been very helpful. The latest issue is the result of the back
> end being deployed on a server in another office about two hours from
> where we are. Because of the nature of our connection (DSL),
> communication between the front / back end is slow (sometimes
> painfully so).
>
> Here's a little background of the database:
>
> The system was developed to manage call information and take orders.
> When a person calls, we enter the information into a call log via
> form; if they need to place an order, we open another continuous form
> showing the parts (separated by company; the parts tables are located
> on the front end to allow for quicker searching) where we adjust the
> quantity column to reflect the amount ordered. The form is continuous
> to allow for different parts to be ordered.
>
> When this is completed, a button is clicked and the following code is
> executed.
>
> If the "To order" check box is clicked, it executes the first portion
> of the code, otherwise it skips the append queries (ussc_Order,
> imp_Order, etc.).
>
> My question is this: what is the most efficient way to speed up this
> process? Would it be better to combine all parts into one table
> (thus, only one append query) or is there a way to evaluate if a
> particular table has a QTY > 0 anywhere in it and avoid executing the
> other append queries?
>
> I have tried:
>
> If [ussc_Parts].[ussc_Qty] > 0 Then
> DoCmd.OpenQuery "ussc_Order"
> ElseIf [cfm_Parts].[cfm_Qty] > 0 Then
> DoCmd.OpenQyery "cfm_Order"
> etc....
>
> To no avail.
>
> Any ideas?
>
> Thanks for your help,
>
> Sam
>
> CODE:
> ************************************************************ *************************************
>
> Private Sub Command9_Click()
> On Error GoTo Err_Command9_Click
>
> '*************
> 'Saves call.
> '*************
> DoCmd.Save
>
> '********************
> 'For order placement.
> '********************
> If Me.tsclog_ToOrder = True Then
>
> DoCmd.GoToRecord , , acNewRec
> DoCmd.SetWarnings False
> DoCmd.Close acForm, "PartSearch_cfm"
> DoCmd.Close acForm, "PartSearch_imp"
> DoCmd.Close acForm, "PartSearch_sdv"
> DoCmd.Close acForm, "PartSearch_mrh"
> DoCmd.Close acForm, "PartSearch_ussc"
> DoCmd.OpenQuery "ussc_Order"
> DoCmd.OpenQuery "imp_Order"
> DoCmd.OpenQuery "mrh_Order"
> DoCmd.OpenQuery "sdv_Order"
> DoCmd.OpenQuery "cfm_Order"
> DoCmd.OpenQuery "updqry_Warranty"
> DoCmd.SetWarnings True
>
> ElseIf Me.tsclog_ToOrder = False Then
> DoCmd.GoToRecord , , acNewRec
> DoCmd.Close acForm, "PartSearch_cfm"
> DoCmd.Close acForm, "PartSearch_imp"
> DoCmd.Close acForm, "PartSearch_sdv"
> DoCmd.Close acForm, "PartSearch_mrh"
> DoCmd.Close acForm, "PartSearch_ussc"
>
> End If

Given the circumstances you descibe I would:
1. use unbound forms
and/or
2. not bind the db at all (depending on what else had to be done)
and/or
3. convert the backend to sql-server
and/or
4. rent internet available sql server space
and/or
5. use an adp
and/or
6. use a web application
and/or
7. pay a capable professional to review all aspects of the application
that might impinge on speed (the queries especially)

I have two sql databases that live on sql severs more than 4000
kilometers away. I connect with them via a cable internet connection.
All my transactions with these are instantaneous. They are also
accessed in Europe, 12000 km away, instantaneously as well, and
sometimes on dial-up, where they are slower but usable.
But I have no persistent connections, and I spend sometime trying to
create the most efficient sprocs (queries, more or less) possible.
Others here have stated that internet enabled sql servers are very
insecure. I disagree. I keep one (a third) empty db available and
invite those convinced of insecurity to break in and create a table
named for themselves. It's been about a year now, and the db is still
so empty!