BE Access Version - Does it Matter

[pdh...@gmail.com]

I have a couple of quite old BEs - that have watched their FE's migrate from 97 to 2003 to 2010. I've never bothered to update the BEs - what are they losing out on ?

Regards
Paul Hayward

[Ron Paii]

<pdh...@gmail.com> wrote in message
news:4aefeff1-a7d5-44d5...@googlegroups.com...

I updated my 97 BE to 2010 to use RTF support in memo fields. Also 2010
allows for larger file size which was handy on my archives.

[Phil]

Also data macros. Never used them though, Are they any use?

Phil

[PW]

On Fri, 22 Mar 2013 17:35:19 GMT, "Phil" <ph...@stantonfamily.co.uk>
wrote:

Aren't they required for Sharepoint? Got me. Albert would know.

-paulw

[Albert D. Kallal]

"Phil" wrote in message news:kii4op$cr8$1...@speranza.aioe.org...

>> I updated my 97 BE to 2010 to use RTF support in memo fields. Also 2010
>> allows for larger file size which was handy on my archives.
>>
>>
>
>Also data macros. Never used them though, Are they any use?
>
>Phil

Access 2010 is the first version of Access to support table triggers and
store procedures. And those table triggers use the new macro language (they
are not VBA code).

So if you looking to use store procedures or table triggers, then the macros
can be rather useful.

The new macros also are used for when you build web forms in 2010.

So if you not using web stuff, then the main reason to use macros would be
if you wanted to use table events or what are more common called table
triggers and store procedures. So using table triggers and store procedure
does not require web based and is a feature that can be used in 100 desktop
+ VBA applications.

Best regards,

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

[David Hare-Scott]

Albert D. Kallal wrote:
> "Phil" wrote in message news:kii4op$cr8$1...@speranza.aioe.org...
>
>>> I updated my 97 BE to 2010 to use RTF support in memo fields. Also
>>> 2010 allows for larger file size which was handy on my archives.
>>>
>>>
>>
>> Also data macros. Never used them though, Are they any use?
>>
>> Phil
>
> Access 2010 is the first version of Access to support table triggers
> and store procedures. And those table triggers use the new macro
> language (they are not VBA code).
>
> So if you looking to use store procedures or table triggers, then the
> macros can be rather useful.
>

Isn't it a little confusing to call them 'stored procedures' when they still
run on the client?

David

[Albert D. Kallal]

"David Hare-Scott" wrote in message news:kinrsv$444$1...@news.albasani.net...

>> So if you looking to use store procedures or table triggers, then the
>> macros can be rather useful.
>>
>
>Isn't it a little confusing to call them 'stored procedures' when they
>still run on the client?
>
>David

I suppose the issue of where the store procedure runs is a fair point.

However, the general definition of a store procedure is something that runs
as the database engine level, not in code or at the UI level.

Since JET/ACE is a file based data engine, then all processing runs at the
client level.

The issue of that data engine having or not having store procedures would
not for any reason I can think of invalidate this issue.

If such store procedures did not run if you opened the database with VB6, or
FoxPro, then I would much agree.

However, no matter what data connection you use, and that includes non
Access connections, then the store procedure code runs.

So, I think as pure and fair definition of table triggers + store
procedures?

They are true and real store procedure and trigger code.

While there are several file based data engines that have table triggers, I
not aware of any that can do so in a multi-user environment like ACE can.

Best regards,

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

PleaseNoS...@msn.com

[pdh...@gmail.com]

Albert wrote:
>Since JET/ACE is a file based data engine, then all processing runs at the
client level.

I have always assumed that a fair amount of processing runs in the BE - for example: all indexing and queries on only BE tables. Add to that any checking of BE table relationships and table-field-validation. I guess I had also hoped / assumed that much of the DCount / DLookup work was handed over to the BE to process as well.

Is any of that true ?

Further to the discussion of the new A2010 Stored Procedures, if they are not supported on link tables (FAQ - blogs.office.com) then I need help to understand how they would be used with a usual multi-user FE / BE setup.

Regards
Paul

[Albert D. Kallal]

wrote in message
news:43acb117-d397-41db...@googlegroups.com...

>Albert wrote:
>>Since JET/ACE is a file based data engine, then all processing runs at the
>client level.
>
>I have always assumed that a fair amount of processing runs in the BE - for
>example: all indexing and queries on only BE tables.

No, the processing thread runs on your computer. You can place the back end
on a jump drive, a external USB drive or a shared folder on the server. That
server, or USB drive has no "processing" and MOST important that USB jump
drive does not have any software installed and running on that drive.

>Add to that any checking of BE table relationships and
>table-field-validation. I guess I had also hoped / assumed that much of the
>DCount / DLookup work was handed over to the BE to process as well.
>
>Is any of that true ?

No see above. the back is ONLY a file. It is no different then a text file,
a excel file or any old "Plain Jane" windows file. The front end opens that
file. The only work that occurs server side is the disk read, but nothing
other then a file read occurs.

If indexing or other tasks were occurring server side, then when did such
software get installed server side? Placing word, or an Access mdb file into
some folder does not magic install software on that system. The code +
software runs "where" you have Access installed.

>Further to the discussion of the new A2010 Stored Procedures, if they are
>not supported on link tables (FAQ - blogs.office.com)
>then I need help to understand how they would be used with a usual
>multi-user FE / BE setup.

Store procedures cannot be called from VBA or forms if you split. However,
if you don't split then you can use them.

However, while store procedures cannot be called from forms or VBA when
split, the table triggers DO work and THEY CAN call store procedures.

In MOST cases your table triggers will call store procedures, not your VBA
or forms.

So in the classic sense in which you have a table triggers calling some
store procedure code?

This setup works even if you split your database.

So the only restriction here is that your front end cannot call store
procedure code WHEN split.

However the table triggers you have in the BE WILL fire, do run.
And when split those triggers can call store procedures in the back end.

So if you comparing a typical access front end, in "most" cases it is likely
the table triggers are calling store procedure code and not your front end
forms + VBA code will calling those procedures.

As noted, in the case that your front end does need to call store procedure
code? Just re-write that code as VBA. So you don't really loose much by
splitting here.

So in summary:

non split database:
Both forms + VBA + table triggers can ALL FREELY call store procedure code
(named data macros).
Data macros can call VBA code.

split:
Table triggers continue to fire, table triggers can call store procedure
code.
Forms + VBA code cannot call store procedure code.
Data macros can call VBA code - that code MUST be in your front end!

So as per above your table triggers (and store procedures) in the back end
can call VBA code if you want - and that VBA code (must) resides in your
front end!!

So at the end of the day, I see little advantage to the fact that when split
your forms + VBA cannot call store procedures - it is usually easy to
re-write code in VBA and call that code. And in both cases the table trigger
can call VBA code.

So the ONLY feature you miss is forms + VBA being able to directly call a
saved store procedure (named data macro).

And as noted, you can in fact have triggers call store procedures and even
VBA code. When you do this, the data macros (store procedures) must be in
the back end.
However, any store procedure or trigger code that calls VBA does require
that VBA exist in the front end.

I would as a general rule avoid having macros call VBA code. If you do this,
then I would for maintains place a copy of the VBA code in both front end
and back end. Since if you open a back end to edit some data - it will
attempt to call the VBA routines which are now not available.

Best regards,

--

[David Hare-Scott]

pdh...@gmail.com wrote:
> Albert wrote:
>> Since JET/ACE is a file based data engine, then all processing runs
>> at the
> client level.
>
> I have always assumed that a fair amount of processing runs in the BE
> - for example: all indexing and queries on only BE tables. Add to
> that any checking of BE table relationships and
> table-field-validation. I guess I had also hoped / assumed that much
> of the DCount / DLookup work was handed over to the BE to process as
> well.
>
> Is any of that true ?
>

The only thing the BE machine provides is file services, that is it responds
over the network to OS requests for files or segments of files. There is no
DB management process running on the BE as with SQL Server. This is one
reason Access doesn't scale well with a native BE, it loads the network more
heavily. Purists from other DB backgrounds will use this to call Access a
toy. With careful design you can have a couple of dozen concurrent users on
a JET BE and then you can go to SQL Server BE and have lots more. You don't
get an enterprise level DB for a few hundred dollars and no per-seat or
per-connection license fee.

Take the case of an indexed search. With an JET/ACE BE the FE sends a
request for the whole index over the network, when it gets the index the FE
searches the index for the key, then it requests the record matching the key
from the BE and the BE returns it. With a SQL Server BE a pass through
query from the FE sends the key value and a request for the record, the BE
extracts the record from the DB and returns it. over the network

With a non-indexed search JET/ACE sends even more over the wire so the FE
can do a table scan.

David

[Albert D. Kallal]

"David Hare-Scott" wrote in message news:kjd6vd$2u9$1...@news.albasani.net...

>Take the case of an indexed search. With an JET/ACE BE the FE sends a
>request for the whole index over the network, when it gets the index the FE
>searches the index for the key, then it requests the record matching the
>key
>from the BE and the BE returns it.

Actually, the whole index is not read - this is far too slow. The index is
balanced b-tree.

A starting frame is read, and then a left/right traversing of the balanced
tree begins.
So you pulling one index frame at a time.

On a million row table, the index can become quite large - larger then a
small database.

With a balanced b-tree, then likely about less then 10 frames are pulled.
This is quite small in terms of data.
In fact this explains why a search to pull one record from accDB back end on
a network drive with 10 rows, or a ONE million rows returns the record
INSTANT!

If a million rows of indexing had to be pulled in, then your reading a
million rows of data and keys - far far too slow.

And in fact SQL server indexing also works the same - the server dos not
pull all keys into ram, but traverses the btree if a index is available.

>With a SQL Server BE a pass through
>query from the FE sends the key value and a request for the record, the BE
>extracts the record from the DB and returns it. over the network

Sure, ultimate the above occurs. In fact a WHOLE SQL string is sent. The
server then processes that SQL and after building a query plan THEN starts
the binary traverse of the index and pulls the record. And this traversing
process is VERY similar to what JET does (whole index is NOT read). However,
as noted with SQL server even without a index and a forced full table scan?
During such a process you hammering the disk drive, but no network chatter
is occurring since as you well point out such processing is and can run
server side.

However, "when" a index can be used? There not huge difference in network
chatter here. The difference here is only going to be a few frames of data
when using Access (usually less then 10 frames). So only a VERY small part
of the index is send down the wire.

The amount of data is not signification for this retrieval, but when you
start going beyond 10-15 users, such small amounts of network traffic can
start to add up. Where the really big gains in sql server occur is with
table joins + aggregates, and that of updates when they can run 100% server
side.

Last but not least:
In the case of a JET backend? ALL data updates to rows must make a full
round trip to client and back to the file on the back end. With SQL server
one can update rows of data without the need for a round trip.

In most cases for a simple bound data form? Then a round trip is implied and
this again explains how Access can give SQL server a run for the money on a
network with reasonable performance.

Best regards,
--

[pdh...@gmail.com]

Albert & David

Thank you for this - very illuminating.
I should have understood it - but wooly thinking / wishful thinking was blocking my assembling the bits of knowledge that I had ;-)

I have never, ever, had a BE working from a non-MS platform... so my nose has never been rubbed in the fact that no MS processes were triggered or started or run by a BE being used. As you have said, a BE will presumably work from a NAS server or a Linux box - so it needs no hidden plumbing courtesy of a MS OS. The fact that an empty accdb container bulks nearly a third of a megabyte is not indicative of great internal intelligence - just of a rather loose container ;-)

It hurts to realise that, for many situations, no matter how smart my structures & query, all the records must come down the wire to be locally filtered by a WHERE clause (ie: one of those dreadful 'Like "*Fred*"' ones ;-). It will probably cause me to be a little less strict in my application of RDBS rules - more things will probably get calculated / assembled & stored & indexed in the BE - rather than doing it 'on-the-fly'.

Best Regards
Paul