MS DB engines

I used to develop in Access but have been retired for 8 years. I may have
to come out of retirement to help a non-profit body who cannot afford market
prices. I have a feeling this is the first of many questions as I struggle
to recall what I have forgotten in that time much less learn what has
happened since (sigh). I have considerable experience with JET, a moderate
amount with SQLserver and know nothing about ACE.

Where can I read a reasonably unbiased comparison of ACE, JET and the 'home'
version of SQLserver (or whatever it is called these days)?

David Hare-Scott

>I have considerable experience with JET, a moderate amount with SQLserver
>and know nothing about ACE.
>
>Where can I read a reasonably unbiased comparison of ACE, JET and the
>'home' version of SQLserver (or whatever it is called these days)?
>

A great question.

And the answer in terms of Access data engines?

No change in what you did back then is required.

So about the only thing important here is that ACE is the new default data
engine that Access uses. This new data engine is based based on the same
code base as JET.

So just like in the past, most access users and developers did not really
have to give this issue much consideration or thought.

So for a small workgroup of a few users, there's not really a requirement to
use SQL server. However there are number free editions of SQL server
available today, and their generally termed "express" editions. And like in
the past, they tend to have most of the major features of SQL server, and
their restrictions are based in terms of the maximum data file size. And
like in the past, Access is well able to use these server based database
systems.

The maximum data file size for Access applications is 2 gigabytes and
remains un-changed from 8 years ago. For SQL server express, until very
recently, it was the same as access, but there's now a new edition called
SQL server 2008 express "R2". This edition does allow file sizes up to 10
GB.

So for the vast majority of typical access applications, just like when you
used it in the past, there was little requirement or need to use a server
based database system for your data. However just like in the past such an
option does exist for typical access development and is available as a
choice if you so require. So you can utilize SQL server with Access, but in
the vast majority of typical access applications such a choice and effort on
your part is not required.

So from an Access developer point of view little has changed from the past.
You do not need to re-learn or change in terms of development habits from
the data engine point of view. you don't have to change your code.

In fact, the only main advice I can give in regards to data technologies in
Access is to continue to use the default data object model called DAO.
Access 2000, and 2002 had the default object model changed from DAO to ADO.
Prior to access 2000, the longtime and preferred choice of most access
developers was to use the default data model of DAO.

Thankfully in access 2003, the DAO "default" returned as the default data a
object model and continues so to this day. In other words the Access
developer community ignored the new data object model being forced to on
the developer community, and thankfully the folks in Redmond came to their
senses.

So in summary:

The good practices and general approach is to software development in Access
that you used with JET is unchanged in regards to using the newer data
engine called ACE. All of that old code and development practices is 100%
compatible and in fact remains unchanged.

In fact the only exception and feature that has been depreciated to my
knowledge is what's called "JET" direct workspaces, and they were rarely
used anyway.

Furthermore the new ACE data engine has no problem opening and using older
format mdb files.

So I can safe say that what you did eight years ago in access with typical
development practices continues to work the same as today.
The same goes for the VBA coding and programming language inside of access,
it also remains unchanged and is 100% compatible with code you wrote back
then.

In fact the larger changes are not with a data engine, but with all of the
changes to the user interface and the introduction of things like the
ribbon.
So all your habits of coding and writing software and using the jet engine
remain intact, it'll just be a bit of time to find where things are in the
product in regards to user interface.

A for more quick rundowns of the major changes to be aware of in ACE over
that of JET:

For the new accDB file format as opposed to mdb, ACE does not support user
level security.
(however with ACE you can still use user level security with the older mdb
file format). And once again, if you never were utilizing user level
security in the past, then this change probably will not affect your
development practices or change anything in regards to access development
practices.

Database triggers and store procedures:
The new version of the access database engine now has support for table
level events and store procedures. I should point out that these new
triggers and procedures run independently of MS access itself. If you thus
open up a Access database file with ODBC drivers from FoxPro, or VB.net or
any different product, the table procedures and triggers will continue to
function. In fact these new table procedures and triggers will even run in
the database on computers or access has not been installed (but like in the
past, you will need a version of the data engine installed on that
computer).

The other significant aspect of these new database triggers is the ability
of Access publishing to the web. When you publish your access applications
to the web, these new store procedures and database triggers do make the
trip up into the cloud, and will now run server side on SharePoint or office
365. This new web based system is referred to as Access Web Services. These
new data triggers are not written in VBA, but in a new data macro language.

However once again, these new table triggers or the new web features of the
product are not required to be utilized for traditional access development
in ANY way. And again from what you did eight years ago will 100% apply to
your typical approach in developing today.

However if you want to see these new web features in action, I have two
videos posted here that will give you a feel for some of the features and
abilities of the web aspects of access:

http://www.youtube.com/playlist?list...5&feature=plcp

So in a nutshell, from the data point of view, what you learned and utilized
in the past, will remain unchanged today. There are not real "development"
practice changes required in use ACE over that of JET.

And as noted, the larger changes is the new user interface inside of access.
And we do now have the choice and possibility of creating and developing
applications that run 100% inside of a web browser. As noted to publish such
applications, you need SharePoint, or now the low-cost office 365.

While the above is not a lot of information, at the end of the day keep in
mind what you learned in the past in regard to access still very much
applies today.

Do feel free to ask for any follow-up questions here.

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

Albert D. Kallal wrote:
>> I have considerable experience with JET, a moderate amount with
>> SQLserver and know nothing about ACE.
>>
>> Where can I read a reasonably unbiased comparison of ACE, JET and the
>> 'home' version of SQLserver (or whatever it is called these days)?
>>
>
> A great question.
>
> And the answer in terms of Access data engines?
>
> No change in what you did back then is required.
>
> So about the only thing important here is that ACE is the new default
> data engine that Access uses. This new data engine is based based on
> the same code base as JET.
>
> So just like in the past, most access users and developers did not
> really have to give this issue much consideration or thought.
>
> So for a small workgroup of a few users, there's not really a
> requirement to use SQL server. However there are number free editions
> of SQL server available today, and their generally termed "express"
> editions. And like in the past, they tend to have most of the major
> features of SQL server, and their restrictions are based in terms of
> the maximum data file size. And like in the past, Access is well able
> to use these server based database systems.
>
> The maximum data file size for Access applications is 2 gigabytes and
> remains un-changed from 8 years ago. For SQL server express, until
> very recently, it was the same as access, but there's now a new
> edition called SQL server 2008 express "R2". This edition does allow
> file sizes up to 10 GB.
>
> So for the vast majority of typical access applications, just like
> when you used it in the past, there was little requirement or need to
> use a server based database system for your data. However just like
> in the past such an option does exist for typical access development
> and is available as a choice if you so require. So you can utilize
> SQL server with Access, but in the vast majority of typical access
> applications such a choice and effort on your part is not required.
>
> So from an Access developer point of view little has changed from the
> past. You do not need to re-learn or change in terms of development
> habits from the data engine point of view. you don't have to change
> your code.
> In fact, the only main advice I can give in regards to data
> technologies in Access is to continue to use the default data object
> model called DAO. Access 2000, and 2002 had the default object model
> changed from DAO to ADO. Prior to access 2000, the longtime and
> preferred choice of most access developers was to use the default
> data model of DAO.
> Thankfully in access 2003, the DAO "default" returned as the default
> data a object model and continues so to this day. In other words the
> Access developer community ignored the new data object model being
> forced to on the developer community, and thankfully the folks in
> Redmond came to their senses.
>
> So in summary:
>
> The good practices and general approach is to software development in
> Access that you used with JET is unchanged in regards to using the
> newer data engine called ACE. All of that old code and development
> practices is 100% compatible and in fact remains unchanged.
>
> In fact the only exception and feature that has been depreciated to my
> knowledge is what's called "JET" direct workspaces, and they were
> rarely used anyway.
>
> Furthermore the new ACE data engine has no problem opening and using
> older format mdb files.
>
> So I can safe say that what you did eight years ago in access with
> typical development practices continues to work the same as today.
> The same goes for the VBA coding and programming language inside of
> access, it also remains unchanged and is 100% compatible with code
> you wrote back then.
>
> In fact the larger changes are not with a data engine, but with all
> of the changes to the user interface and the introduction of things
> like the ribbon.
> So all your habits of coding and writing software and using the jet
> engine remain intact, it'll just be a bit of time to find where
> things are in the product in regards to user interface.
>
> A for more quick rundowns of the major changes to be aware of in ACE
> over that of JET:
>
> For the new accDB file format as opposed to mdb, ACE does not support
> user level security.
> (however with ACE you can still use user level security with the
> older mdb file format). And once again, if you never were utilizing
> user level security in the past, then this change probably will not
> affect your development practices or change anything in regards to
> access development practices.
>
> Database triggers and store procedures:
> The new version of the access database engine now has support for
> table level events and store procedures. I should point out that
> these new triggers and procedures run independently of MS access
> itself. If you thus open up a Access database file with ODBC drivers
> from FoxPro, or VB.net or any different product, the table procedures
> and triggers will continue to function. In fact these new table
> procedures and triggers will even run in the database on computers or
> access has not been installed (but like in the past, you will need a
> version of the data engine installed on that computer).
>
> The other significant aspect of these new database triggers is the
> ability of Access publishing to the web. When you publish your access
> applications to the web, these new store procedures and database
> triggers do make the trip up into the cloud, and will now run server
> side on SharePoint or office 365. This new web based system is
> referred to as Access Web Services. These new data triggers are not
> written in VBA, but in a new data macro language.
> However once again, these new table triggers or the new web features
> of the product are not required to be utilized for traditional access
> development in ANY way. And again from what you did eight years ago
> will 100% apply to your typical approach in developing today.
>
> However if you want to see these new web features in action, I have
> two videos posted here that will give you a feel for some of the
> features and abilities of the web aspects of access:
>
> http://www.youtube.com/playlist?list...5&feature=plcp
>
> So in a nutshell, from the data point of view, what you learned and
> utilized in the past, will remain unchanged today. There are not
> real "development" practice changes required in use ACE over that of
> JET.
> And as noted, the larger changes is the new user interface inside of
> access. And we do now have the choice and possibility of creating and
> developing applications that run 100% inside of a web browser. As
> noted to publish such applications, you need SharePoint, or now the
> low-cost office 365.
> While the above is not a lot of information, at the end of the day
> keep in mind what you learned in the past in regard to access still
> very much applies today.
>
> Do feel free to ask for any follow-up questions here.

Thanks, it is (slightly) comforting to know that all has not changed beyond
recognition. Some supplementary questions:

I take it that ACE is a file server and so needs no software support on a
LAN server and that the normal split FE/BE for JET design is still
appropriate?

Say I buy Office 2010, including whatever package has Access and the Access
runtime builder in it for my development machine. If I have an networked
office environment with various versions of Windows (say XP SP3 or higher)
and possibly some installations of Office (versions uncertain) but not
Access. Can I then install Access runtimes on these machines? Will the
installation go smoothly with no side effects or will it tromp all over some
of the machines as used to happen back in the dark ages of Access 2000 etc?

David

On Wed, 7 Mar 2012 09:14:41 +1100, "David Hare-Scott"
<secret@nospam.com> wrote:

>I take it that ACE is a file server and so needs no software support on a
>LAN server and that the normal split FE/BE for JET design is still
>appropriate?

No. Ace is the new Jet with support for the new field datatypes found
in ACCDB files. So it comes with A2007 and newer but is downloadable
separately if required.

>Say I buy Office 2010, including whatever package has Access and the Access
>runtime builder in it for my development machine. If I have an networked
>office environment with various versions of Windows (say XP SP3 or higher)
>and possibly some installations of Office (versions uncertain) but not
>Access. Can I then install Access runtimes on these machines? Will the
>installation go smoothly with no side effects or will it tromp all over some
>of the machines as used to happen back in the dark ages of Access 2000 etc?

My experience has been that Access runtime installs were very
troublesome if other versions of Access were already on the same
system. But if none were found it was quite trouble free.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

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

>> Do feel free to ask for any follow-up questions here.

>Thanks, it is (slightly) comforting to know that all has not changed beyond
>recognition. Some supplementary questions:
>
>I take it that ACE is a file server and so needs no software support on a
>LAN server and that the normal split FE/BE for JET design is still
>appropriate?

Correct. In fact even the new store procedures and table triggers continue
work, and do so with only a 'file' sitting in some shared folder.
So yes - the basic file server setup continues to work as before and since
you have knowledge of the "split" concept, then having spent the time to
learn this concept still applies today.

>Say I buy Office 2010, including whatever package has Access and the Access
>runtime builder in it for my development machine. If I have an networked
>office environment with various versions of Windows (say XP SP3 or higher)
>and possibly some installations of Office (versions uncertain) but not
>Access. Can I then install Access runtimes on these machines? Will the
>installation go smoothly with no side effects or will it tromp all over
>some of the machines as used to happen back in the dark ages of Access 2000
>etc?
>
>David

Yes, I can well safe state the above is a fine assumption on your part.
And do note that the Access runtime for 2010 is free and included with
Access 2010.

So in the above, without other versions of Access, then the runtime install
for Access 2010 should be rather trouble free and easy.

You just install the runtime, and from that point out you just need to copy
your application(s) to those target computers and you should be just fine.
You will of course want to have tested or used your application in a runtime
environment. However, once again, the runtime from 2003, or how the 2010
runtime works is again much the same.

So you need to ensure you have your own startup forms and provide the basic
user interface (which a lot of Access applications do). You don't really
need to provide custom ribbons, but you might create or grab one for
reports.

--
Albert D. Kallal
Edmonton, Alberta Canada
PleaseNoSpam_kallal@msn.com