Dcount in ADP

[Jeff]

I wrote some codes to help count the amount of each item sold within a period
of time, I use the following codes in my MDB and it works well

intItemAS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And [Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" & Me![EndDate] &
"'")

intItemAL = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And [Size]
= 'L' And [SoldDate] Between '" & Me![StartDate] & "' And '" & Me![EndDate] &
"'")

intItemBS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemB' And [Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" & Me![EndDate] &
"'")
…………………………….

Now, I have upsized my MDB to ADP using SQLserver 2000 as my database. The
codes still work in ADP, but I'd like to make it work faster. What I am
thinking is that I can build an in-line function to fetch the record set
within a set period of time first then let Dcount count the items in this
record set (in-line function). I tried but I can not get the first part work,
i.e. I don't know how to execute the in-line function first. Can anyone help
me with this? Thank you.

 

[Graham R Seach]

Jeff,

Domain aggregate functions are notoriously slow. Against SQL Server, it's
going to be faster to use Count (not DCount) within a recordset.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Sylvain Lafontaine]

When using ADP, Access should convert your DCount to a direct call to
Count() on the SQL-Server. (This is something that you could easily verify
using the SQL-Server Profiler. I've just made this quick verification
myself; however, I cannot vouch for you.)

If you have some speed problem, this is likely because you are missing some
index on the table tblSale. If you have access to the SQL-Server client
tools, then taking a look at the query plan with the Query Analyser should
help you in finding the source of the problem.

It is obvious that you cannot make things going faster by pulling all the
records over the network and doing the calculation yourself.

 

[Jeff]

Thank you for your help. I upsize my MDB to ADP once again, but Access still
doesn't convert DCount() to Count() for me. I know I may need to write a
user-defined function and I need time to read something about this. The
Function I need maybe something like:

CREATE FUNCTION dbo.fctCountItem()
@StartDate char(8),
@EndDate char(8)
@ intItemAS Integer OUTPUT
@ intItemAL Integer OUTPUT
@ intItemBS Integer OUTPUT
As
Select @intItemAS = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate
Select @intItemAL = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'L' And [SoldDate] Between @StartDate And @EndDate
Select @intItemBS = Count(ItemName) From tblSale Where [ItemName] =
'ItemB' And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

I got stuck in writing this Function. The problem I have now is how to pass
the parameters from the form to the Function and from the Function to the
form. Your help will be appreciated.

 

[Sylvain Lafontaine]

Count() is a SQL-Server function and cannot be called directly in Access'
VBA code or in forms, the function DCount must still be kept even after the
upsizing. It's when Access is making a call to the SQL-Server that the
DCount function is translated to the Count function.

For your function dbo.fctCountItem(), to my knowledge, you cannot call it
directly from VBA if this function doesn't return a table or a resultset and
the OUTPUT parameters are also useless in this context.

The easiest way to deal with SQL-Server from Access is to call a stored
procedure, not a function, and to have the results returned as part of a
Select query:

CREATE procedure dbo.prCountItem()
@StartDate char(8),
@EndDate char(8)

AS

Declare @intItemAS Integer
Declare @intItemAL Integer
Declare @intItemBS Integer

Select @intItemAS = Count(ItemName) From tblSale Where [ItemName] = 'ItemA'
And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

Select @intItemAL = Count(ItemName) From tblSale Where [ItemName] = 'ItemA'
And [Size] = 'L' And [SoldDate] Between @StartDate And @EndDate

Select @intItemBS = Count(ItemName) From tblSale Where [ItemName] = 'ItemB'
And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

Select @intItemAS as ItemAS, @intItemAL as ItemAL, @intItemBS as ItemBS

You can also use OUTPUT parameter with a stored procedure and call it from
Access but you will have to use the ADO's Command object associated with
Parameters objects but it's a little more complicated then using a Select
statement. See for example:

http://groups.google.ca/group/micro...422cf93130f934bc?tvc=1&hl=fr#422cf93130f934bc

The newsgroup for ADP is m.p.a.adp.sqlserver.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thank you for your help. I upsize my MDB to ADP once again, but Access
still
doesn't convert DCount() to Count() for me. I know I may need to write a
user-defined function and I need time to read something about this. The
Function I need maybe something like:

CREATE FUNCTION dbo.fctCountItem()
@StartDate char(8),
@EndDate char(8)
@ intItemAS Integer OUTPUT
@ intItemAL Integer OUTPUT
@ intItemBS Integer OUTPUT
As
Select @intItemAS = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'S' And [SoldDate] Between @StartDate And
@EndDate
Select @intItemAL = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'L' And [SoldDate] Between @StartDate And @EndDate
Select @intItemBS = Count(ItemName) From tblSale Where [ItemName] =
'ItemB' And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

I got stuck in writing this Function. The problem I have now is how to
pass
the parameters from the form to the Function and from the Function to the
form. Your help will be appreciated.

--
Jeff


"Jeff" ??:

I wrote some codes to help count the amount of each item sold within a
period
of time, I use the following codes in my MDB and it works well

intItemAS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemAL = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'L' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemBS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemB' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")
............

Now, I have upsized my MDB to ADP using SQLserver 2000 as my database.
The
codes still work in ADP, but I'd like to make it work faster. What I am
thinking is that I can build an in-line function to fetch the record set
within a set period of time first then let Dcount count the items in this
record set (in-line function). I tried but I can not get the first part
work,
i.e. I don't know how to execute the in-line function first. Can anyone
help
me with this? Thank you.

 

[Graham R Seach]

Jeff,

Change the function to a stored procedure, then use the following ADO code:
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "sp_CountItem"
.CommandType = adStoredProcedure
.Parameters.Refresh
.Parameters("@StartDate") = Me!StartDate
.Parameters("@EndDate") = Me!EndDate

Set rs = .Execute
Debug.Print .Parameters("@intItemAS")
Debug.Print .Parameters("@intItemAL")
Debug.Print .Parameters("@intItemBS")
End With

rs.Close
Set cmd = Nothing
Set rs = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you for your help. I upsize my MDB to ADP once again, but Access
still
doesn't convert DCount() to Count() for me. I know I may need to write a
user-defined function and I need time to read something about this. The
Function I need maybe something like:

CREATE FUNCTION dbo.fctCountItem()
@StartDate char(8),
@EndDate char(8)
@ intItemAS Integer OUTPUT
@ intItemAL Integer OUTPUT
@ intItemBS Integer OUTPUT
As
Select @intItemAS = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'S' And [SoldDate] Between @StartDate And
@EndDate
Select @intItemAL = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'L' And [SoldDate] Between @StartDate And @EndDate
Select @intItemBS = Count(ItemName) From tblSale Where [ItemName] =
'ItemB' And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

I got stuck in writing this Function. The problem I have now is how to
pass
the parameters from the form to the Function and from the Function to the
form. Your help will be appreciated.

--
Jeff


"Jeff" ??:

I wrote some codes to help count the amount of each item sold within a
period
of time, I use the following codes in my MDB and it works well

intItemAS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemAL = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'L' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemBS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemB' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")
............

Now, I have upsized my MDB to ADP using SQLserver 2000 as my database.
The
codes still work in ADP, but I'd like to make it work faster. What I am
thinking is that I can build an in-line function to fetch the record set
within a set period of time first then let Dcount count the items in this
record set (in-line function). I tried but I can not get the first part
work,
i.e. I don't know how to execute the in-line function first. Can anyone
help
me with this? Thank you.

 

[Jeff]

Hi Sylvain,

Thank you very much for your help. I don't know how to call a stored
procedure from Access & pass the returned parameters into the TextBox on my
form using your method. I am sorry, I am new with ADP. I am appreciated if
you can write me some more lines. Thanks.

--
Jeff


"Sylvain Lafontaine" 來函：

Count() is a SQL-Server function and cannot be called directly in Access'
VBA code or in forms, the function DCount must still be kept even after the
upsizing. It's when Access is making a call to the SQL-Server that the
DCount function is translated to the Count function.

For your function dbo.fctCountItem(), to my knowledge, you cannot call it
directly from VBA if this function doesn't return a table or a resultset and
the OUTPUT parameters are also useless in this context.

The easiest way to deal with SQL-Server from Access is to call a stored
procedure, not a function, and to have the results returned as part of a
Select query:

CREATE procedure dbo.prCountItem()
@StartDate char(8),
@EndDate char(8)

AS

Declare @intItemAS Integer
Declare @intItemAL Integer
Declare @intItemBS Integer

Select @intItemAS = Count(ItemName) From tblSale Where [ItemName] = 'ItemA'
And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

Select @intItemAL = Count(ItemName) From tblSale Where [ItemName] = 'ItemA'
And [Size] = 'L' And [SoldDate] Between @StartDate And @EndDate

Select @intItemBS = Count(ItemName) From tblSale Where [ItemName] = 'ItemB'
And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

Select @intItemAS as ItemAS, @intItemAL as ItemAL, @intItemBS as ItemBS

You can also use OUTPUT parameter with a stored procedure and call it from
Access but you will have to use the ADO's Command object associated with
Parameters objects but it's a little more complicated then using a Select
statement. See for example:

http://groups.google.ca/group/micro...422cf93130f934bc?tvc=1&hl=fr#422cf93130f934bc

The newsgroup for ADP is m.p.a.adp.sqlserver.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Thank you for your help. I upsize my MDB to ADP once again, but Access
still
doesn't convert DCount() to Count() for me. I know I may need to write a
user-defined function and I need time to read something about this. The
Function I need maybe something like:

CREATE FUNCTION dbo.fctCountItem()
@StartDate char(8),
@EndDate char(8)
@ intItemAS Integer OUTPUT
@ intItemAL Integer OUTPUT
@ intItemBS Integer OUTPUT
As
Select @intItemAS = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'S' And [SoldDate] Between @StartDate And
@EndDate
Select @intItemAL = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'L' And [SoldDate] Between @StartDate And @EndDate
Select @intItemBS = Count(ItemName) From tblSale Where [ItemName] =
'ItemB' And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

I got stuck in writing this Function. The problem I have now is how to
pass
the parameters from the form to the Function and from the Function to the
form. Your help will be appreciated.

--
Jeff


"Jeff" ??:

I wrote some codes to help count the amount of each item sold within a
period
of time, I use the following codes in my MDB and it works well

intItemAS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemAL = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'L' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemBS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemB' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")
............

Now, I have upsized my MDB to ADP using SQLserver 2000 as my database.
The
codes still work in ADP, but I'd like to make it work faster. What I am
thinking is that I can build an in-line function to fetch the record set
within a set period of time first then let Dcount count the items in this
record set (in-line function). I tried but I can not get the first part
work,
i.e. I don't know how to execute the in-line function first. Can anyone
help
me with this? Thank you.

 

[Jeff]

Hi Graham,
Thank you very much for your help. It works fine with your codes, but ERROR
3704 comes up when I run the code. The problem may have to do with line
rs.close. If I take this out, it's ok.

--
Jeff


"Graham R Seach" 來函：

Jeff,

Change the function to a stored procedure, then use the following ADO code:
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "sp_CountItem"
.CommandType = adStoredProcedure
.Parameters.Refresh
.Parameters("@StartDate") = Me!StartDate
.Parameters("@EndDate") = Me!EndDate

Set rs = .Execute
Debug.Print .Parameters("@intItemAS")
Debug.Print .Parameters("@intItemAL")
Debug.Print .Parameters("@intItemBS")
End With

rs.Close
Set cmd = Nothing
Set rs = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you for your help. I upsize my MDB to ADP once again, but Access
still
doesn't convert DCount() to Count() for me. I know I may need to write a
user-defined function and I need time to read something about this. The
Function I need maybe something like:

CREATE FUNCTION dbo.fctCountItem()
@StartDate char(8),
@EndDate char(8)
@ intItemAS Integer OUTPUT
@ intItemAL Integer OUTPUT
@ intItemBS Integer OUTPUT
As
Select @intItemAS = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'S' And [SoldDate] Between @StartDate And
@EndDate
Select @intItemAL = Count(ItemName) From tblSale Where [ItemName] =
'ItemA' And [Size] = 'L' And [SoldDate] Between @StartDate And @EndDate
Select @intItemBS = Count(ItemName) From tblSale Where [ItemName] =
'ItemB' And [Size] = 'S' And [SoldDate] Between @StartDate And @EndDate

I got stuck in writing this Function. The problem I have now is how to
pass
the parameters from the form to the Function and from the Function to the
form. Your help will be appreciated.

--
Jeff


"Jeff" ??:

I wrote some codes to help count the amount of each item sold within a
period
of time, I use the following codes in my MDB and it works well

intItemAS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemAL = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemA' And
[Size]
= 'L' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")

intItemBS = DCount("[ItemName]", "tblSale", "[ItemName] = 'ItemB' And
[Size]
= 'S' And [SoldDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] &
"'")
............

Now, I have upsized my MDB to ADP using SQLserver 2000 as my database.
The
codes still work in ADP, but I'd like to make it work faster. What I am
thinking is that I can build an in-line function to fetch the record set
within a set period of time first then let Dcount count the items in this
record set (in-line function). I tried but I can not get the first part
work,
i.e. I don't know how to execute the in-line function first. Can anyone
help
me with this? Thank you.