Christian Bitsqueezer

Niner since 2009


  • The Access Show: Access 2010 demo of Access Services and web databases

    Hi Vladimir,


    no, I didn't, because the only ActiveX-elements I use in my Access applications are those which are already installed on the most client machines like MSCOMCTL and so on. But if I would need it I would simply use an external installer as the MS installer in Access is in general not enough for me. I use which is fast, free and can be configured like I want. Moreover it creates great zips, very small. Here I can insert registry keys and scripts to start before and after installation so it is no problem to copy further files and add them to the registry, if I need it.

    That's what I said: If the best functionality in Access can only be created by importing maybe great ActiveX addins then I can go on and directly use a real programming language like .NET languages.


    Look into the several Office websites - in the most cases real database programmers use Access only for "quick and dirty" or small business applications for which everything inside Access is enough. The most people you'll find there are normal users which even don't know what a database is and far away from website programming, for those people the way MS goes here is the right way - more macros, simple Sharepoint deployment, automatic database creation on SQL Server and back. The target is definitely small business and not big Enterprise databases.


    Ribbons and Navigation pane are clearly a solution built directly from WPF in .NET (because you can built it there with a few lines of code). In my eyes they are a great enhancement of working with an application. For Ribbons you don't need to change anything in size of your application, simply make a doubleclick on the ribbon tab and they disappear like the hideable taskbar of Windows. So in the end you have the same space for your application. The navigation bar can be changed with a lot of possible settings so you can change it like you want - and you are definitely very much faster working with this than with the old database window! The same with ribbons: The possibilities they open for own application and a lot better application design are immense! One thing is that you can move all the buttons and settings of forms which take a lot of space into a ribbon and you need to design the ribbon only once and can reuse it in any form by simply assigning a ribbon to a form. How do you do that in a standard form? Normally you must copy and paste a button to the next form, move it to the right place and most often developers copy the code from one form to the next (because in most cases the code is too short to call a function there). With ribbons it is not necessary as you can get from where the function was called and do the same things for equal functions. If you want to insert a close button, do it once, assign it a form close function and assign it to the form's ribbon. In the next form you don't need to think about it, assign the ribbon to the form und you have ALL the standard functions you need everywhere.

    So, don't blame the really great ribbons and navigation bar, but your own incapability to work with new UIs and new possibilities. Yes, the ribbons need a good external editor to be created - but MS has one in Visual Studio addons and there are at least two other external solutions for this job. And if you really don't want to use them, you can use ActiveX solutions to insert menus, toolbars, status bars as many as you want - directly shipped from Microsoft - and switch off any ribbon.


    So my opinion is: Why should we use an obsolete, moldy UI like in Access 2003 if we can have a vectorbased UI with WPF/.NET?

    Why should we use Access to create a "real" database solution? A client which needs a continous connection to a database server is not a "real world" database in my eyes.

    As I said: It's OK for small projects or small business applications for rapid development. But you will get to a lot of problems if you work with "real" databases with big amount of data. You need a lot of tricks to do this in Access.


    Access = Office = Small Business, Small Projects.

    Sharepoint = Small Business, Small and Middle Projects.

    SQL Server and .NET = Enterprise Solutions.


    My opinion, no try to persuade someone.



  • The Access Show: Access 2010 demo of Access Services and web databases



    I'm a developer of databases since DBase and FoxPro for DOS had their best times. The first Access versions until 2.0 started as a very simple database which never had a real chance against the very much better FoxPro until Microsoft bought them and used their Rushmore technology in Access.

    Since then Access was a real database and began to be "the" database on local PCs or in small network envionments. But in the "big" world of IT Access didn't play a real role, big databases used big database servers and Microsoft saw that now's the time to go on to the next step and bought the first version of SQL Server and developed it to the real great server it is today.


    Access WAS a customer and desktop database and it IS one. Yeah, very impressive to create a 2 TB database with a lot 2GB Access backends - surely possible but why the hell should one do such a job with a clearly desktop database??

    EVERY real SQL Server out there like MySQL, Oracle, DB/2 and MS-SQL Server are very much better in handling of big databases and that's their job - not to present clickycolory nice UIs but fast data with nearly unlimited possibilities if it is done in a performant way. MUCH more than Access will ever be able to do.


    To make it clear: I LOVE Access, I love to create quick and dirty solutions with the easy interface and the powerful new enhancements Access 2007 has against older versions - even if no one here seems to see them. Some things to mention: The new Layout view with a very easy way to create endless forms for example, the possibility to click on ONE button to create a good looking report right from a table or form view and last but not least: The first Access which has a FREE Access runtime so I don't need to buy a developer edition and I can deploy Access databases! If I would develop and sell Access solutions for customers this simple fact should be one of the greatest enhancements of Access 2007 for me to buy a cheap product (compared to other solutions) and develop and earn money with it, if I want. And this could be done by any small business which only buys a professional version or an Access standalone, which in mostly is already the case.


    I read a lot about the ribbon here: Yes, I searched my known menus from older versions, too, and I searched the functions I needed like anyone else - but after working with it for a while and gone back to an older Access I found that working with menus and submenus in the older style is in no way the better UI.

    In a ribbon I can display little help screens even with graphics and I can use and form it nearly like a normal user form. I can doubleclick it to hide it to get the most of the screen and it is simple XML so I can load it from a table of the database even with a SQL Server. Now I can create simple reusable ribbons, group functions into tabbed symbol bars, show special tabs for special forms and a lot more. I could even program some form functionality into it - show me how to do this with a 2003 symbol bar!

    Yes, it would be of great help to have a Ribbon editor directly in Access - but there are a lot good Ribbon UI creators out there. Here's one:

    I bought it and used it, it's cheap and fast, the fine-tuning I can do very fast with any simple XML editor. So what?


    Linking of tables: Come on, is there really any serious developer who links external tables like 60 or more tables MANUALLY?? If you don't know how to link ODBC tables, here's a link from Microsoft:

    With this method you can link any number of tables with one simple loop in VBA.

    And to have the possibility to do this kind of linking in an ACCDB is one of the best possibilities of Access - because you have the possibility to link to an Excel spreadsheet, a MySQL table and SQL Server view at the same database and handle it like any other table. So it's not a bad thing, it's a good thing!


    2 GB file size: Yes, in modern times it would be a good idea to increase the file size but this was not a limit of Access this was a limit of the maximum of 2 GB file size of Windows NT file system. This was the only reason why this size limit exists in former times because Access handles all database types in one file. Other databases like FoxPro saved different files for different types and so it could use bigger sizes. But as someone here created a database in terabyte size with Access you see that working with backend databases CAN increase the size nearly infinitive, if you want.

    But why becomes a database the size of 2 GB or higher? In the most cases I saw a reason is that people saved binary objects into the database - and that's normally no place to save them. If you save links to file ressources it is hard to increase a database of even small business projects bigger than 2 GB - only filled with text. If you compact a database from time to time and handle temporary objects like file imports in other backends.


    "SQL Server is hard to learn and it costs a lot." Not really. Everyone can get SQL Server Express with Advanced Services for nothing. File size 4GB (double of Access). Reporting services with IIS. Everything for free and fully network accessible with as many users as your hardware can handle. So that's not really an argument for most small business solutions.

    And hard to learn? Come on - are you developers or are you not? You don't need to know very much about SQL Server 2005 or higher to install and handle it. If you can work with Access SQL you can do anything you want with T-SQL, too - and after reading some docs you find it very much easier to work with SQL Server and T-SQL than with Access SQL.

    And you learn that such things like database triggers which worked in the very old FoxPro are not implemented since Access 2010 (and it does it with Macros like shown in the demo...). But they exist in SQL Server and VERY much more which you can only dream of in Access like stored procedures or user defined functions to mention only two. Hard to learn? Not really.


    And Access can even more - and with Access 2007, too, even Microsoft has hide it a little bit - Access can be switched to project to directly communicate with SQL Server and switch off the "gamedatabase" JET. Now you can use the table and view editor directly in Access, if you want - so you have your better SQL editor - the one from SQL Server Management Studio. But it's better to do it there because it has a lot more possibilities.

    Access works best as Access project with SQL Server backend - my opinion - and I created a big database solution on an SQL Server 2005 Enterprise Edition for an international business with this combination. It works, and it works really good and fast.


    B U T !!!


    At this point you should think about what do you want to develop and what is the target customer for Access: Access is not meant as a concurrence to any big database solution. It HAS a VBA editor to develop solutions with it, it can even work with object oriented programming a little bit but it is stuck in technology on Visual Basic 6 level with DAO and ADO - which are not technologies for modern database developments - yes, it works, but not more. Did you ever tried to close your laptop while being connected to a network backend? You will see that Access will not work anymore with the connections after you open it again. Because ADO/DAO is an ONLINE technology, which means that it MUST have a connection to the database at any given time, no network break possible!

    Access with JET handles so much overhead for any sort of SQL Server which breaks any performance advantages and must convert any datatype that in most cases you NEED a timestamp column in ANY backend table to get it working in a multi user environment!

    Access project solves these problems - but is hidden and not recommended by Microsoft anymore (and maybe removed in 2010?) and only works with MS-SQL Server.

    I saw that 2010 now accepts 50 (!) conditional formattings, good idea - but did you every tried with the current 4 in an endless form? Open a form with let's say 150,000 records of a backend database, filter them down to 100 and if you implemented only one conditional formatting you can watch the screendrawing and eat some donut in the meantime. Now we have 50 of this - will they be faster? I don't know but I don't believe that.

    Programming: It is no real programming to have only ONE file for frontend programming to do anything inside it. If you develop in a professional way you maybe want to have someone to create the reports, one to create module1, one to create class2 and so on. This is nearly impossible in Access because getting them together into one file with no bugs is a really hard job. So in the most cases we see what the developers really are: Single people who develop normally small solutions for small businesses - not more.

    If you look into the forums about office you see on the other hand SO MUCH people who really have no idea what a database is, what it really means to create a database solution - and they don't want it. They worked with Excel, they heard that a database like Access can solve the Excel problems and can create nice-looking reports, they want to form their Excel solutions into a database and they don't want to learn database programming.

    So for these people (for which I state that they are the most users of Access) the inventions in Access 2010 like shown in this demo here are really great enhancements. I for myself would never program with Macros, that's no real programming language in my eyes, I for myself would have enjoyed if MS would worked more in a better VBA editor with language enhancement than moving the people on to Macros but for the mentioned people this is what they NEED: Have the possibility to simply write some simple statements for simple solutions.

    And if there is a possibility to upload it to a Sharepoint Server to create a web solution with SQL Server (which is normally the base of Sharepoint) and ASP.NET without programming any line of code and without even knowing how to separate a database into front- and backend this is (in my eyes) a really GREAT enhancement of Access 2010 which let me think about buying the new version for me, too. I don't use Sharepoint at the current time which doesn't mean that I will not in future but if a simple wizard is able to create all these objects and code with one simple click than I can use the advantages of Access with the advantages of SQL Server by converting everything automatically without doing anything - and maybe have a good base to get the result and change it to my needs for outside Sharepoint use.


    If I create bigger databases for big solutions I now think about moving on to next-generation programming, which means Visual Basic.NET (in my case as I found it easier than C). You can get the most development tools for free with VB Express, including C#, J# and Web development (ASP.NET).

    I heard a lot about that it is much more complicate to program in these "bigger" solutions. Why? VBA is nearly the same as VB.Net from the base of the language - but VB.NET can do a lot more for me. And the Visual Studio editor is a dream in compare to VBA editor. Most things are immediately reported to me - I don't need to constantly choose "Debug - Compile" like in VBA. And no simple red line if an error occurs but clear sentences showing me what I did wrong and offering solutions what I can do to solve this.

    .NET is more complicate with lots of containers like I read here? Not really. But .NET has what a lot here criticized on the Access form editor: That it has a LOT controls to work with (by the way: You can use ActiveX controls to add symbol bars, menu bars, status bars and a lot more like treeviews, listviews... even with Access 2007).

    .NET has different kinds of access methods which makes it only complicate if you cannot decide which possibility you want to choose. Working with Windows Forms and datagrids? You need only some simple mouse movements and clicks and creating a connection to a server to get a working endless form which is fast. You can get a navigation bar with ready code which is better than the navigation in Access.

    But .NET can do more. .NET can for example create forms with Windows Presentation Foundation (WPF) which allows you to create vector-based forms (!!!) which can be resized and zoomed to any size without quality lost! Please do that in an Access form. It can create forms with modern and stylish interfaces whose look can be changed easily - and if you want, at run-time! As it is a simple XML (XAML), you can save a form into a table of a database and you can change the form "on the fly" by changing some XML on the table to let the users work with a new edition of the same form! Can you do that with Access? Never.

    .NET can be programmed independent of OS so that it don't need to run on Windows, it can work on a smartphone, if you want. It doesn't need any registry entry to work, an application could be copied with DOS if you want. So no disadvantage to an MDB/ACCDB. It can create EXE files and DLL files, and: It is fully object oriented with all (most) powerful advantages of this kind of structured programming which makes code reusable and a team can work together on one project, everyone with his own capabilities. Can you do that with Access? I don't think so.


    I'm an amateur in working with .NET and I begin working with it since some weeks, but everything I read about it and experimented with it by myself let me think "why the hell did I work that long with Access to develop big databases?"

    .NET does the most for me and I can work with ready classes (I read about more than 4000 at 3.0 and we get 4.0 these days...) from programmers who solved the most problems someone can have. In Access I often read from people who asks how to read in a file or get the regional settings of .NET everything is there...


    What I can say for my future in developing:

     - in private and for small projects I will go on using Access with anything it has now, that's enough for most cases for those projects.

     - in business I will only go on if a customer really don't want to use a professional way of development like SQL Server and .NET

     - if I need any backend database with Access I will always use SQL Server as backend with Access project - best solution in my eyes

     - in general I will try to learn anything to work with VB.NET and SQL Server 2005 (maybe 2008 later on, but currently no need) and I think after creating the first database solutions with this I maybe will never do anything with Access bigger than a recipe database...Smiley


    PLEASE - think about what Access is: Not a professional development solution but a desktop database which has grown to a better desktop database with included VBA, network capabilities, ODBC and project. These features are good and allows good and professional solutions, but that doesn't change the fact that Access is an Office product and not a developer product.


    By the way - I currently don't work for Microsoft in any way but I'm a fan of most Microsoft products.


    Which you a happy new year, coming in a few days....