You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
In the first Access blog post, I noted that Access 12 has a new database engine, but one derived from and very similar to the old Jet engine. There were a bunch of questions & emails about what we did, why, and what it means, and it will take me more than one post to get through everything so please be patient. In this post, I’ll cover what how the database engine fits into Access and how the new is different from Jet. In future posts, I’ll talk about the Access data security model and other engine-level stuff.
Access differs from most other database authoring tools by storing everything in a single file. When an Access author creates a database, the data and all the UI to work with that data all live in the same “.MDB” file. This makes database installation very simple and enables end-users to work with Access databases in just the same way they use Excel spreadsheets or Word documents. The engine provides 3 things to Access: somewhere to put data, somewhere to put UI forms and reports (this is really just more data), and a query processor.
In past versions, Access has used the Microsoft Jet database engine for data storage and query processing. Jet is commonly thought of as being part of Access (or even as being “Access”) but in reality it is a Windows system component built by the SQL Server team. Jet is unique in Microsoft because it provides both a data storage mechanism with a heterogeneous query processor. This ability to run queries across a number of different data stores is a key reason people use Access, so it was super important for us to retain that ability going forward. The SQL team is de-investing in Jet, and telling developers to code against SQL Server Express, but as it lacks the heterogeneous query functionality we needed, Access stuck with Jet.
However, we needed to extend the Jet engine, so took a “private” copy of it, and have extended it for Office. This means Access 12 no longer uses the system Jet engine, but is tightly bound to its own version. The new version is fully backwards compatible with the old, so it will read & write files from earlier versions without any problems. And since Access now can update the engine, developers can be confident that their Access apps will continue to work in the future. Developers can still program against the Access engine, but since it isn’t part of the system any more, application users will need Access on their machines. Developers targeting users without Access can continue to use the Jet engine as they have for years or can move to SQL Server Express.
The primary feature we added to the new Access engine is support for “complex data”. Complex data really isn’t so complex – it is simply a join in relational terms, or a repeating region in XLM terms. What it brings to Access is full schema compatibility with Windows SharePoint Services lists. This allows us to take SharePoint list data offline and to provide rich-client UI for SharePoint using Access.
In the application, complex data usually shows up as the ability to select more than one value for a field. For example, imagine an Issues list with a column for “Assigned to:”. In a single table you could only assign an issue to one person, and Access would provide a simple bound drop down list of people to Assign to. With Complex data, you can (still looking at a single table) assign the issue to several people at the same time, and Access provides a drop down check list with the ability to select several people. This works just the same way as it does in SharePoint.
Behind the scenes, Access does just the same thing a developer would when building the same functionality. We create a table for the join information and create a many-to-many join (in this example between issues and people). The Access database is fully normalized, and there’s no string manipulation monkey business or anything untoward going on – its just vanilla relational joins. However, from the end-user’s point of view, it is far simpler than building the tables and setting up the relationships herself. And since SharePoint doesn’t support joins, it maps directly with the SharePoint way of doing the same thing.
Developers, of course, are quite comfortable doing this by hand and can choose to either continue as they have or to use the new functionality. For developers targeting SharePoint, it provides a mechanism for handling SharePoint’s complex data structures naturally.
In the next post, I’ll talk about the changes to the Access security model, and how that flows through both the data layer and the rest of the application.
Comments: (35) Collapse
>The SQL team is de-investing in Jet,
>and telling developers to code
>against SQL Server Express, but as
>it lacks the heterogeneous query
>functionality we needed, Access
>stuck with Jet. Then the answer is clear, JET should be transferred to the control of the Microsoft people working on MS Access. If the SQL Server folks won't take care of it, then the people who care about it should. >
>However, we needed to extend the Jet
>engine, so took a “private” copy of it, This makes absolutely no sense. None whatsoever. (Not the act, that is understandable. The part that doesn't make sense is that it was done at all instead of continuing on with what went before, but simply in the hands of the MS Access developers instead of the SQL Server developers.) Perhaps some high-powered management needs to say "do it" to get it done, but JET needs to move to a new house. >This means Access 12 no longer uses
>the system Jet engine, This makes even less sense. >but is tightly bound to its own
>version. Different versions of JET? What for? Microsoft should keep on doing what it has always done. Upgrading JET's functionality in exactly the way it has always done. "Private versions" and "extensions for Office" serve no purpose. There should only be one version. Updated and upgraded and modernized with new features and capabilities. If the SQL folks won't do it, it's time to turn over JET to those who are willing and able. >Developers can still program
>against the Access engine, but since
>it isn’t part of the system any more,
>application users will need Access on
>their machines. This is a big problem. This is really of no help or use that I can see. Departing from the model of the MS Access GUI + JET that has succeeded with so well to date is completely unnecessary and counterproductive. >Developers targeting users without
>Access can continue to use the
>Jet engine as they have for years or
>can move to SQL Server Express. The developer thus misses out on any of the potential benefits and features of an "upgrade", creating a situation where there might as well not have been an "upgrade" in the first place. >
>The primary feature we added to the
>new Access engine is support for
>“complex data”. Complex data really
>isn’t so complex – it is simply a join
>in relational terms, or a repeating
>region in XLM terms. [...] So, SharePoint data support is the primary feature that will be added? I have to admit, I have a lengthy list of things I'd like to see added to MS Access, and SharePoint support isn't on the list. (It's not that I think SharePoint support is bad, I just think there are many more high-priority things that need to be supported.) >
>Developers, of course, are quite
>comfortable doing this by hand and can
>choose to either continue as they have
>or to use the new functionality. For
>developers targeting SharePoint, it
>provides a mechanism for handling
>SharePoint’s complex data structures
>naturally. I am hoping that there are a *lot* of MS Access developers out there with *lots* of paying customers who want SharePoint support. (While simultaneously doubting it.) I am also hoping that everyone who formerly didn't need to own a copy of Office with Access in it, is now willing to purchase or upgrade to it in order to run new "Access Applications". (Though, again, I doubt it.) --------------------
Everywhere this article refers to Jet, it is refering to JET Red. JET Red was an ISAM originally developed by Microsoft for BC7, compiled basic. JET Blue was originally developed by Microsoft as a prospective upgrade for Access, but it was never used in this role. JET Red and JET Blue began sharing the common JET moniker in the Spring of 1990 when a query technology, QJET, was developed that would host on both Red and Blue. As others have noted, JET Blue went on to be used by Microsoft Exchange, Active Directory and many many other Windows services. JET Blue was a private API for many years, but became a published API in April of 2005 when three middle ware applications not covered by the Windows EULA chose it as their data store. Now anyone can use JET Blue. JET Red is a file sharing techonlogy with page level locking and best effort crash recovery. JET Blue on the other hand is designed to be embedded in a server application, does not share files, has write ahead logging for guaranteed crash recovery and has record level locking. JET Blue does not ship with a query engine but instead relies on applications to write their own queries as C++ ISAM code.
So the reason that Access doesn't use SQL Server engine or SES (JET Blue) is the need to maintain shared file access. Right. But network file sharing is so 1980s... There could be new database format, based on SQL Server Express engine or maybe WinFS, and a technology to automatically share such databases in server-side mode... And what about unifying query language semantics on a per-project basis, so moving to SQL Server is easier?
I only saw one person mention multi-user environments. I would like to hear that something has been done to reduce the impacts of slow networks and dropped connections. And to go with this, what about improving backend replication/recovery processes? I did like the comment on the need for better reporting tools, but all this needs to keep the non-server owners mind. Those who use Sharepoint, likely own and operate SQL Server, or at least MSDE. It means thay have more options, for what ever reason. Access has always been the tool of choice for those with limited options. A God send, and is the biggest reason I like (even love) Microsoft. High on my list as well are Excel, Sharepoint 2003, and Visual Studio.Net. It's the tug-o-war at Microsoft that keeps it evolving. Would you rather have Oracle Portal?
Runtime Version?
Will there be a distributable runtime file for Access 12 or will all users need a full versin of Access to runan app written in 12?
Comments: (loading) Collapse