Comments

# AL said on February 3, 2006 2:20 PM:

Glad to see you're still alive!

Personally, I would appreciate it if you went through that list in REVERSE order. The last few items involve life or death decisions for project developement. The first half of the topics are mainly cosmetic. The people who read your blog are not newbies; we are mostly experienced developers here.

These topics are critical:
Access Limits
Upgrading to Access 12
Access in Mixed Environments
Access and SQL Server
Access Developer Extensions
*Installation, large scale Deployment, Automatic, hands-off updates
*MDB vs ADP vs .NET

The other topics are very interesting and important (especially re ease of use for novices), but won't be as relevant to developers if Access can't handle complex multiuser scenarios and installation issues.

# Ryan said on February 3, 2006 6:04 PM:

I agree go in reverse order! Here is what's most important to me.

* Access and SQL Server
* Access in Mixed Environments
* Access Limits
* Access Developer Extensions
* Linking to External Data
* Access on SharePoint
* Customizing the New UI in Access

# Martin Reid said on February 4, 2006 4:23 PM:

While true i.e many developers need to know this I think that the majority of users are not developers. I know that in my place while we build pro Access DBs users build far more of them than we do and I get the distinct feeling that developers aside that this is the market MS are aiming for. Well so be it. No mater how you dress up the interface via GUI, at the end of the day to build real applications and applications that work you still need to knwo table design, data types, relationships etc etc. Even to sue the new Many to Many tool you stioll need to know aht a Many to Many is and why you are using the lookup to hide this. This little "trick" by MS excapes me as users have to learn this theory anyway in order to make use of this hidden design tool.

# DmitryKo said on February 4, 2006 5:37 PM:

So far Excel is far more common 'database' application for a typical end user. I'm yet to see a user who ever tried to create its own database with Access... maybe version 12 is going to change that.

But whether a user succeeds in building a viable business solution, or turn to a developer to materialize it, it's certainly going to be of interest for other users in this new reality. So here's the topics I'd like a further info on:

1) Access as a RAD tool for SQL Server
2) Converting databases to SQL Server
3) Deploying Access databases with SharePoint Services
4) Web reports and forms
5) Secure deployment and restricted user access
6) File sharing issues over peer-to-peer networks
7) Access SQL language enhancements (if any)

Also, I wonder if Access 12 over Sharepoint could be treated as a kind of low-cost version of the SQL Server, but customizable by end users?

# radasys said on February 6, 2006 10:29 AM:

Hi !

I don't think that a lot 'users' watch this blog.
Maybe you can make a poll how access is used from the visitors.

- drag&drop and assistances, only one mdb
- makros, querys in design view
- all logic in VBA and dynamic SQL

or
- better Excel
- small department specific applications
- just as RAD for protoyping
- enterprise business apps

maybe ms did something similar as marketresearch already ?

thomas

# StepUP said on February 6, 2006 11:05 AM:

I would have to agree with DmitryKo. I watched the Access presentation from the PDC (BTW...very impressive!) and it seems that this idea of users creating their own databases is still a prevalent one.

In reality, this just doesn't work. I've been doing consulting, mostly with Access, for 13 years now and I have yet to see a database created by an end user that wasn't...well...a mess!

OK..maybe the out-of-the-box templates aren't a bad idea. But anyone that needs a databasee app for any real world purpose will never be happy with a template application, IMO.

Erik, the question of security is still a hanging one. Seems that the question was never directly addressed form the blog of a few months ago. Will there be user level security? If so, will it still follow the SYSTEM.MDW type of model, and will the encryption be strengthened so that it can not be hacked?

Thanks,

John

# Erik Rucker said on February 6, 2006 1:46 PM:

I totally understand the desire to invert the order and cover Access 12 from the inside out.  There are 2 key things that keep me from doing this.  First, we're really serious about making the product easy enough for end users, and it will be hard to understand the investments we've made in the guts of Access unless we've gone through the rest of the app.  I really will get to the innards of Access, but it will make much more sense if I can set the context first.  Please just be patient with me.  Second, there are actually a lot of folks that read these blogs who aren't developers, and I need to get them up to speed as well.  Again, please stick with me and we'll get to the details.

On the security front, user level security is covered in the October 19th post on Security.  In short, we don't recommend using user level security in Access where significant protection is required.  That is a great place to use SharePoint or SQL Server.

# StepUP said on February 6, 2006 2:21 PM:

<<On the security front, user level security is covered in the October 19th post on Security.  In short, we don't recommend using user level security in Access where significant protection is required.  That is a great place to use SharePoint or SQL Server. >>

Wow, I really don't understand that position. I come into an environment where the customer need might be something like "I need a department level app for 10 users, and it must be secure since we are tracking customer investment information". Access can easily handle the requirements, but now I have to tell the customer "Well you have to invest in SharePoint or SQL server, hire a installation specialist and probably an administrator". It just doesn't make sense!

What it boils down to is just to have a STRONGER encryption for passwords in SYSTEM.MDW; this does not seem like an unreasonable request. Now I can fulfill the customer needs, make it economically feasible for them, with no additional and unnecessary overhead in resources required.

99% of the work I have done in Access have been these types of applications. Why should we have to force a customer into something that just isn't required for the particular need? Its the proverbial using a canon to swat a fly approach.

I have no objection to having that functionality on Access, in fact I think its great. However, if there is an emphasis on making Access an end user tool, then do NOT require a big and expensive back end for it to be functional.

Thanks,

John F

# Third Of Five - just another voice in the Collective said on February 8, 2006 5:17 PM:

It's been a long time but I'm finally back. Why have I been away?
Well, between the holidays, shipping...

# Trisha said on February 9, 2006 5:13 AM:

maybe split the blog to 2.. one for newbies and one for developer

# Alan Cossey said on February 10, 2006 11:45 AM:

Erik, Thank you for getting this blog going. The list of subjects to be covered looks really good. So far I have been really impressed by what I have seen. Truly I have. I do still fear what is happening on the security side though. Even though SQL Server Express is a bit easier than previous versions of SQL Server, there is still a big jump involved in using it instead of ACE/Jet. Please do act on the concerns expressed by some of the developers here. Expecting us to use Sharepoint or SQL Server to get any reasonable security is a big fault.

# StepUP said on February 10, 2006 2:28 PM:

I agree wholeheartedly with Alan. I too am looking forward to the new release. Many of the new features look very promising.

But I know that in most cases, based on my past experience (I've been developing Access applications since 1.0), I will NOT be using Sharepoint or SQL for my apps. Its great that these options are available, but I think its too stiff of a penalty for an end user, or customer for whom I might develop an application, to require SQL in order to have decent security.

Please...PLEASE...shore up local Access security. I think the biggest issue is with SYSTEM.MDW. If the passwords there were strongly encrypted, I think it would solve the major problems in the security model.

# A discussion of what's new in Access 12 said on March 28, 2006 3:26 PM:

This is a quick post that isn't on the overall list but that is provides some more details about new...

# Mitesh Patel said on May 5, 2006 10:34 AM:

Its great to have the new GUI features that are comming with the new version. What about the size of the database that is to say is it comsuming more data storage.
Previous versions has 2 gigabytes minus the space needed for system objects.

I am keen to know whether MS Access 12 accomodates the date capacity and consumes more data storage space using the new GUI.

I would make a sincere request to increase the data storage capacity atleast considering two factors Small Business and a effective and effecient use of MS Access 12 for the same.

# Pete S. said on May 10, 2006 4:38 PM:

Your statement that you have not increased the 2GB size limit of an Access database has me very disappointed. As a developer I would have hoped that MS had increased this limit in Access 2007. Outlook personal folders mailbox store used to be limited to 2GB but MS increased this to 20GB with Outlook 2003, why can't the Access 2GB limit be similairly increased? At least 20GB would give some breathing room, 2GB is way too tight nowadays.

# Rick in Houston said on May 10, 2006 4:58 PM:

I think that 2 GB is all you would ever care to try to push through the JET Engine.  If you're storing data in Access, you're going through JET to get it.  It's just not that powerful an engine.

I don't know your motiviation to stay in Access, so it may be compelling and that's that.  I can't help thinking that by the time you're trying to move around in more than 2GB of data it's time to be in SQL Server.

One tip from the old days:  When I had an application that stored more data than Access could handle (must have been Access 2 or 97) I finally settled for splitting the data tables apart, storing each large table in a separate MDB.  I lost delcared referential integrity, but I also lost the size limit that was killing us.

If that's an option, you could run integrity checks through a middle tier that's actually inside your MDB or MDE.

I'm curious to know, why the drive to try to deal with more than 2 GB of data within Access (as a data container)?

# John Rylander said on May 23, 2006 8:56 PM:

Agreed that the 2 GB limit is quite painfully obsolete these days.

How strange: in the new MS Office, if you're working with small amounts of data, you use the database, but if you're working with multiple gigabytes, you use the spreadsheet instead (limited only by OS-available memory, which with 64-bit Windows means 128 GB).

This isn't right.

It's very disappointing to know that I'll need to forgo using Access except for projects where there's no chance at all of wanting to store a lot of data, which want is common when doing data logging and analysis, even on a standalone PC basis.

Excel is growing up, albeit very slowly (breaks the 64K barrier in--what year is it??); I wish Access would too.

# Charles Wannal said on May 23, 2006 9:57 PM:

Is there any reason that you couldn't move data to one of the versions of SQL Server, perhaps the free one?  Why so grim over the fact that no one single tool can fit all situations?

Not so much data (as with 90% of apps)?  Access for app and data.

More data, not too many users:  SQL Server Expresss.

More data, more users:  SQL Server.

What's the big deal?

# John Rylander said on May 23, 2006 11:41 PM:

Charles,

So long as one has a small application that won't grow much, Access is fine; otherwise, it's not.  And for many smaller or budget-conscious businesses, paying for, setting up, and maintaining SQL Server is just too expensive and intensive to be taken seriously (though MySQL or PostgreSQL are getting their attention, if cost is the issue).

In the real world, this means a lot of people doing analytical work in smaller shops or with limited budgets using the wrong tool (Excel) simply because the tool designed for database work is nearly stuck in 1997 or so, capacity-wise.  (Was 1 GB a decade ago, now 2 GB.  Wow.  It doubled.  If this pace keeps up, by 2107 AD Access will be simply too powerful for today's biggest hard drives! (barely.))

Sorry for the annoyed tone--not annoyed with you at all, just a bit with MS.  It's just unfortunate when customers are victims of forcible market segmentation, with Access being the PCjr of the Office world, it seems.  But they do have alternatives.

# John Rylander said on May 24, 2006 11:41 AM:

(Looks like I overstimated the rate of growth of Access' capacity: the limit was 1 GB back in 1995, too.  Some things in life never change--like Access' capacity.  :( )

# Charles Wannall said on May 24, 2006 1:34 PM:

John:

You said "setting up, and maintaining SQL Server is just too expensive and intensive to be taken seriously "

If a user is even flirting with having 2 GB of data, and is not willing even to install the free SQL Server, then the user is in serious need of better IT advice.  By the time you get anywhere near that amount of data, you are way past needing to understand that you have a serious application and it's going to take some serious talent to deal with it.

If you can't budget a person to take care of the task, then you have no business trying to take on that task.  One of the biggest problems I see continually is management thinking that somehow database development is just magic.  You put some data in a database, whatever that is, and it all just works, somehow.  And if you buy Access you can have your secretary put it all together.  And it will work forever, regardless of how your data grows.

If you set up free SQL Server as wide open as your Access database back end was set up, it's not that big a deal to get going.

What possible reason could there be for causing an MDB to be able to hold more than 2 GB of data, when you'd be limited to the Jet Engine to get at it anyway!?

We have an application that holds two years' worth of engineering documents, requirements lists, mission requirements lists, and on and on and on, and we're just finally flirting with 2 GB.  We knew we would get this big by about now, but we've been in SQL Server for a long time, for a number of reasons, all of which became relevant long before we ever approached the size limit of Access.

Now, if users don't do any sort of planning to start with, and they run into limits willy-nilly, of course the're going to have headaches.  But that will be true regardless of the tool you're in.

And if you do any reasonable sort of planning, then you start by looking at tools and make some informed decision about which ones will see you through.

Whew.  Didn't realize I was so worked up.  No one tool covers all bases, especially Access.  I think I get annoyed by what looks to me like blaming a tool for lack of planning understanding.

# Charles Wannall said on May 24, 2006 1:40 PM:

Type in 1:34 PM post:

"tool for lack of planning understanding."

should be

"tool for lack of planning OR understanding."

# TomT49 said on May 24, 2006 1:42 PM:

Charles: Amen to that. I was in the same boat several years ago, when I decided an in-house app based on MS Access was soon going to be too big in terms of data, number of users, etc. to remain viable.

Instead of waiting for things to blow up, we made the move to SQL Server, and have never looked back. Access, as you know, was not intended to go beyond certain limitations. It's like trying to load your Civic with all your furniture, instead of using a truck......

# John Rylander said on May 24, 2006 2:12 PM:

Well, I think for large or mid-size corporate environments, with dedicated IT staff and stable, predictable needs, you're quite right.  But for small shops, people who just use the tools they have at hand, and who are doing (say) mathematical analytical work where data requirements can explode overnight, and then vanish the next week, the only option now is Excel--even for database work.  And that's not just arbitrary, but kind of pathetic, IMHO.

It does these customers no good whatever to say "instead of spending your time and a few hundred dollars for Office, you should hire dedicated staff, buy dedicated server(s), and get SQL server software--just in case you might need it some day."  They don't have the support of a large technical bureacracy, and their needs may change day to day and week to week, not year by year.  Back in 1995, Access could handle anything these users could throw at it--unfortunately, today it's the same: Access can handle anything they could throw at it in 1995.  Since then, Excel's capacity has increased 64-fold (and more, so far as actual MB/GB of storage capacity goes), while Access has doubled.  And for the users I have it mind (financial and scientific analysis), that means Access isn't much of an option.

Let me emphasize: I agree with you on typical corporate projects--lots of time (though never enough, of course), ditto for money, people, and hardware, very stable projects that may continue for years--some well-administered SQL product is right for them.  But for the small shops, with very dynamic needs, Access should be an option, but it isn't.

I'm very glad that the Excel team didn't take the same approach, even though a much stronger case can be made against expending Excel than against expanding Access (nutshell: it's a spreadsheet, not a database--and since I can't imagine needing a 10GB spreadsheet, I've no time for the people who do, etc. etc.).  More importantly, so are many of MS's customers.  Indeed, the expanded capacity--the first expansion in many years--seems to be, by acclamation, the one must-have, most desperately appreciated feature.  (Its new capacity sure puts Access and SQL Express to shame, GB-wise anyway [not wrt rows/records per table--1M limit in Excel].)

You can have the last word here--just remember, that I agree with you on large- or medium-shop situations, at least when it comes to long-term, stable, corporate-level projects.  I'm particularly thinking of small shops and/or dynamic projects--which for the foreseeable future will be done now just in Excel.  :|  (Unless the people doing them happen to have spare IT staff and hardware at their immediate disposal.)

# Charles Wannall said on May 24, 2006 2:32 PM:

My last word is just a trim-up comment.  I just don't feel that the only options are small scale (app and data in Access) or monster scale (hire dedicated staff, buy dedicated server(s), [buy] SQL server software, large technical bureacracy).

Squarely in the middle is SQL Express -- free to download, goes in pretty easy.  Handles up to five users concurrent.  You don't HAVE to set up any complex security just because you can.  And it doesn't require any sort of dedicated server.

I have it on my local workstation (a pretty good development machine but no server, strictly speaking) and we put the development and testing copies of our data on it.  I might not prefer to run the live data from here, since I have about 100 users, with 10 to 20 concurrent, but the truth is that even at that level this thing would do a reasonable job.

Blah blah blah.  I didn't intend to run this into the ground.  You're very clear on lots of details about different develoment situtations and I enjoyed reading everything you posted, except for those little passages where I think you're not noticing the solution for the middle case (app in access, data in SQL Express, or some other cheap backend for that matter).

Thanks for the lively exchange.  A genuine pleasure.

# Alan Cossey said on June 12, 2006 3:10 PM:

Erik, I have searched high and low for some meaningful information on security with Access 2007 when using an Access back end (or even as a one file solution). There is a souped up database password, souped up encryption and the Navigation Pane, but this is all easily bypassable. Say you have used the above on a .accdb file and have it open. It is easy open another database and write some code using GetObject that will hook into your "secured" database and do whatever you like with it, e.g. add/modify/delete its data even though you may have thought your Navigation Pane settings had secured the tables.
As has been mentioned in this thread, it is often, in fact very often, not possible to use Sharepoint or SQL server with Access to secure your data. In fact, I don't see that usng them will help at all if you are using linked tables as you can surely use the same code to get to those linked tables.
Unless I have missed something (and I really, really hope I have), Access 2007 has become a whizzy toy that is no good in any context where you need any security on your data at all.
If I and the other people who have expressed our concerns about this dumbing down of Access (and I don't think you can call it anything other than that) have misunderstood the effect of Microsoft's changes, please, please, please explain how we can still use Access seriously. So far, there has been a deafening silence about security apart from a few soothing words that don't actually provide any substance.

A very unhappy Alan Cossey

# StepUP said on June 12, 2006 4:16 PM:

Well, once again I strongly agree with Alex.

I installed the new beta last week. I really like some of the features. PDF is great, sorting and filtering in reports is awesome, I can see where tabs could be nice. And I think the Ribbons interface works very nicely in Word and Excel.

Overall, however, from strictly a developer perspective, I must say, I am disappointed.

I apologize if I am misinformed on some of the following points. If there is a workaround on any of these, please enlighten me.

As expressed in some of the other threads, there are too many things I am "forced" to comply with in the new version. If someone wants ribbons, that's fine, but why I am forced to NOT used standard menus and a standard (classic) Windows interface? For an end user that is just using the application (not developing), the interface simply doesn't work! (My understanding is that custom menus ONLY work with Access 2003 versions? Couldn't get them to work in Access 2002 versions. Not good!).

I really don't like the Navigation Pane. The old tabbed version was much easier to navigate and understand. I miss the multiple columns in detail view!

Its been said many times, but I am compelled to say it again. Sharepoint and/or SQL Server is usually NOT an alternative for a custom Access solution. People use Access as a mid point solution between Excel and SQL; the idea is to AVOID the high cost of a server solution when client server functionality is not needed for a particular solution.

And finally...per the post of Alex..is the security issue! SO IMPORTANT! And so ignored in Access. Its nice to have the functionality of using Sharepoint and SQL, but to offer it as the solution to security just doesn't cut it!

I now see there really is NO end user security model (I guess it was stated in earlier posts, I was in denial). Why can't this be an option?? The old security model was really very good, but totally ruined by the crackability of SYSTEM.MDW.

PLEASE...make this an option, with strong encryption of passwords. It would be SO simple to implement, and by making it optional, even if defaulted to off, wouldn't we have best of both worlds for users across the board, as well as developers?

Are we developers beating a dead horse? Seems our ilk raise the same issues here over and over again, and quite honestly they seem to be pretty much ignored. If things aren't going to change, please level with us now and just dish it out directly. I for one am getting much more involved in Dot Net. As amazing and powerful as Dot Net is, there is still no beating Access for its RAD capabilities (and, BTW, performance!). If things don't change in A12, I'll just bid my fond farewell and uninstall the Office 12 Beta.

An equally unhappy StepUP.

# StepUP said on June 12, 2006 4:19 PM:

Soooory..I meant Alan (Cossey), not Alex!

# Rick said on June 27, 2006 5:26 PM:

I have just found this blog after having been shocked to read that the ULS security is gouged out of Access 12.

Navigation is not security and I think everyone knows that, so please stop calling it security.

SQL server is not a realistic substitute for Jet ULS.  Jet ULS is admittedly imperfect, but (1) you could improve it with very little effort, and (2) we can program around the shortcomings (and have been doing so for years).

PLEASE put it back in!

# Weddings said on June 5, 2008 2:22 PM:

Its been a long, long time since my last post, and I'm truly sorry to have fallen off the earth like I did. I'm re-launching the Access blog today, with a new look and a new commitment to keep the flow up. I'm finally at a point where I can dedicate mos

New Comments to this post are disabled