This works, yes - but why?

[Darryl Kerkeslager]

In the FE mdb I put two variables in a module:

Private selectedFromDate As Date
Private selectedToDate As Date

I create functions/subs:

Public Sub SetSelectedFromDate(fromDate As Date)
selectedFromDate = fromDate
End Sub

Public Sub SetSelectedToDate(toDate As Date)
selectedToDate = toDate
End Sub

Public Function GetSelectedFromDate() As Date
GetSelectedFromDate = selectedFromDate
End Function

Public Function GetSelectedToDate() As Date
GetSelectedToDate = selectedToDate
End Function

---------------------------------------------

In the BE mdb, I have a stored query:

SELECT off_name, cs_open_date
FROM offender INNER JOIN current_sup
ON offender.offender_id = current_sup.cs_off_id
WHERE current_sup.cs_open_date
Between GetSelectedFromDate() And GetSelectedToDate()
The mdb files are not linked; I simply use this to get the data:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM OpeningsbyDateRangeQuery IN ' " &
GetBackendPath() & " ' "
End Sub

So, how does this work? Does the CurrentProject.Connection cover any mdb
that it touches? How does the BE mdb know what the two functions are, since
they are only defined (and given value) in the FE?
Darryl Kerkeslager

[Ed Robichaud]

Explain how the FE and BE are not linked. Access can only deal with data
tables that are local/imported OR that are linked to at some known location.
-Ed

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:a5********************@comcast.com...

In the FE mdb I put two variables in a module:

Private selectedFromDate As Date
Private selectedToDate As Date

I create functions/subs:

Public Sub SetSelectedFromDate(fromDate As Date)
selectedFromDate = fromDate
End Sub

Public Sub SetSelectedToDate(toDate As Date)
selectedToDate = toDate
End Sub

Public Function GetSelectedFromDate() As Date
GetSelectedFromDate = selectedFromDate
End Function

Public Function GetSelectedToDate() As Date
GetSelectedToDate = selectedToDate
End Function

---------------------------------------------

In the BE mdb, I have a stored query:

SELECT off_name, cs_open_date
FROM offender INNER JOIN current_sup
ON offender.offender_id = current_sup.cs_off_id
WHERE current_sup.cs_open_date
Between GetSelectedFromDate() And GetSelectedToDate()
The mdb files are not linked; I simply use this to get the data:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM OpeningsbyDateRangeQuery IN ' " &
GetBackendPath() & " ' "
End Sub

So, how does this work? Does the CurrentProject.Connection cover any mdb
that it touches? How does the BE mdb know what the two functions are,
since
they are only defined (and given value) in the FE?
Darryl Kerkeslager

[Rick Brandt]

Ed Robichaud wrote:

Explain how the FE and BE are not linked. Access can only deal with
data tables that are local/imported OR that are linked to at some
known location. -Ed
Incorrect. If you look at his code again it is specifying the path to an
external file as part of the SQL. In that case no link is required.
"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:a5********************@comcast.com...

In the FE mdb I put two variables in a module:

Private selectedFromDate As Date
Private selectedToDate As Date

I create functions/subs:

Public Sub SetSelectedFromDate(fromDate As Date)
selectedFromDate = fromDate
End Sub

Public Sub SetSelectedToDate(toDate As Date)
selectedToDate = toDate
End Sub

Public Function GetSelectedFromDate() As Date
GetSelectedFromDate = selectedFromDate
End Function

Public Function GetSelectedToDate() As Date
GetSelectedToDate = selectedToDate
End Function

---------------------------------------------

In the BE mdb, I have a stored query:

SELECT off_name, cs_open_date
FROM offender INNER JOIN current_sup
ON offender.offender_id = current_sup.cs_off_id
WHERE current_sup.cs_open_date
Between GetSelectedFromDate() And GetSelectedToDate()
The mdb files are not linked; I simply use this to get the data:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM OpeningsbyDateRangeQuery IN ' " &
GetBackendPath() & " ' "
End Sub

So, how does this work? Does the CurrentProject.Connection cover
any mdb that it touches? How does the BE mdb know what the two
functions are, since
they are only defined (and given value) in the FE?
Darryl Kerkeslager

[David Schofield]

On Tue, 11 Jan 2005 08:43:50 -0500, "Darryl Kerkeslager"
<Ke*********@comcast.net> wrote:

In the FE mdb I put two variables in a module:

Private selectedFromDate As Date
Private selectedToDate As Date

I create functions/subs:

Public Sub SetSelectedFromDate(fromDate As Date)
selectedFromDate = fromDate
End Sub

Public Sub SetSelectedToDate(toDate As Date)
selectedToDate = toDate
End Sub

Public Function GetSelectedFromDate() As Date
GetSelectedFromDate = selectedFromDate
End Function

Public Function GetSelectedToDate() As Date
GetSelectedToDate = selectedToDate
End Function

---------------------------------------------

In the BE mdb, I have a stored query:

SELECT off_name, cs_open_date
FROM offender INNER JOIN current_sup
ON offender.offender_id = current_sup.cs_off_id
WHERE current_sup.cs_open_date
Between GetSelectedFromDate() And GetSelectedToDate()
The mdb files are not linked; I simply use this to get the data:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM OpeningsbyDateRangeQuery IN ' " &
GetBackendPath() & " ' "
End Sub

So, how does this work? Does the CurrentProject.Connection cover any mdb
that it touches? How does the BE mdb know what the two functions are, since
they are only defined (and given value) in the FE?
Darryl Kerkeslager

Hi
Surely code is only ever run in the FE. If the functions were in the
BE database you could run the query there but not from the FE
database.
A stored query is not a stored procedure.
David

[Darryl Kerkeslager]

"David Schofield" <d.***************@blueyonder.co.uk> wrote

Surely code is only ever run in the FE. If the functions were in the
BE database you could run the query there but not from the FE
database.
A stored query is not a stored procedure.
David

Yes, actually I do run some code from the BE, on Saturday, when nobody else
is around ... I copy the three mdb files from the separate LANs, back up
the mdbs, delete the accumulated error log entries (sometimes I even find
the cause of the errors), run some code to find bad data entry and combine
new data from the three mdb files, and compact & repair. Then I send the mdb
carelessly back over the WAN to the remote sites. Hey, that's what backups
are for. While I *could* have a separate db to use as a FE just for my
Saturday dba functions, it is so much easier to just open each of the BE db,
do my work directly, and send thenm off.

The two functions in my original post are not stored in the BE, just
included in a saved/stored query (Access mdb stored query, not SQL Server
stored procedure). If I were to try to view the results of the query
directly from the BE, it errors with "undefined function". It only works
when the FE accesses the BE query - and then, "magically", the BE not only
knows about the function, but returns the result (from the FE) to the BE
stored query, which uses it to select the appropriate rows.

So how does it work?
Darryl Kerkeslager

[David Schofield]

On Tue, 11 Jan 2005 08:43:50 -0500, "Darryl Kerkeslager"
<Ke*********@comcast.net> wrote:

In the FE mdb I put two variables in a module:

Private selectedFromDate As Date
Private selectedToDate As Date

I create functions/subs:

Public Sub SetSelectedFromDate(fromDate As Date)
selectedFromDate = fromDate
End Sub

Public Sub SetSelectedToDate(toDate As Date)
selectedToDate = toDate
End Sub

Public Function GetSelectedFromDate() As Date
GetSelectedFromDate = selectedFromDate
End Function

Public Function GetSelectedToDate() As Date
GetSelectedToDate = selectedToDate
End Function

---------------------------------------------

In the BE mdb, I have a stored query:

SELECT off_name, cs_open_date
FROM offender INNER JOIN current_sup
ON offender.offender_id = current_sup.cs_off_id
WHERE current_sup.cs_open_date
Between GetSelectedFromDate() And GetSelectedToDate()
The mdb files are not linked; I simply use this to get the data:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM OpeningsbyDateRangeQuery IN ' " &
GetBackendPath() & " ' "
End Sub

So, how does this work? Does the CurrentProject.Connection cover any mdb
that it touches? How does the BE mdb know what the two functions are, since
they are only defined (and given value) in the FE?
Darryl Kerkeslager

Hi
Presumably the FE retrieves the (compiled) query from the BE and
executes it. If any of the function arguments depend on a field in the
base table, the whole table would have to be passed to the FE for
processing.
In the case of ODBCconnections you can see this sort of thing by
running with SQL tracing on.
I am actually replying to your response lower down but this hasn't
arrived at Newscene yet.
David

[Darryl Kerkeslager]

"David Schofield" <d.***************@blueyonder.co.uk> wrote

Presumably the FE retrieves the (compiled) query from the BE and
executes it.

so based on this, if the same query is placed in the FE, the code should
execute faster, because the step of retrieving the query is eliminated.
Darryl Kerkeslager

[David Schofield]

On Wed, 12 Jan 2005 21:06:28 -0500, "Darryl Kerkeslager"
<Ke*********@comcast.net> wrote:

"David Schofield" <d.***************@blueyonder.co.uk> wrote

Presumably the FE retrieves the (compiled) query from the BE and
executes it.

so based on this, if the same query is placed in the FE, the code should
execute faster, because the step of retrieving the query is eliminated.
Darryl Kerkeslager

Yes, but maybe this only applies if user functions are involved.

I have never used Access user functions in a query on a non-JET
backend and didn't even realise you could (if indeed you can, I am at
presently home and can't test it). However it shows why views/pass
though queries or stored procedures, which operate on the server, are
faster - especially if a function has to be evaluated on a per-record
basis.
David

[Darryl Kerkeslager]

"David Schofield" <d.***************@blueyonder.co.uk> wrote

Yes, but maybe this only applies if user functions are involved.

I have never used Access user functions in a query on a non-JET
backend and didn't even realise you could (if indeed you can, I am at
presently home and can't test it). However it shows why views/pass
though queries or stored procedures, which operate on the server, are
faster - especially if a function has to be evaluated on a per-record
basis.

I *am* using a Jet BE mdb file. The setup I described in the OP operates
in as close to zero time as one would expect. The only difference I did
notice when I timed it was that the query ran slower the first time - so I
assume that (somewhere) the query was optimized the first time, and operated
at optimized speed for additional calls. I am 99.854% certain that the
query is only returning the needed records to the FE.
Darryl Kerkeslager

[David Schofield]

On Thu, 13 Jan 2005 17:07:53 -0500, "Darryl Kerkeslager"
<Ke*********@comcast.net> wrote:

"David Schofield" <d.***************@blueyonder.co.uk> wrote

Yes, but maybe this only applies if user functions are involved.

I have never used Access user functions in a query on a non-JET
backend and didn't even realise you could (if indeed you can, I am at
presently home and can't test it). However it shows why views/pass
though queries or stored procedures, which operate on the server, are
faster - especially if a function has to be evaluated on a per-record
basis.

I *am* using a Jet BE mdb file. The setup I described in the OP operates
in as close to zero time as one would expect. The only difference I did
notice when I timed it was that the query ran slower the first time - so I
assume that (somewhere) the query was optimized the first time, and operated
at optimized speed for additional calls. I am 99.854% certain that the
query is only returning the needed records to the FE.
Darryl Kerkeslager

Hi
my reply to this seems to have vanished, anyway here is a fuller
version

Your criterion functions do not depend on the values of individual
records and so only need to be evaluated once. The time this takes and
the time for transmitting the query and these values between FE and BE
are negligible and in this case only the selected records will be sent
to the FE.

If your criterion function had an argument which depends on the value
of each record, I believe the whole set of records would be sent to
the FE and the selection done there. After all, the only other way
this could be done would be to send the function in some way from the
FE to the BE. But I think the back end is actually only invoking JET
and cannot understand code at all,

I mentioned other back ends only as an aside. If you do use an
ODBC-connected backend and switch on TRACE you can study how the query
optimiser allocates work between FE and BE However in these cases if
you need a function you would try to use a passthrough query with a
BE native SQL function or a stored procedure and not an Access
function.

David