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
>hesitate to ask this, but.. What will happen with DAO? I will say that near riot would break out if my DAO code all of a sudden stopped working. However, if you look at what’s happened with DAO, the latest version of ms-access we have (a2003) actually INCLUDES THE dao by default!! Now, you have to go WAY BACK to access97 to find a version of ms-access that includes the DAO by default. This is a simple matter of the MS team listening to us developers! Guess what? Well, we got the DAO back as a reference by default in a2003. I think this shows a incredible flexibility on the MS access team. That means that the ms-access team cares about folks like me and you who still use DAO. I can’t think of a BETTER example of the ms-access team listening to developers, and catering to our needs to continue to support, and use DAO. More amazing, is that is DAO support exists for new features, and remember even the forms reocrdset (not the clone) also supports DAO. Remember, the forms recordset was a new “ado” feature in a2000. Do note that if you stuff the forms reocrdset into a ADO reocrdset, you get a ADO reocrdset. If you stuff a forms reocrdset into a DAO reocrdset, you get a dao reocrdset. So, if I can think of one area where we been taken care of, the DAO issue has got to be it. We get new features, and the DAO references is now back by default. Considering that I made the jump from a97 to a2003, it been a real nice ride for me. Considering the recent efforts of the DAO ref back by default, and support for new features in DAO, you got feel all warm and fuzzy on this issue. I am simply not even worried given the recent actions in regards to DAO. As for the record locking issue pointed out? Well, I would have to think that 99% of the time, this locking would be occurring at the forms level, and that works just fine anyway. If you do have some DAO recordset code that needs record locking, the KB article does give a workaround, but I have not had a problem in this regards. Albert D. Kallal
Edmonton, Alberta Canada
Kallal@ msn.com
I really agree with R. comment:
"I would be much more interested in RAD improvements, particularly with ADP formats and .NET integration. Will the new many-many features make it to ADPs also? Somehow I doubt it." The user are not requesting Sharepoint or new JET Engine (simply siwtch to SQL Server/MSDE). They are requesting to keep form separated from Report, and code in .NET/Component. We want our MDB/ADP stopping to growth and require incredible amount of RAM for running an Access based application.
Albert, thanks for your interesting comments on DAO. Sign me up for the "near riot" if it stops working! I'd really like an authoritiative response from MS, on that question. PS. When I read Erik's comment on multiple values in one field, I thought to cite your article on multi-valued databases, which I read some time ago. I didn't, because I wasn't sure if you would approve. If you want, I encourage you to cite that article, here. I'm an old-time IT pro, but I had not heard of multi-valued databases (let alone SharePoint), and I found your article quite instructive.
Thanks for the good discussion about DB layer, here's a consolidate response to some of the questions & comments. I rolled up the comments as: Why SharePoint? - Lots of discussion here, and here are some thoughts. The main question seemed to be "why invest in SharePoint when my clients don' use it". SharePoint isn't everywhere yet, but it sure looks like it will be - SharePoint Portal Server is the fastest growing server product in Microsoft's history. Windows SharePoint Services (the part that comes with Windows Server) is growing even faster but is harder to track accurately since it isn't sold separately from Windows Server. We believe that where people use file servers today, they'll use WSS servers tomorrow. OK, not literally tomorrow, but literally next month - it is happening very quickly. I understand the issues about small businesses moving to WSS, and agree that it can take some time, but we really do see the evidence showing that this will happen, and sooner rather than later. How come you didn’t build any features? – Hold yer horses on this one. The DB layer stuff and the SharePoint work represent only a portion of what we did in Access 12. We’ve tracked the list on UtterAccess (and lots of others, plus our own research, etc.) and have nailed lots and lots of those features. I just haven’t gotten there yet, as I figured the engine discussion was the most interesting way to get started for this group. I’ll be talking about those things over the coming months. Even without the SharePoint work, Access 12 will offer the biggest change since Access 1.0. What happens to DAO? – It lives on just as it has. As several folks noted, we can’t bust DAO or we’d break a huge number of apps. We’re working on a solution for the row-level locking issue, but it is unclear if we’ll get this in or not. Will there be an Access runtime? – Yes, that is the plan. Solutions created with the Access Developer Extensions should work like they have before. The ones that are different are solutions created in VB directly against the engine. Those didn’t require the runtime or Access before, since Jet was in the system. Now programming VB against the Access engine will require Access, or you can still use Jet from the O/S just like before. What about Jet as a server backend for websites? – Jet will continue to work for this just as it has, and Access 12 can author those databases just as before. As noted, this is a pretty common scenario for a lot of small businesses (FrontPage even has, or had, a feature to make it easy). Also, as noted, running Jet on the server comes with a bunch of other challenges, so it isn’t a panacea either. Other solutions include SQL Server Express and (wait for it…) SharePoint. Yes, I know SharePoint isn’t generally thought of as appropriate for public sites, but it actually works well, and provides the simple list functionality many users are looking for. And yes, I know I’m gonna get an earful for that one…
Ok, So, I'm still confused. Are we talking about Jet Blue or Jet Red here? Is this the Jet that the new outlook express is using? I take it that it is not considering that OE choose "Jet" cause it was on all Windows machines? BOb
All the access user groups are great; but really poor old Mr Gates keeps on bringing out re works of the same thing
Eric, your comment 'Even without the SharePoint work, Access 12 will offer the biggest change since Access 1.0.' really hasn't yet been proven...but I'm a patient guy. I just hope that there is more behind your comment than hot air. I heard about this post via the October 17 post on the JoelOnSoftware.com blog, where Joel Spolsky rips Microsoft for the pathetic text-search capability in ANY Microsoft database product. (He makes some other observations that really only adds to your pressure of having to really have substance behind your 'biggest change' statement.) Personally, I fear that this latest revision of Office is not much more than a general reskinning of the UI across all apps. I'll be keeping my wallet in my pocket if that is the case. Ah, to remember the 'good old days' when a new version of software actually meant a substantive improvement in performance and capability of my applications rather than marketing noise.
Erik wrote:
> The [cases] that are different are solutions
> created in VB directly against the engine.
> Those didn’t require the runtime or Access
> before, since Jet was in the system. Now
> programming VB against the Access engine
> will require Access, or you can still use
> Jet from the O/S just like before. Let me check I understand this. You're saying that the new Jet is still a seperate component, accessed through an intermediary technology (eg. DAO), but for marketing reasons, you'll only supply it bundled-up with Access - you won't supply it on its own? That's a marketing decision, not a technical one?
TC - Basically Access just comes with a modified version of Jet with new features (like Complex Data) that don't exist in the system component. If you use those new features, people will need the new version of Access (with the modified version of Jet) to read it. If you use existing Jet functionality, you don't need to care about it. Ron - I think there are some pretty big changes, at least. Changing the UI is much more than just reskinning Office 2003, and Access probably has more "visual" changes than the other apps overall, but these changes are also functional. Even that aside, there is still lots of new useful things that Access 12 can do that will hopefully be more apparent due to the visual changes.
I am so excited about this blog. I really hope I am able to use Access 12 to build apps. So keep the information coming! I'm interested in multi-user scenarios.
Kawigi wrote:
> TC: Access just comes with a modified version
> of Jet with new features ... If you use those
> new features [you] need the new version of
> Access (with the modified version of Jet) Sure; but that that doesn't answer my question. My question was: why won't MS supply the new version of Jet[1], seperately to the new version of Access[2]? Is that a technical decision, due to [1] being bound more-tightly into [2], or is it a non-technical decision, ie. a marketing one?
Bob Archer-- Access is Jet Red. Outlook Express will be using Jet Blue, aka the "Extensible Storage Engine" (ESE, aka Exchange Storage Engine). See msdn.microsoft.com/.../portal.asp for details.
I don't understand why MS would make these changes to the JET Engine, but not add it as a seperate componant.
Does this mean that JET that comes with Windows will be staying static from now on with Access having it's own engine? (Where we can develop against in either old Windows JET or new Access JET?)
Steve, that's how I undertood it. The old one (Jet) is not enhanced, the new one (now called ADE, Access Data Engine) is, and you can code to either one. But I still don't know why ADE will only come bundled with Access.
Your description of JET sounds like a re-write of history. JET was originally part of Access (or FoxPro for the very early versions). It split into Jet Red and Jet Blue. Jet Blue was used for MSExchange and Active Directory and is shipped with Windows 2000 and later. It's now known as ESE. Jet Red was still shipped with Access. Whether some marketing wonk considered it part of Windows or not is similar to how msvbvm50.dll was shipped with Windows. All throughout it's history, Jet Red has been freely distributable. Many many many VB apps were shipped with Jet Red. Now there seems to be some politicizing and backstabbing going on within Microsoft - why should the Access team need to take a "private" copy when they own Jet Red. Are there some internal fights going on? Will this now be called Jet Green? Why won't Jet Green be freely distributable? Do Microsoft listen to customers? Are Microsoft deaf?
Comments: (loading) Collapse