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

Great to see Access alive. I'm using Access for last two years to browse data from linked database and this is what I would vote for:

1. When linking tables from ODBC datasource Access asks if I want to save the password. The problem is it asks for every table. And I have 60 tables. Painful. Would be great to have an option "Save for all".
2. Data modification security option is annoying, give me the option to turn it off in Access settings.
3. Next to the query design option on Create tab give me SQL view straight away. I write my SQL by hand. Clicking Cancel on Show Table and the right click on the tab to switch to SQL View is just awkward.
4. Why can't I indent SQL code in SQL area with TAB?
5. Results view when query is executed, please do it like in MSSQL Management studio, keep the SQL code visible all the time. If I have to modify the query I have to again right click on the tab, select SQL View and then to run the query switch to Design view (not always) and hit Run again! Why it has to be so painful?

rgruchalski: As a workaround for #3 above, you can do the following in Access 2007. Click on the dropdown arrow to the right of the Quick Access Toolbar (QAT) -- that's just to the right of the Office button. Choose "More Commands...". From the "Choose commands from" dropdown, choose SQL Statement Tools | Design Tab. Click on the "SQL View" item in the list below. Then click on the "Add > >" button to add it to the list on the right. Click OK. You should now have the SQL view button in your QAT. It's still an extra button click (open from the nav pane first, then choose SQL view from the QAT), but at least you save yourself the right-click.

I love that hat.

ACCESS on SharePoint Rocks. Thanks, Team.

This Access/SharePoint synergy looks to have the makings of a big hit.  You'll get some fantastic consumer/professional exposure if this could be leveraged on Office Live Small Business and Office Live Workspaces as well.

This is awesome.

What version of SharePoint is going to be required? Also, would we be so lucky as to get this functionality in SharePoint Services? SharePoint is a bit pricy for my customer base.

robh71,

It really is awsome!  I'm not aware of official release info, but it's expected that sharepoint server license will be required for Access Web Services.  I agree that some support for access services should be in wss (sharepoint foundation), as it will surely show small businesses the power of moving Access Apps to the web.

On a positive note, it appears that External Lists in sharepoint 2010 may be available in sharepoint foundation 2010. 

Keep up the great work guys...nice hat!

Josh

Small businesses can hardly afford Share Point. It needs it's own infrastructure and IT support, which is pricey.

While in big enterprises Share Point is so lockdown that hardly anybody can do anything with it. While the idea is great it would be a hard push.

About Access, I still didn't see nice SQL editor, kind of like in SQL Server Management Studio (SSMS).

When my customers ask me to give them a specific ad-hoc query that they can paste in Access, I always have problems. I do not like Access SQL Editor, it is soooo bad, so I write my queries in SSMS. But Access is not SQL spec compliant. For example it does not have FULL OUTER JOIN, and has a bunch of it's own syntax different from T-SQL. If you could somehow unify this, it would be great.

But we probably wont see Office 2010 and SharePoint 2010 in our corporation for several years if at all. So we are converting everything to .NET and SQL Server in the mean time. So it is a great opportunity for Access to loose it's market share to competing technologies.

Great time watching this demo......I'm floored in a few ways but not in the way you might think. I'm an Access developer and have been for almost 10 years......

A few comments and opinions on the "new" Access 2010.....

1) If ever there were a truth about a Microsoft product, it certainly holds true for Office 2007 and beyond....which is, that THIS PRODUCT HAS JUMPED THE SHARK......Microsoft has OVERPRODUCED it.....the ribbon sucks, the new application facelift is virtually unnavigable, and relative to MS Access, you've removed the data window and jacked around all the data filter commands....WTF?

2) MS Access 2007+ is virtually unusable for a developer. It is cumbersome to work in at best. I cannot work in these versions and I REFUSE......I REFUSE to develop a solution in MS Access 2007+ USING MS Access 2007+. For the FORSEEABLE future, I will continue to develop MS Access apps in 2003 and test them on later Office platforms when necessary. If you are going to retard the product with a useless ribbon, could you at least have some sympathy for the f*^&%^ing developers of the product (also known as your LOYAL user base) and provide a user option to return to a "classic version" configuration?

3) Macros have always been a joke (in my opinion). If you are going to develop an Access application for a business, you should be working directly with VBA not macros.....with rare exception the only macro necessary to use in VBA for Access is the AutoExec macro which kick starts the application with the opening of the main form. But in Access 2010 you've REFOCUSED efforts to further dumb down the app by employing all that * interface for macros? It's an overproduced waste of time......

4) It's great that you want to reincarnate MS Access from a WinForm solution to a hybride WinForm+WebBased solution.....I'm ecstatic that Microsoft is not going to deprecate Access from the world at large since quite frankly, it's the only damn product that Microsoft ever TRULY got right.....(you could argue that Front Page was another extraordinary MS product but where is it today?) That's right, Microsoft has shitcanned that app for a clumsy version of VS Web Developer........again, another product that jumped the shark....back to the point....MS Access 2010 has been reincarnated to work seamlessly with SharePoint.....great.....give me an add-in that I can use for MS Access 2003 and I'll use it....otherwise that shitball of an application is useless....

I hate to be so hard on the hard-working efforts made by MS devs who have contributed to this product but, I have to tell you that you are developing this application IN ALL THE WRONG DIRECTIONS......

AS an aside, VISUAL STUDIO devs could learn a few things by employing some of the elegance and simplicity that comprises user navigation and UI in MS Access 2003. Let me see, let me develop an app using VB.Net and try to connect to a database, develop FLEXIBLE reporting and then hand it off to the user.....let me do the SAME thing in MS Access 2003 and the entire effort is about 70% less work.....VS 2008 database connections include fucking adapters, binding sources, datasets, table adapters, etc.

Let me SCREAM AT THE TOP OF MY FUCKING LUNGS for some functionality pieces in MS Access that developers around the world are looking for......it's another waste of keystrokes here though because no one at MICROSOFT IS LISTENING....but if this rant doesn't get canned from the Channel 9 site at least it will show some semblance of freedom of expression here as well as openness to L I S T E N.

SOME OF THE MOST POPULAR MS ACCESS FUNCTIONALITY THAT THE ACCESS DEVELOPER WORLD REALLY WANTS:::::::

1) Ability to convert mdb or accdb into EXECUTABLES

2) Ability to encrypt and/or lock tables separately or all of them simultaneously

3) INCREASED SELECTION OF CONTROLS FOR MS ACCESS DEVELOPMENT

4) Increased file capacity from 2GB to say 100GB

5) Better documentation tools integrated into the VBA Editor (Ex: ability to export COLORIZED VBA code documents)

6) More datatypes added to enrich what can be stored in Access tables

7) Better (MUCH BETTER) graphing capabilities for charting data

8) Increased VBA language capabilities that parallel VB.Net

There are many more needed improvements but the aforementioned is a start.

Now, MS decision makers, go back to your development shacks and forget these opinions were ever expressed...

It sounds like you really don't want Access. You want SQL Server + Visual Studio.

Re SQL editor. Gee even Find and Replace would be major advancement. How basic is that?

We are still using Office 2003, because people are scared of that ribbon UI , because it looks very different (e.g. execs say needs training -> time & money -> x Number of people (45000) = lots and lots of money and now everybody is scared. )

Actually if you use right tools it is a lot faster and easier to develop in .NET and SQL Server. And your code is better organized, clean and maintainable. Access code is hardly maintainable and it is the biggest expense in in-house development.

Access big problems are:

- bound forms, which you don't have to use, but many people do and everybody (users and devs) get confused as to how not to mess up undelying table.  If you like to get rid of these forms you need to use ADODB which is very cumbersome in VBA as oppose to .Net.  In .Net you create a proper business layer and map it to your database. No need for table adapaters and all that junk. Just use enterprise library and execute reader to fill in the objects' values. Your objects implement either IObservable or INotifyPropertyChanged and your collections implement IObservableCollection<T> or IList<T> and WPF/Silverlight pick up changes and refresh UI automatically. All you need is to issue proper Commands to ViewModel which implements ICommand interface. Everything is nice and clean, and loosely coupled => very maintainable.

- forms' properties are not searchable in Access . in XAML everything is in a text file you can search for any form property and no surprizes as to "ohhh that property had a query".

- proper constructors and initialization of forms. I see a lot of code where people use globals to pass values between forms... that is just wrong. Now you constantly modifying global state, and who knows what it is at any given point in time.

- SQL text editor is very bad, not SQL spec compliant.

- SQL is not native citizen in VBA.

- no multi user support, unless you go through SQL Server.

- when you debugging Access form and click to save your code before stopping debugger you've got some of the values saved within the form, and since it is not searchable good luck finding a bug next time around. That value is going to sit there almost forever.

I would just throw out Access DB engine and VBA and replace it with SQL Express and .Net behind the scenes.

Now in .Net if you have your BL designed, then design your ModelView and you can bind Silverlight or WPF on top of it easily. Reports go into reporting server or local RDLs. If it is Silverlight then it is very easy to distribute, just publish  update to the server and that's it. If it is WPF you can use click once or XBAP, same thing very easy and users have to have least privilages.

FoxPro, FoxPro, FoxPro!!! It is dynamic, it is somewhat functional, it has native SQL (like LINQ) and it was developed long time ago and mainstream programming platforms are only catching up with these ideas. It's got problems like security, and lousy controls' library, and lack of good data management, unless you connect to SQL Server, then most of your problems are solved.

People forget good things...

I have yet to meet people who like Access.

Haven't been this excited about Access since I got hold of 2.0 (a long time!) - what a great job you guys have done with this product. The ability to zip from a concept into an app into a web page is amazing.

Looking forward to more posts on this product.

Nice effort.

How does this compare with EQL Data?

It claims to do about the same thing already.

Brice, I fell your pain. I've been developing in Access for over fifteen years.

Rather than create these silly ribbons and new macro techniques, why not fix existing problems?

How about creating a sortable list box? What about a list box with sizable columns?

Give us a list box with the ability to make a row with forecolor or back color a different color?

Its looks like 2010 is another 2007   I'll stick with A97, A2K, A2003 (Heck, I'll even use A95).

Couldn't agree more with Brice It - thanks for taking the trouble to be so specific (perhaps a little TOO explicit, but it's understandable!)

We are an ISV stuck in Access 2003 because 2007 was such a pain. The ribbon concept being one problem (but there are others) - as an ISV we want to hide anything other than our GUI.

Clint - are we on the wrong track here? Should we be moving away from Access for our application (mainly small-business CRM - up to about 10 users mostly). We need a front-end DB as well as a back-end & Access has served us well. VB.net would not give us what we want & we're keen to use 2010 if suitable.

Here's my question - are MS really standing behind Access for development of commercial apps or is the focus now just for internal SME use? We're about to spend a squillion dollars on the next release. We really need to know if we're on the right track. Please be honest - should we quit now and move platforms?

Appreciate your time...

Rgds,

Chris.

Chris--it is really hard to give you advice about your particular app without understand a ton of other variables. There definitely are people that make a good living selling Access applications. Some examples include:

http://www.fairsoftware.com/

www.EquineMax.com

http://www.easypayroll.net/

These customers have done really well with ribbons. I think all are very excited about the fresh new look it brought to their product.

We absolutely see small business as very important to our long term bet especially for hosted solutions where small business don't want to maintain servers. I would love to take this offline and talk with you more about our opportunities for partnering in the small business space--my email address is c l i n t c ATSIGN m i c r o s o f t DOT c o m.

I'm not saying that Access is perfect for every ISV project--it simply isn't but there are some scenarios for ISV where it makes a lot of sense.

Regards,

Clint

This all looks very promising, but Rocndav's ListBox suggestions are very much needed for developers that are using 200X versions, specifically conditional coloring of each cell.  I use the ListBox callback function for disconnected lists and the function does not allow you to conditionally change the forecolor in the acLBGetValue section without reinitializing, and they didn't include the [{color}] formatting in the acLBGetFormat section as as solution to this either.

Clint -

I just wanted to thank you publicly for taking the time to answer my query. Those links were helpful, too.

Also, I've just returned from Office DevCon 2009 here in Australia and have to say I was impressed by the way Access is heading.

The guys from Redmond were great & our confidence is restored! Yeah, the ribbon's a pain from a developer's perspective but there are ways around it and lots of great features in Access 2010 that we could utilize to have a very slick commercial product.

Thanks for the offer of direct contact - will take you up on it.

Cheers,

Chris

Hi, I'm not a full blown developer, more of a power user. The new stuff looks good to me.

However, can we publish to non Sharepoint sites? I for one cannot afford the licence for SP. So I imagine, a lot of others too.

oX.

I also refuse to develop a database for users using 2007 for development.  Layout and forms design might be little better but the whole UI sucks compared to Access 2003 which is way easier to develop on.  Seriously give developers the tools they need to make great databases, not for these people who don't know what a database is from a excel spreadsheet.  True databases need strong forms, datatypes like SQL ,Great reports, VBA macros with strong intellisense and data macros.. and it should way easier to create and edit queries, and the SQL editor is not even up to SSMS standards, nor does it have any functionality like Notepad for crying out loud.  And why cant Access themes all the controls a certain way, and why cant we write native SQL, why cant VS2005 QBE be included instead of the garbage query creator, and why cannot we create queries like the SSMS app.. and why cannot the navigation bar not be so hard to see the objects, there is too many customized objects view and too large of text, and too big of icons, and no tree like SSMS view of a SQL Server database in VS.  And why cannot security be simple to implement like a SQL server access.   And why doesn't access have F5 to run querys and why cannot I write a  SQL statement in VBA to read data from a table like I would in a query.. why is everything not compatible with TSQL when porting the SQL behind a JET QBE query, and why doesn't the ODBC use a one time security password for the connection and not to all tables..  why do I have to go through so many screens to import a table, and why is VBA not color coded to make our life easier.. and why cannot I reference a table field in VBA by just doing: table.field.value = where record id field = my index number.  

All this these so called features in 2010 are for dumb end users who cannot see and are blind and these examples for databases are always like I build a form with a table in design grid, that is my database.. no real develop creates an Access databases that way.. .  2007 only made the end user feel better about using Access.  It did absolutely nothing to help developers make better databases or make development of databases easier.. and I agree with the previous comment..   even the import wizard is a piece of crap.. I've tried to import over 40+ different Text and XLS file formats and had to modify most by hand before Access could even import half of them with any type of success rate to get past the wizard.

I need SQL DataTime Stamp as Access Table datatype..  Date/Time does absolutely jack for tracking timestamps formatting a field control or input mask or display format of the table column doesn't do anything for this.   Hire some real app developers from the SQL team to create a application that is better for developers.. not office secretary.  Take your head out of your *, this application needs a smarter Project Manager, who actually knows developers hate Access 2007 with a passion, and don't think MS listens to www.MakeOfficeBetter.com

All the examples on the Access Blog, are from people who don't know UI design and build garbage databases..  I've only been developing databases in Access for 3 years, and would like a really IDE for creating databases that is powerful, not stupid.   

Having tables is not a database, its just an overglorified excel spreadsheet.. come on, developers need some real respect from MS. 

UX designers need to know, UX makes all the difference.. and clearly having 5 versions of Access exactly the same with a new version number is not progress.. then making the next one with less user space, crappy UI ribbons and over zealously database window pane is not progress.. and nobody worries about having the ability to upload access database data to Sharepoint.. that is why its in the F'in database so we can do data analysis, charts, graphs, reports for managers (not the kind at microsoft, but those who care about quality, numbers and productivity, tracking info from materials, production systems, employee performance, employee records, anything and everything.. not some stupid.. spreadsheet as a database. 

Have I said it, you really need to get rid of the Project Managers for Access and Just look down the hall the SQL Server and Visual Studio teams.. to create a better Version of Access. 

If Access was just used for personal at home use, which is not even included in the SOHO version of OFFICE, people at home don't do mini databases, they use Excel, businesses use Access all the time and not sharepoint for anything out side of storing a copy of query data on a portal site.  And no, they are not collaborating on multiple people updating that sharepoint xls file.   Depite what you think people are doing with Access and Sharepoint, we dont develop databases to do that.. its not needed, since Access databases can be accessed on a network drive and locked down with Active directory permissions..  they VPN in to use the database, which Access Multiuser record locking is F'ing broken too.. and has been in Access 97..

Clearly the Access team is very dumb..  fire them all, please for the love of God, make a better access version for developers, PLEASE!!!!

Holy *.

My big question is why did you guys not just come out and say "We have made a quatum leap with the direction we are taking Access..."  and why not add that you are introducing some radical new concepts in writing code (especially converting that code to JavaScript for you)?  Don't get me wrong, I'm ready to embrace this brave new....world.  But putting a "Oh we just added this, and we're doing this now, and you're going to like this but don't mind the shellshock..." face on it all is a bit disconcerting.

I expect a plethora of post release documentation to help us through these changes.  I do NOT expect to go out and buy a whole new set of books to have them explained to me.

Otherwise, I like what I see.  I was just in a downright quandry on contemplating how to move my Access app to the web: total rewrite using Expression Web?  Silverlight?  How much more time would I have to invest.  Then you all handed this little treasure.  Actually it's a godsend.  For now I say screw the costs: let me do my experimenting with the betas, and I'll let you know how we're looking on a bank loan.

I see understandable gripes in this thread.  But once you all understand the little fact (sarcastically) that this is a paradigm shift, you might like what's going on.  I just wish the developent guys would have made that point a little bit clearer.

I agree with Brice in some areas and disagree in others.

It is important for the customer to use the right tool for the right problem. Having programmed in .Net for almost four years I realize that it is a better solution for corporate or enterprise applications, especially where a web app is concerned.  But Access is actually a better tool for small business; I mean busineses in the one to five million a year category.

I like the Ribbon Interface and promoting 2003 is to no avail.  The industry is not going to go backwards.  Reduce and Simplify is the clarion call of small businesses, and Access fits that montra to a "T".  Using the larger tools for problems involving less that a quarter million records is like rolling out an Air Bus when a 737 will do. 

I just wish Microsoft would market it (Access 2010) for more small business solutions. Nobody uses it for receipies and socker games.  Many small businesses need to be using it instead of spreadsheets.  In the hands of a professional developer it can do wonders for a small to medium size business.

My comment on Access 2007 and for the new Access 2010:

1.  ABOUT THE RIBBON AND NAVIGATION PANE

     Previously I really hate Ribbon because I need to learn how to use and program in XML to create the ribbon but when I learned it thru   

     reading and researching, I kind like it now and same towards the ribbon.  Previously I expressed my hate to ribbon on different access

     forums and microsoft blog and forums or discussions but now its ok. Maybe I have to learn more.  What I just need is the complete

     reference material like command idMso and other idMso.

    On my learning and working with Ribbon, I find the reference material available for download in Microsoft.com Site somewhat lacking

    especially for control id ( idMso ).  I need the complete reference for the purpose below:

     a. for customizing the office button and QAT toolbar (disabling them)

     b. for customizing the office button (to change the logo or its picture)

     though it is indicated in the license agreement that the logo should appear there, but I guess it would not matter since the application is

     still recognizable thru its filename extension.  and the "poweredby AccessRuntime2007" will be visible.

2.  SECURITY / MACROS / SANDBOX MODE / TRUSTED LOCATIONS / DIGITAL SIGN

      Previously I also asked Microsoft to dumped this securities for this will disable the application especially on runtime when the startup

      involves functions and macros.  However, upon some researches, I have managed to solve it.

3.  MDB OR ACCDB TO EXECUTABLE FORMAT

     I really dont know if it is possible to create executable file from mdb or accdb format for an executable file is not a database file format, 

     they are totally different format, but I dont know maybe Microsoft find a way to do it since they are the creator of the application.  Having

     the accdb or mdb format into an executable file is much more elegant.

4.  ENCRYPTION / USER LEVEL SECURITY

     On version 2007 and 2010, its encryption method is strengthened.  Actually in everythingaccess.com, you can find a utility that will even

     more change the encryption method to a more powerful one.  However, still every access forum sites that I have joined, the moderators

     there say that the security is likened to a "LOCK ON A SCREEN DOOR".  It is same as USELESS.  They would suggest upsizing to SQL servers

     if we are after for security. 

     But since I am using Access product, I would like to suggest to empower the security of access (the encryption). 

     On the other hand, I am grateful that the ULS has been abandoned because that is really a waste of time and construction.  It is just   

     redundant security to password security.  It is better to have one password security that will satisfy its purpose.

5.  DATABASE FILE SIZE CAPACITY

     On many forums I have joined with, they say (especially the moderators and VIP's) that 2 gigabyte per database is enough but we all

     know that someday it will outgrow and need more space.  They also say that you can use as many backend database files as you want so

     there is no need to ask to increase its size capacity.  What they say could be thru but I think in the long run, it will rise more problem. 

    They say that when you really need more space, you should UPSIZE to SQL SERVER.  But the only problem there is that we need to learn

    another product and that is using SQL Server. And maybe the customer might need to learn SQL too which is impossible for medium to

    small business enterprises. Also that would incur us additional expense.

    Another option is to use MySQL which is freea and unlimited size capacity.  But again you need to learn how to use and administer

    MySQL.

    The MySQL option would be preferrable since the product is free and all you need to do is to learn it.

     If that is the case and if we take into consideration that Microsoft products are user-friendly,  why should Microsoft limit the database size

     of its Access database.  It is futile if the purpose is to bring the customer to upsize to MS SQL because there is MySQL.  Why not satisfy

     their Access customers wants and needs instead so that Microsoft will even more bring their customers closer to them?

     The fact that Microsoft has built new technology (SharePoint) thru internet, but it is also a fact that not all customers use internet. 

     So I would also like to ask to upsize the size capacity of Access database, not only limited to 100GB but even to higher size capacity liken

     to MySQL if Access is to be competitive.

6.  OTHER SUGGESTIONS

     Many users still dont want Ribbon because it is hard for them to create.  May I ask Microsoft to create a wizard or an addin to assist the

     users in the creation of Ribbon.

7.  Maybe there are still many features that needs to be incorporated.  But the above features that are requested are in my opinion and base

     on my experience are the most needed and wanted features in MS Access database application.

     Hoping for Access Development Team to hear us. 

My expectations are so low I will be happy if a Help file is created.  It's kind of important.

I agree with Brice It, mostly.  I too loved the Access 2003 interface and have not yet felt comfortable in the new UI.  Even though I took/passed the certification for Access 2007 I still feel like a stranger when working in it, never knowing quite were to go to find things and feeling like I'm "programming with mittens on".

But I too want to commend all the hard-working developers at Microsoft on the Access team.  It continues to be a great platform for delivering real business solutions.  I would love to implement the new features of Access 10, but I too am concerned about how/where I'm going to set up Sharepoint on our network.  We'll see where that goes.

Finally, I must disagree with Bruce's last thought, that Microsoft doesn't listen.  It's just a question of who they are listening to.  Remember that there are power users, end users who are not developers, who ask for functionality too.  I once heard a Microsoft Access Team rep talk at our user group meeting here in Denver where he called Access a "landing pad for data".  That's what it is for many users and that appears to be the audience targeted by the new version. 

That's not to say I'm happy with the direction, only that I understand it.  In my humble opinion, they have turned Access into "Excel on Steroids", which is great for power users, but not for Access application developers.

That having been said, one could criticize me for not biting the bullet and learning how to manipulate the new UI in code.  I know there are developers who have, who can bend the ribbon to their will.  I guess we old-guys-of-Access need resources that will help us become New UI Developers.

So to the Microsoft Access team I can only say, "Kudoos!"  and "Thanks for all the fish."

My comment on Access 2007 and for the new Access 2010:     Addition to my previous post

The new features presented here in the demo are neat and would be useful in the long run as developers would use it. 

However, those features are in my opinion not very much immediately useful because for many years now, it is a fact that most developers have stayed using version 2003 instead.

It would be VERY MUCH useful and recognized if the password security and database file size capacity were enhance, upgraded, upsized because it is MUCH MORE NEEDED than added new features presented here in the demo.

With these new features, we pay for it which are not useful for us.  But if what we pay is very useful for us, we would not hesitate cashing out money for it. 

Why do I protest to upsize to SQL server?  It is because:

1.  Access database it much more easier and friendlier to use and maintain.

2.  Access database is much more easier to deploy.

3.  Access database is my choice.

Eventhough there is this MySQL that is free and unlimited database size capacity and more robust security but the fact is the above 3 reasons.

Using MySQL is very possible in the very near future but if password security and database file size capacity were not enhanced, upgraded, upsized, that would be enough for Access developers to continue using access database.

codexproject@yahoo.com

Developing in Access 2003 VS Access 2007

I have to say that I have developed for both 2003 and 2007.  However application UI changes 2003 is about 10 to 20 times faster to develop in.  For this reason where possible I develop in Access 2003 or develop in 2003 and convert to 2007.

Access 2003 kicks *

Developing in Access 2003 VS C# and WPF

I developed in four months an application in Access 2003 for global multi national that had over 500 users world wide and over 4tb of Access MDB storage (Smartly segmented database files). 

The application was accessible form the web as well. I have a bit of an engineering background as well so I know a few dirty tricks for getting straight Access on to the web with no special server technology required.

I am sure there are others here who have done more sophisticated applications.  But I like to think I’ve earned my mud.

My application was met to be a working prototype.  However it went intro production 100% bug free.  After six months I was ask to come back and write enhancements to it as the C# developer were behind schedule.  The only bug they had found in my application was an error message at shut down due to the custom role based security system.  

It took to C# developers 18 month to convert the application to SQL and C# and another 6 before all the bugs were removed.  

Again Access 2003 kicks *.

I am not going to go into Silverlight development for business database applications.  As having observed first had development of a three tear Silverlight, C#, MS-SQL solution it is a nightmare and a sad state of affairs for everyone. However see my predicted The Future of Development at the end of my article.

Why Was Access 2003 Is So Good

Microsoft developed Access to dominate the desktop database application market.  This was originally dominated by Dbase and its variants, Paradox and Clarion.  As soon as these products died; were kill off by Access and VB after year 2000.

Around the year 2000 the next major function developers wanted was to be able to use or port Access application to the Web (AKA IIS).  This never happened and Access received only bad cosmetic UI changes since.

Why Access after 2003 Is So Crap

As they had reach dominance in the sector and there were other makets to invest there revenue in.  They has also bundled the application with Office which was another nail in the coffin from a revenue and perception point of view.

Adding Access as a primarily Office product changed the perspective of the product with the goal of being a professional rapid application development tool to an end user tool. 

This bundling meant Microsoft makes very little money selling Access as it is part of the Office Suite.  Yet Microsoft has to provide extensive range of support and tools to allow Access developers to continue developing with it.

Mean while Visual Studio had become a massive cash cow.  Selling Visual Studio, (VisualBasic, C++, J# etc and later C# and Web Developer), Addons, Books, Training and Examines.

Had Microsoft add the interactive Web functionality that Access developers wanted there would have been less investment and upgrading in Visual Studio and this is why Access evolution stopped unofficially between 2000 or 2002.

Why SharePoint and Access 2007 and 2010

The reason Microsoft started adding SharePoint functionality to Access to the determent of the product, is Microsoft want to push a high revenue product SharePoint.  

So expect Access to continue being dumbed down for end users with each version and to become less practical as a development environment. But above all do not expect Microsoft to listen to your request for real would functionality.

The SharePoint functionality maybe what Microsoft want, but having done SharePoint development.   I know any application built on or using SharePoint technology in anyway shape or form is very slow and cumbersome. 

They may make Access and SharePoint look good in the demo, but I doubt if you could find a real world use that would be able to perform responsively and be fully function and professional.

From on experienced and from developer to another don't even think of mixing SharePoint and Access, if your application is anything more than the most basic of solutions.  SharePoint server will crawl along like crippled ants (to quote Tolken).  I’m not going to go into the Architecture of how SharePoint innards work, but trust me. 

The Future of Database Application Development

The future us Silverlight, C#, MS-SQL.  This will be either Silverlight 4 and Visual Studio 2010 with one of the Open Source Frameworks.  Presently Silverlight the Framework available are to immature.  However Silverlight 4 and VisualStudio 2010 appear to remedy this to some degree and the Open Source RAD Framework projects starting to appear on CodePlex and other place will remedy this.

My recommendation is to forget pleading with Microsoft to fix Access anything will be too little to late.  Instead keep developing in Access 2003.  Learn Silverlight, C# and look for a RAD Framework on CodePlex that fits your need.

When the time is right you wll be ready to jump ship to SilverLight.

If you find a good Framework post the project name hear so we can all check it out as the more support he Framework gets the faster it will mature.

Kind Regard

JacksonMate

Different Users have different needs and wants.  One user may not want to use the ribbon because of his own personal views and experience, one user may want to use the new features base on his own views and experience.  One user can create application or projects using different tools, one user can create application or projects using only few tools base on his present knowledge.

While I think, creating program doesnt only limited to those professional programmers.  That is why there is the beginner, advanced and expert programmer.  Thus, the level of knowledge varies.

On the other hand, what is true here is that Microsoft is a company who's objective is to attain PROFIT.  That is why they created or innovate products that would meet that goal.  And we cannot do anything about that.

That is why Sir Clint Covington Sir, I would not mind if the price of Access will increase due to my requested features, because what is the use of paying small amount and nothing or limited things that can be done. 

I guess it will be best for Microsoft to give many options to the customers to increase the chances of sale.

For example:  Sharepoint is great for those who can utilize it but some people may avail of sharepoint but not all, so they choose other

                        option.

                        The other option would be stand-alone Access DB with huge size capacity which will cater the knowledge level of the user

                        The other option would be MS SQL which will also cater the higher knowledge level of the user on server.

                        I think on this way, Microsoft Access programmers have many options which will also cater to their own and customers/client  

                        needs.

                        I think also, on this way, instead of Access users will migrate to MySQL which is free, Microsoft can ensure that their customers

                        have options and Microsoft can make the sale.

P.S.  I am only pertaining to MICROSOFT ACCESS and its users which is the main topic of this site.

Having read the posts in the various Access areas.  It appears there are two type of people commenting. Real developer who are complaining and have been for sometime.  And those who have never written a line of code and are impressed with presentation.

After creating the last 6 years, many applications with Ms Access, I would like to say that after the 2003 version, Microsoft has dicided for sure to change the "positioning" of the Product, aiming customers that have no idea of programming with VBA. Now it is considered to be somehow "A database for dummies".

As a VBA developer I am very very very dissapointed with that. As a developer I need:

- Better security. Perhaps table based security, being able to lock specific tables

- At least 100 GB size limit

- Better development environment for VBA

- To keep the functionality to select between classic view or the new ribbon style

- Better connectivity, without the need of SharePoint

- Better performance. We all know that Access 2003 is FAR FAR FAR FAR faster than Access 2007.

Am I asking too much?

All these are easy for Microsoft to impelent. But as I said before, Ms Access is aiming to Low Profile Users ... Perhaps they don't need us anymore ...

For sure I will try the new version, just to find out if at least one of the above will be corrected ...

Hi,

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:

http://www.ribboncreator.de/en/index.php

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:

http://support.microsoft.com/kb/892490

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 Windows...in .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...

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....

Christian

Christian:

Did you ever try to deploy an Access 2007 application with A2007 Wizard if your application uses ActiveX from Visual Basic? You cannot succeed since the wizard ignores ActiveX & similar stuff. Come back, land on the Earth and read my opinions which have been collected to a Bugs & Wishes database: http://www.alis.cz/relax/download/access/Access2007_bugs.rar

IMHO, first of all, Access developer team should fix all known bugs. Then they can show us porting 1 or 2 table "database" to SharePoint. But do you think that "2-tables-database" is a real world? Access in it's version of 2007 became a toy for so called "power-users" who don't know what database is. I'm affraid that those users will NEVER go to SharePoint.

And finally, why SharePoint - a new unknown & buggy technology from Microsoft? Why they can't support Web Services which rule the Internet and will rule it in the future? No, real admins won't go to SharePoint, trust me. They will not port their fully functional applications to an unknown (and probably very expensive) SharePoint.

Also see SharePoint Nightmare: Installing the SPS 2010 Public Beta on Windows 7": http://oakleafblog.blogspot.com/2009/11/sharepoint-nightmare-installing-sps.html which was mentioned at http://blogs.msdn.com/access/archive/2009/11/24/install-access-services-on-vista-or-windows-7-x64-os.aspx#9929508.

Why should we use several back-ends to make a real database solution? BTW, it's impossible to create relations between tables within different databases. Will such solution work? Will it work fast?

Ribbon? One of the worst thing from Microsoft. ("Windows Registry" is No.1). You don't need to use menus & submenus if you create toolbars (see A2003 and older). Ribbon consumes big part of the screen. With ribbon you must re-design your old forms to fit to smaller area. Ribbon? NO! Please...

QAT? Too many bugs, QAT doesn't render correctly, very difficult to use it with VBA (same for Ribbon).

Navigation pane? I'd rather call it "Navigation Pain". I'd prefer former "Database Window".

Compatibility with older versions of Access? Again, see http://www.alis.cz/relax/download/access/Access2007_bugs.rar

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 http://www.createinstall.com/cifree/ 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.

Christian

I was really excited about the export to Share Point and the browser. For all of my database work I all most always use Access to prototype a solution, i.e. db design, forms, reports, code etc. It is a great design tool!! And you can get your ideas down fast. Most applications I work with are disconnected. When the app is large I transfer it to Visual Studio and Sql Server and some type of transfer mechanism such as ftp, replication, or web services. The transfer is a huge job. What I take for granted in Access takes lines of code in VS. I would love to be able to export the Access prototype to a VS, SQL Server and Web Services solution.

Completely in agreement with brice I continue working with access 2003 for the same reasons and also I think that the use of macros is BIG setback

The examples that I see are on simple things but I  want to see an example of a real update of inventory
first where the record is looked by the previous information (deleted table in TSQL), two options are had exist or do not exists if it exists to add record to assign values to every updated field and to save record

then to do the same thing with the new information (inserted in TSQL) two options are had record exists or does not exists if it exist to add record to update fields and to save record

More or less this it is the work in the real world, in Vb code approximately 20 lines but in macros approximately 20 pages!!

all my critiques are to MsAccess in the role of database NOT as front-end
My last suggestion to avoid this eternal and useless confusion is to put " Access Front End " extension (AFE) to.mdb files front end AND " Access Back End " (ABE) to mdb files with only tables, because by example I send many topics to Kallal about the use of Mysql and Triggers and the answer was "Mysql don't have forms, reports, etc" 

For the record, I agree with MUCH of what Brice It is saying here.  I have been developing Access applications for 15 years and I have EXTREMELY disappointed in the direction the product has taken.  Access 2007 is almost unusable for developing software and I am still using 2003 for my clients work. Hopefully 2010 makes it better not worse.

That said...this is truly a remarkable demonstration...if it ends up working that effortlessly then it will be  a big success.  But sharepoint is WAY to expensive for most of my clients.  

Seth

Web Database

I downloaded Access 2010 beta. I am particularly interested in its Web capabilities and appreciate any guidance and answers to my questions below.

I tried getting free hosting at this site http://accesshosting.com/Free-Trial/Free-Access-2010-Hosting-Trial.htm without success and thus was not able to test its new functionality.

My sincere apologies, if some of the questions are intimidating or seeking answers that are obvious.

1. Can I publish to Access Services an accde format (I do not want any design changes by other users)?  When any users login or run their FE when connected to the web will they get all new design changes for both web and client objects while online? Most of the text I gathered from the net does not specifically address accde online update.
2. Access 2010 has both Web objects and Client objects (Form, Report, Query etc.).  If I am not interested to run in browser, should I just create with Client objects and still get all the benefits of the web link (multiuser like environment).  The reason why I want to create with Client Objects is because I can use VBA + Macros.
3. Are there number of users’ limitation? How many users can run their FE (accde) simultaneously to enter new data or retrieving it online?
4. Are there any rows (in table) limitation, 1 million, 10 million or more rows of records.
5. When I create a new Web database and decided not to Publish it to Access Services, can I still create the conventional split to FE/BE and expect it to function normally (even if I had created all my objects with Web objects)?
6. Is it difficult to create user level security in Sharepoint 2010/Access Services. The usual expectation is to limit a subset of data only to certain users. If I am not wrong, Access 2010 will cache all data into the local table once a user gets online and all is dangerous.
7. Can multiple column primary keys be set? I use this in a number of my tables to prevent duplicate records.
8. In my database, it is not uncommon to run bulk delete and append queries to a data table. Can an administrator still perform this function in web solutions?

Thanks (alcsy8@gmail.com)

alcsy - good questions. Let me take a stab:

1. publish accde - this isn't supported. You can restrict using permissions users ability to modify and create new objects. We have a blog post on this topic nearly done.

2. Client verses web objects. If you want to sue VBA--use client objects. You can mix client and web objects if it makes some sense for your app to have a web interface.

3. There aren't any hard limitations for frontend users. We test 600+ users regularly.

4. There is a throttle for limit of records in the list for data that is stored in SharePoint. I think it defaults to 500,000 but don't remember for user. I strongly recommend prototyping your app against real data sets with real queries to get a sense of performance. It is nearly impossible to generalize perf characteristics because so much of it relies on the design of the app and types of queries you are using. We sort of think of most apps falling well under the 100,000 record range.

5. FE/BE for web apps. i would expect this to work fine.

6. SharePoint has a pretty robust model for security permissions on lists and items. You can do most anything outside of column level permissions. We don't make it easy to get to list and item level permissions but they are available if you work hard enough. Permissions are always enforced when you sync back regardless of what you do while offline. For example, if you update a list offline that you don't have permissions--SharePoint will block the update.

7. You can do it wiht data macros. I have a blog post nearly done on the topic. Expect it in the next couple of days.

8. yes. TEst performance characteristics of the types of queries you expect to write before moving too far.

I will see if we can hook you up with accesshosting folks. Watch your email.

Thanks for the questions.

Hi ClintC

Thanks for this spontaneous reply.

For items 1 & 7, I look forward to the addresses.

For items 5, the capacity of 500K rows in a table seems like a limiting factor to adopt this exciting web feature. Some trading houses I worked with have normalized data of approximately 1 million rows (4 years records). Many use 1+3 years sales data to forecast future performance. A query is only as good as what is in the table we are quering into. How to overcome this?

Thanks for the answers.

First off what a long time it took to finally get to the actual demo.  I see that Access is becoming more and more a part of sharepoint.  What a disapointment!!!!!!!!!!!  There are users and companies that are not able or willing to pay for the expensive sharepoint.  I find it funny how they are now making a big deal about the web functions when there were web functions in the 2002 and 2003 versions of access.  They were called Data Access Pages. Coding them required VB.net knowledge however there was a limited vb.net editor.  I will admit they were not perfect however at least they did not require a seperate package "called sharepoint".  Ever since the 2007 version they have forced people that wanted a web portal to have sharepoint setup.  I don't care for the changes to the macro setup.  I find it a dubed down version of coding editor.  Personally once you get beyond a few small steps such as record navigation the remaining shouldbe done with vb editor.

If they really wanted to impress me here is a list

Increase the size of the allowed database as indicated by others.

If you can have a developer option to package a solution from access into a install cd as a runime event then you can code the option to package it as a webpage directly from access.  There is no reason I can think of to require it to go to sharepoint if all you are doing is creating a web bassed database.

I know I will have more however I do not have the time to list them all now.

As a professional developer I am very concerned about the direction Microsoft have taken with both Access 2007 and now 2010. A product that has been used over the past 15 years to produce robust front ends for database applications is now being hijacked to suit users who will never be able to master the new functionality. The new interface is like developing with furry gloves on - everything takes longer - how about a switch to enable reverting back to Classic mode. 

We use Access as a front end to a business product that can use either Jet or SQL Server backends, depending on the data requirements of the customer. Arguments against using Access because of limitations in data storage capacity are nonsensical and display a lack of understanding of Access as a tool for creating database front-ends. There is no limitation. You choose the backend to suit your needs. SQL Server for large enterprises, otherwise Jet (or any ODBC or OLEDb compliant database).  The real power of access is for developing front ends to data stores.

It is wonderful that the Access team is adding extra functionality, especially the web capabilities. I cannot see any large applications ever being ported to them because of speed but there are some smaller apps that will be greatly enhanced by this.  Also the new colors and templates are great - it really does give us the ability to modernise the look and feel of our apps, a very important consideration from our  customers point of view. We want the ability to present data more clearly to end users and this new version has clearly succeeded in this goal.

A wants list:

How about fixing up some fundamental flaws, known bugs that undermine the whole product.  The fact that legal code can cause the Autonumber field in a Jet table to reset and stuff up an entire application is a major flaw that MS has known about for years, introduced in Access 2002!! How about fixing it?  I realise it is a Jet issue - but??

Also how about providing direct access to the web services currently being provided by the SOAP 3 dlls. As these are no longer supported and will not install on Windows 7 (on my last attempt this was the case)  direct access to Web services from witihn Access would be a great boost for professional developers.

An easier bridge to the .NET platform would also be great. Currently it is via Addins.

The ability to revert to CLASSIC view in the IDE.

The user interface in the product demos LOOK great - how about making the underlying product match them.

Hi ClintC,

AccessHosting folks finally gave me an account. I published a test database into it after running the compatibility check with no issues.

In the web it does not work and I kept getting the message below.

Report viewer failed to set the report parameter defaults for this report. Select new default parameter values from the tool pane.
An error occurred during local report processing.
An attempt was made to set a report parameter 'SMID' that is not defined in this report.

I totally do not understand the message and appreciate if you or others can provide some in sight to overcome it.

The flow is as below.

1. When the program launch, a form is displayed by Salesman.
2. When you click the View Report button a report should open and be filtered to the selected SMID.

The macro behind the button as below.

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/04/application"><UserInterfaceMacro For="Command30" Event="OnClick"><Statements><Action Name="OpenReport"><Argument Name="ReportName">Sales History</Argument><Parameters><Parameter Name="SMID" Value="[Forms]![WT_Salesman]![SmID]"/></Parameters></Action></Statements></UserInterfaceMacro></UserInterfaceMacros>

I also understand that web query cannot do grouping or summing. What is the alternative?

Thanks

Is that Papa smurfs hat?

Hi

my name is daniel chen

i am using access for 15 years and microsoft partner for 10 years

please see my website www.ngerp.net

i am very excited about Access Services and sharepoint

is this feature available for ADP and ADE files as well?

our systems use ADE and sql 2008

if not is there any chance you can add it?

thanks

dchen

HI

I'm not agree at all with your consideration.

Ribbon is a good feature even if you understand how to use it. I can accept that ta beginning it can be hard to set it in A2007, but you could use also many tools to semplify it.

The problem of executability of the file is a false problem, many lenguages use the runtime and at least anyone imports if you use an exe file or a accde/mde file + runtime. You can build a good package and deploy it.

Also the size is not a problem, if you need a database bigger than 2 GB you have to use SQL SERVER, and the integration is complete. Jet/ACE can find difficulties to transfer so heavy data, a db server can be a better choise. In this way you could have also the encryption and all the security strategy of a database server. You have to separate data from GUI, 2 GB for the FRONT END is enought.

About 2010 the problem seems that you not understand that M$ wants build an super user, able to create an quickly solution of data manipulation. We force to use it, I'm happy to do it, as a development tool.

M$ can help us giving other features, like a better editor of Query and solve several problem in the VBA editor, but at least Access is a amazing tool and the web feature can be very interesting (let's hope it will be bettere than DAP).

Good work Clint and your team

MA

--

AccessGroup.it

couldn't agree with you more on avoiding Access 2007+

The interface is horrible for developers. I also stick to Access 2003 and then, when everything works, convert from Access 2007. Even then there are some bugs with 2007 that require special coding (like fields not updating in a timley manner so the user is looking at incorrect information). The ribbon doesn't work with Access, although, having said that, I find it works well with Outlook for our purposes, and VSTO allows us to easily customise, and debug, solutions.

If VBA could be upgraded to some sort of VBA.net, that would be great. I could get rid of all the API calls in my code.

T-SQL is amazing with ability to use procedural logic in joins, recursive queries, temporary tables, refer to managed code... Now if Jet could do all that, I'd stop complaining about the ribbon.

The SELECT statement syntax of Access and SQL Server are different, but you can still do a full outer join in both using the older syntax where you separate the table names with commas and do not specify any join criteria: "select c.*, o.* from Customers as c, Orders as o" for example.

Hi Guys,

I need your help in two points:

• How I can manage the access data on Sharepoint. on other words, I want each user to be able to see specific records or rows  from a table based on his/her Login.
• Is there any format for the text box control such as Password.

Thanks in advance

I LOVE IT

Hi Khaled.

I'm interested in the answer on your question: