Slow Response on Shared Data-using Workgroup

Similar Threads:

1.Very slow response when MDB stored on Small Business Server 2003 network share

2.slow response time in Access 2002 using a date variable for cr

"MGFoster" wrote:

> Barb wrote:
> > I have converted an Access 97 database to Access 2002, (file format 2000). I 
> > have a make table query that retrieves data from on Oracle database.  I am 
> > using a start and end date entered by the user in one of the criteria fields. 
> > The response time in Access 2002 is about 20 mintes.  In Access 97 it was 
> > less than 1 minute.  If I "hard code" the dates i.e. (between "07/09/2005# 
> > and "07/15/2005#) in the query in the Access 2002 version, the response time 
> > is less than one minute.
> > 
> > Any ideas what could be causing this slow response time using the variable 
> > name ?
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> The correct syntax for "hard coding" a date is  #07/09/2005#.
> 
> You don't say how you're getting the date from the user - is it a query
> pop-up prompt, or a form reference?
> 
> You could explicitly specify the parameter data type by putting
> something like this in the query:
> 
>    PARAMETERS [Start Date?] Date, [End Date?] Date;
>    SELECT ... etc.
> 
> The above is an example of a pop-up parameter.  Here is an example of
> using a form reference parameter:
> 
>    PARAMETERS Forms!CriteriaForm!txtFrom Date,
>               Forms!CriteriaForm!txtTo Date;
>    SELECT ... etc.
> 
> When you explicitly declare the data type of the parameters, Access
> doesn't have to "guess" the data type and try to convert it to the
> proper data type.  This should speed up the query a little.
> 
> You also don't say if the query is a JET (Access) query or an SQL Pass
> Through (SPT) query.  Usually, using an SPT will result in a quicker
> query 'cuz it is letting the RDBMS server (Oracle, in your case) do the
> processing.  The SPT has to use the RDBMS's SQL syntax, not JET's SQL
> syntax.  Read the Access SQL articles on pass thru queries for more
> info.
> -- 
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> 
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> 
> iQA/AwUBQuB29YechKqOuFEgEQLP0QCgqzkcaYwHUjBxwO9GeQq4oCQJ3VMAoIWV
> l252f1qMnoeikIrI3yB+xiWJ
> =mr4Z
> -----END PGP SIGNATURE-----
> 


Yes, sorry about the typo on my part.  I did have the correct syntax for the 
hard coded date.

I am getting the date from the user via a VBA module:

Option Compare Database
Public getd As Integer
Public DayNum As Long      'Number of days since first of year
Public WeekNum As Long     'Number of Weeks since first of year
Public MonthNum As Long    'Number of Weeks since first of year

Public begin As Date
Public finish As Date

Option Explicit
Public Static Function Startdate(getp As Integer)
Static start As Date
Static DateSTR As String
Static MonthSTR As String
Static YearSTR As String

' Getd 0  will reset the start and end date to yesterday
' Getd 1  will get the start or end date
' Getd 2  will change the start or end date
' Getd 3  will show the start or end date

' The following are automated dates.
' Getd 7  will set the start and end date to the previous week
' Getd 8  will set the start and end date to this Week up to and including 
today.
' Getd 11  will set the start and end date to the previous Month
' Getd 12  will set the start and end date to this Month to date

getd = getp

If getp = 2 Then
        start = InputBox("Enter start date  ")
    Startdate = start
Else
        If getp = 0 Then
        ' set to yesterday's date
        start = Format(Now() - 1, "mm-dd-yy")
        Startdate = start
        ElseIf getp = 7 Then
        ' Set to Last weeks starting date (Saturday)
        start = Format(Now() - (WeekDay(Now(), vbSaturday) + 6), "mm-dd-yy")
        Startdate = start
        ElseIf getp = 8 Then
        ' Set to Beginning of this week starting date
        start = Format(Now() - (WeekDay(Now(), vbSaturday) - 1), "mm-dd-yy")
        Startdate = start
        ElseIf getp = 11 Then
        ' Set to Last Months start date
        YearSTR = Year(Now())
        MonthSTR = Month(Now()) - 1
            If MonthSTR = 0 Then
            MonthSTR = 12  ' beginning of year rap back
            YearSTR = YearSTR - 1 ' wrap back to previous year
            End If
        DateSTR = MonthSTR + "-01-" + YearSTR
        start = Format(DateSTR, "mm-dd-yy")
        Startdate = start
        ElseIf getp = 12 Then
        ' Set to beginning of this Months start date
        MonthSTR = Month(Now())
        YearSTR = Year(Now())
        DateSTR = MonthSTR + "-01-" + YearSTR
        start = Format(DateSTR, "mm-dd-yy")
        Startdate = start
        Else
        Startdate = start
        End If
        
        
        Debug.Print Startdate
        Debug.Print getp      'Debug info
        Debug.Print start
End If
begin = start
Debug.Print begin
End Function

Public Static Function Enddate(getd)
Static endd As Date

If getd = 2 Then
    endd = InputBox("Enter Ending date  ")
    Enddate = endd
Else
    If getd = 3 Then
'        Display the current end date
        Enddate = endd
    Else
        If (getd = 0 Or getd = 8 Or getd = 12) Then
        endd = Format(Now(), "mm-dd-yy")
        Enddate = endd
        ElseIf getd = 7 Then
        endd = Format(Now() - (WeekDay(Now(), vbSaturday)), "mm-dd-yy")
        Enddate = endd
        ElseIf getd = 11 Then
        endd = Format(Now() - (Day(Now())), "mm-dd-yy")
        Enddate = endd
        Else
        Enddate = endd
        End If
    End If
End If
Debug.Print Enddate
Debug.Print getd
Debug.Print endd

finish = endd

Debug.Print finish

End Function

The start and end dates are initialzed to a certain date, such as, 
=Startdate(3), on the main input form field, or the user can change the start 
and end dates.   
The variable in the make table JET query uses "Between Startdate(1) and 
Enddate(1)"
as the criteria for the date field. In debug mode, when I run ? 
Startdate(1), the date format returned is 7/9/2005.  

I have inherited this program from someone no longer here, and don't know as 
much about Access as he did, but I am learning more and more as I go.

When I run the query with the date variable (before I turn it into a make 
table query) the response time is quick.  The long repsonse time comes in 
when I turn it into a make table query and run it.

I also tried a pass through query, but wasn't sure how to reference the date 
variable field in that query.  It did not like recognixe Startdate(1).

Thanks very much for your help,
Barb

3.slow response time in Access 2002 using a date variable for criter

I have converted an Access 97 database to Access 2002, (file format 2000). I 
have a make table query that retrieves data from on Oracle database.  I am 
using a start and end date entered by the user in one of the criteria fields. 
The response time in Access 2002 is about 20 mintes.  In Access 97 it was 
less than 1 minute.  If I "hard code" the dates i.e. (between "07/09/2005# 
and "07/15/2005#) in the query in the Access 2002 version, the response time 
is less than one minute.

Any ideas what could be causing this slow response time using the variable 
name ?

Thanks,
Barb

4.Slow Response when using Save As

Looking through a few news groups, a lot of people have experienced the
problem where there is a considerable dely when using Save As. It does
not matter what office app you use.
Other like Notepad work fine.
We and all our customers use folderredirection and thus by deleting the
contents of \\"server name"\folderredirection\"username"\application
data\microsoft\office\recent, this seems to solve the problem.

This "fix" took a while and include removing all mapped drives an
network places.

Does anyone know of a registry entry or otherwise to limit the number
of entries into this recent folder.
Would be helpful, instead of every month or so having to direct over
2000 users to empty this folder each time

5.Slow response using front-end over network

Melody,
the problem description pointing to VPN; DSL and a particular firewall you
are using.
Besides, VPN is always an Internet based protocol. Sending a pure graphic
(which is the Access forms) with ActiveX controls over the Internet will
kill the application performance anyway.
If your company has enough money - implement Cytrix.
Otherwise try Terminal Services (even so thay are not really secured).
Anyway, your problem has to do with a "nature" of Access, not Access tuning
at all.


"Melody" < XXXX@XXXXX.COM > wrote in message
news:0dfe01c3577a$13c30380$ XXXX@XXXXX.COM ...
> We have a small database with about 35 tables and 15
> users.  We have created a front-end application that
> filters the data so that the user only sees their
> information.  When testing the front-end and back-end
> response time, it works great on our company LAN; however,
> when testing it on a DSL through a VPN, the response time
> is unacceptable.  I have two contractors working on this
> and they have made several changes to combo boxes, sub-
> forms, etc., but it only helped in the local environment.
> Any suggestions?  I've been told to take it to SQL, but I
> don't want to do that without the guarantee that this will
> fix the problem.
>
> Thanks!
> Melody

6. Slow response time with ASP & Access **PLEASE HELP**

7. Slow response from MS access report

8. Very slow or no response in Access for linked Oracle database tab