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
My heart dropped when I read "multiple values for a single field". I thought wow, you've capitulated to all those beginners who don't understand about normalization! But it's really smart to implement that in a proper relational manner "behind the scenes". This means you don't loose the folks who are perfectly happy designing directly to the relational model. Very exciting! Hanging out to hear more.
Bad move, very very bad move. From my days running hosting servers I remember that there are loads of web sites that run off Microsoft Access db's. I can think of very few providers that will install Access onto web servers just so people can use the new features. And can you imagine telling people that to use your desktop app (where I work we just released an application that stores it's offline data in Access) they need to purchase and install access as well. I see the web market moving away from Access with this. I just hope there is an option to save to the old format in the new Access 12.
They wouldn't need Access - just Jet. If I understand him correctly, nothing has changed in that regard. It's just a /new version/ of Jet. As for websites using Jet back ends: you're kidding me, right? Can you give an example of a public website using a Jet back-end? I just can't see that happening ...
JET must die. Just what we didn't need - yet another MS code-fork off JET. This type of code dupication is one of the bigest things wrong with Office. It's wrong in Office 12, and was it was wrong in all the previous versions too. All these code feifdoms within Microsoft create more confusion and waste in the user community than anyone seems to realise. IMHO, the right answer was to work tightly with MSDE.
Jet is perfect for my main application. I specifically do not want the greater technical complexity and administrative overheads of SQL*Server etc. The "everything in one file" approach is a winner, in appropriate situations.
A lot of what "makes" Access *is* Jet. The tight binding of Jet to Access is what makes the Query Designer and the Expression service strong. Access without Jet is like SQL Server Enterprise Manager - SQL Server is itself a strong engine but Enterprise Manager is much weaker than Access Query Designer. The reason why public low capacity websites use Jet is that it *isn't* SQL Server or MySQL.You don't need to set up TCP/IP connections to Jet or admin accounts or whatever. The file can just be ftped on and off, for offline edits and maintenance. dBase/Paradox is similar to .mdb, but .dbf isn't one file. And yes, there are public websites using Jet - as long as your Jet request from the webserver process is completed *before* the next Jet request in the queue, there is no problem with performance. Jet based websites can take 1 hit per second equivalent to 10-20 visitors a second because not every visitor is hitting the submit button at the same time. Extending Jet to support triggers or audit trail loggin might be an idea, but extending it just to support Sharepoint is not, IMHO. There are so many portal / cms engines both MS and non MS.... What makes Sharepoint so important that it diverts resources from making Access better? SQL Server has strong points and Jet has strong but different points - there is no need to make them compete. What Access needs right now or yesterday is deliverables to and from web formats. Notably transformations from Win32 type Access/VBA master-detail Forms and Reports to .NET Web Forms + SQL Server Report Builder type objects so that they can be generated from Access and hosted on IIS. What Access needs now is lots of strong XML functionality - to read in XML text or XML text over HTTP. And to transform reports into good formatted .XML data and markup (XSL-FO?) to RTF, Acrobat format, XSL-FO, XHMTL+CSS stuff. Just a thought: ADPs have been one move towards Client+Server and we need more. In COM, there has been VBA+Access and VB6. In .NET we have VB.NET but it's not nearly as RAD. Access 13 should be Access.NET
"They wouldn't need Access - just Jet. If I understand him correctly, nothing has changed in that regard. It's just a /new version/ of Jet."
Yes, but the new version of Jet *only* ships with the new version of Access ("but since it isn’t part of the system any more, application users will need Access on their machines."). So thus my understanding is for use of the new features you will need to install Access on the end users/webserver machines, if you are happy to use the old features only then you will use a save as function to save to an old format and use the "old" system version of jet. "As for websites using Jet back ends: you're kidding me, right? Can you give an example of a public website using a Jet back-end?"
Thousands of websites, all little guys, the guys who do not run there own infrastructure but get shared hosting, the guys who make use of Access because it is cheaper than SQL Server. But since you asked for 1 example: www.gilbertestates.co.za
This is the kind of little guy that if they were going to develop there site now would likely end up paying extra every month to their hosting provider for a SQL database when they didn't really need it.
Like some of the others, I am completely underwhelmed by the changes in JET and Access. I would be willing to bet that SharePoint will be used by fewer than 1% of Access users - why spend all that time on it? 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. So far, I have been very, very depressed about what I am hearing about Access 12. It seems that there is very little of importance being added. If a made a list of my top 100 desired improvements for Access, not a single has been mentioned my Microsoft. There was no reason to upgrade to Access 2003, and now it looks like the same for Access 12. Please, please, please, give us some good news! Give us what the USERS want - take a look at the hundreds of requests for changes on UtterAccess - have you addressed even one of them???? Has ANYBODY asked for SharePoint integration??? (Answer: not even one). Please don't waste your time developing unnecessary features just to promote the Microsoft SharePoint agenda.
There may not be many public sites running on Access data but lots of medium-small companies use the mdb format on their intranet and extranet sites because it's so simple to handle. They put the data together in Access on the desktop then squirt it up to the web page. Speed, security and scalability aren't really an issue for them. I'm thinking of people like our local car showroom - they load a couple of dozen cars on their site each week and are delighted to get a hundred hits a day. They don't need SharePoint.
Well, first, the fact that the office dev team gets JET is great. As a developer, I think every year, or 2nd year, we get some rumor, or large IT department stating that JET is dead. Now that the ms-access/office team owns jet, then any doubt about its future is removed. With office owning JET, then the future is bright. The office team has promised compatibility with JET, and I can’t see things working any other way. In fact, while I use the (2002-2003) format in access 2003, it actually defaults to a2000 format. So, tradition from the office people is rather good in terms of compatibility, and I don’t see anything that would prevent users from building, and saving data in a mdb format to be used for a web site. So, the ability to use, and work with a JET mdb file is preserved here. >What Access needs now is lots of strong XML functionality You know the last two versions have had much improved XML abilities. Further, there is the soap ad in tool kit for web services. So, as far as I can see, we are getting bits and pieces that allow us to move towards using web services. And, those SharePoint features are also a move in that direction. I mean, I can’t believe that companies are still spending HUGE amounts of developers money to build applications that let you “move” a set of documents through a process. You got some product you are making, and a whole bunch of documents like spreadsheets, letters of correspondence, meeting notes, pictures of the product, specifications for the product etc are created, shared, and move through the company. It usually starts at the sales process, but regardless, you need something that lets every one go along for the ride. While some of these work flow systems do warrant a bunch of developers writing code, for a SURPRISING amount of systems, the ability to group a bunch of documents together for a product (or whatever), and then have people add documents, pictures, spreadsheets, and move this mess along through a corporate process is exactly what SharePoint does. I think it would be quite sad that MS leaves ms-access complete out of this SharePoint loop. So, while I don’t need those share point features now, I can well see me wanting to use them in the future. So, don’t diss the fact that we are getting SharePoint features for ms-access. I will say that few are using the SharePoint features, but if we NEVER get any sharepoint features, then you will NEVER see more then 1% of ms-access users using sharepoint!! Adding SharePoint features to ms-access gives it a NICE credibility in the corporate environment, and opens up new doors for access users (and developers) in the future. So, I do welcome SharePoint features for ms-access. Just like I welcome the xml features we got, and also the soap tool kit we got too. Albert D. Kallal
Edmonton, Alberta Canada
Kallal@ msn.com
Good to see you Albert. (I hope others from CDMA will speak up as well.) I would beg to disagree with you on several points you make: 1. I'm not "dissing" any feature - it's just that over the past 10 years, I have personally seen needs for Access features in improved heterogenous connectivity, "good to go" web outputs and inputs that are w3c compliant and a host of other things. Sharepoint needs from my clients or potential clients? Nada. A dev team obviously has a laundry list of features to implement with consequential resource demands, priorities and datelines. Sharepoint features in that priority list? IMHO low. Programmer/User friendly M:M wrappers? High. Increased Win32 Form and Report features and wizards? High. Spare a thought for the poor 10 year old Expression Builder.
LINQ syntax in VBA? Nice. 2. Add-on SOAP toolkit and PIA in extra cost VSTO and 4 (four) measly files in %program files%\Microsoft Office 2003\OFFICE\AccessWeb\ (which are not even ASP.NET but ASP) and the stray VBA Application command to import/export XML - that's not a lotta XML features.
"We’re very excited about this, in large part because it is pretty much exactly the same as our existing database engine. The difference? We own it now and can extend it." I hope this means that we can look forward to this problem being resolved: PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
support.microsoft.com
I hesitate to ask this, but.. What will happen with DAO? My main product has 75k loc of VBA /and DAO/. It's clear that Access, VBA, and now Jet, are not going away anytime soon. But that leaves DAO as a critical factor for my main product. I can live with DAO not being modded for new Jet features (if that's the way it has to be), but I am reeeeely reeeely dependent on DAO continuing to work to its original spec. Tell me this will happen.
There is still no mention of a runtime version of Access 12. Is that in a future blog (one way or the other)? The rather obscure sentence...: "Developers can still program against the Access engine [phew!], but since it isn’t part of the [Windows?]system any more, application users will need Access on their machines [or the runtime?]." ...certainly casts no light. Andrew Wrigley
I think SharePoint is great but... 1. You need Windows Server 2003 to get SharePoint (am I right?). 2. Like many Access developers, I develop for small to medium sized businesses. None have Windows Server 2003. 3. Most of my clients outsource their network management. Therefore, due to compounded interital forces, they won't get Windows Server 2003 until their network guys are comfortable with it. Ie, another 3 years. 4. Therefore, I won't be developing solutions that use Access with SharePoint for at least 3 years. 5. This has the following effects:
5.1. The Client won't invest in Office 12.
5.2. This opens the door to the client purchasing non microsoft products (and me losing the client)
5.3. If a client needs SharePoint-like functionality, they will have to pay me through the nose to design it in good old Access.
5.4. All this functionality will be redundant the minute they have Windows Server 2003 on their network.
5.5. 5.4 will be another reason why Access (and me!) get a bad name without deserving it! (Another is OZPP Syndrome [Over Zealous Parish Priest, that inevitably ends in relational tragedy])
5.6. GoTo 5.2. 6. For Small to Medium businesses, it is an understated fact that cost of ownership is rendered insignificant against cost of development. An app for 10 users will cost the same as an app for 200. 7. The curious paradox is that small to medium businesses would benefit more from investing in state of the art server software than large corporations. 8. I don't think that Microsoft has realised this. 9. To get SMBs to invest in office, Microsoft would do well to find a way to deliver SharePoint without the Client having to upgrade their entire server to Windows 2003. 10. I have no idea how this would be done, but if it is not done, Microsoft is targetting Office 12 almost exclusively at large corporation and missing a huge opportunity. 11. GoTo 5.2. Andrew Wrigley
Comments: (loading) Collapse