:
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).
