Microsoft Access Team Blog

PingBack from http://www.artofbam.com/wordpress/?p=7415

We use access for RAD - I can do things in it 10 times faster than coding it in vb.net.  It has become apparent to me that Microsoft seems to think its used as a glorifed excel.  Maybe some do use for that,  I have no clue why, but to each his own.

I don't see how it should be part of the Microsoft Office Product line personally, especialy since VFP is being droppped

I think have a way to migrate our current apps to vb.net model is a major need, and i think many will agree that we all want to get our apps away from the office model and into a complied code.  What used to be a very good system has become way to integrated into Microsoft Office and problems arise left and right when people don't upgrade their office systems and have access 2007 runtime with office xp, or office 2003, etc.

its too tightly integrated for my liking.  And if thats the direction yoru going, you need to provide us with a way to migrate our aps away from it so we are not so tighly integrated and forced into a direction that has nothing to do with the software product offerings of our apps built with access 2007

It's great to see companies actually asking their customers what they want in products.  I'm sure some amount of that goes on anyway, but the development tools market is getting overloaded and competing for developer mindshare is becoming increasingly difficult.

I think this is excellent. I used to integrate applications with Access as the main tool as well as develop, automate and electronically distribute reports for the Illinois Department of Human Services and would love the opportunity to become involved in a group to enhance Access' capabilities.

Great to see you guys doing some user feedback to help with the direction of Access, and would love to throw in my 2 cents worth if it is helpful.

1. Access is a great presenter of data, forms are very easily bound and the report writer is excellent. As a RAD environment it is second to none.

2. Data access and binding can be inflexible however. In the past we have had to go through porting code from DAO to ADODB, and Jet to SQL  and now the possibility of MDBs and ADPs to ACCDBs.

Where I personally think Access is weak and produces problems for us evolving our products is the runtime binding of data, and this leaves our evolutionary development pathways hindered.

3. All new code we write that is not strictly Office\Access related we try to write in managed code. There needs to be better support in this area to carry us forward into the world of Web Services and managed components.

Points 2 and 3 represent, in my humble opinion, weakness but also great opportunities to carry Access into the future. We are more and more dealing with data from different sources and want fast and effective ways to manage and present it using the latest tools.

A few examples of data binding issues we face:

Without writing your own CRUD (and therefore losing the aforementioned ease-of-binding) you cannot bind to a disconnected ADODB Recordset as the row-state is not correctly maintained. There is an opportunity here to totally leap frog over this issue and go to Datasets.

In practice binding at runtime to even a connected ADODB recordset also does not work for anything except the most basic form and query.

Access does not correctly recognize identity columns in multi-table views in SQL2005 so these are not updateable so avoiding table access permissions in your database seems impractical.

These issues become more as acute we (and everyone) write more and more managed dot net code.

Already we run into issues of handling ADODB objects with ADO.NET objects and wanting to span transactions over both etc.

Also if you could bind forms natively to XML or dot net Datasets then most of the above issues go away.

I agree with M. David Matney about the productivity of Access as a RAD environment and we want to be able to maintain and evolve our  products without doing anything rash like throwing them out for dot net forms etc (while also having an easier path to go there if needed).

Suggested solution, looking at improving the delineation of Access the presentation layer vs the data layer, and increasing the generic handling capabilities of the Data Access Layer. Access stopped being a combined mash of data and presentation when MDBs stopped being the default mode and we started using SQL Server, so cleaning up a few of these loose ends would be awesome.

Some examples of coding platform issues we face:

An issue for us is runtime cominterop because it is inextricable related to product evolution using managed components.

Due to dll-hell inherent in COM (and therefore VBA), we have taken to latebinding any managed components we use in VBA so that we don't have to rely on our cominterop interfaces in VBA and this has worked brilliantly but it does cause our productivity to suffer due to the lack of consideration of managed components so I don't know if there are options to look at improvements there. Let’s face it though, these would only be a stepping stone to a managed development environment anyway.

Dealing with ADONET data kind of falls into both camps I guess, interop and more generic datahandling.

These are just a few of the areas that we face to maintain and evolve our access applications and try to keep them current and viable.

We love Access but we need the tools to interact with the rest of the IT framework out there such as web services and dot net framework components.

I may post on my views on what would be good in the way of new stuff later, but there are some important corrections to be made to 2007. For myself (and quite a lot of other developers according to discussions I have had), the main things to put right are:

1) Give Access some decent security, i.e. not just a database password (even though the 2007 password is so much stronger than in previous versions). We need the per-object and per-user security that the old workgroup information file-based security gave (though the new one should have the robustness that the new database password and encryption has). All the blurb that came out from Microsoft about security in Access 2007, since it was so tied in with security in the rest of Office 2007, almost completely missed the point. My customers are not overly worried about viruses in Access (has there ever been one?), but do need granularity. Giving a lowly clerk the same level of access as an MD is, frankly, laughable.

2) Make the ribbon movable. With the trend towards wide screens, taking up space at the top of the screen is daft.

3) Do something about the Navigation Pane, e.g. allow multiple columns. For a developer it is naff. The old database window was so much easier to use to find your database objects.

In case that all sounds rather negative, let me say that there are lots of good things about Access 2007. Stuff like 1-3 above spoil it though.

Zac,

This is not relevant to this particular post on the blog, but

1) Thanks for all the information that you are posting. The blog has taken on a really new lease of life and lots of useful information is being posted.

2) Thanks for the Interactive Access 2003 to Access 2007 command reference tool. It is a great help (didn't manage to read the relevant blog post in time to reply there).

3) Thank you for the time and effort you are putting into the discussions with people asking questions and making comments here.

Alan Cossey

I love Access for managing data.  I use it for bulk updating and reporting.

I am missing a way to publish my databases/applications to SharePoint without needing Acces for the end-user - something like a combination of InfoPath and Access in one product.

The ribbon MUST go, we MUST be able to create applications in the next version that have nothing of this new interface, UNLESS we want to use it.

The new interface artefacts are a complete disaster except for people that just dabble with data like they currently play with data in Excel and Sharepoint.

We have dumped all development in Access 2007 and will NEVER support this immature product.

The next version MUST also expose the underlying structures in the inane COMPLEX DATA on the relationships window.

If the next version is less RELATIONAL than Access 2003 then goodbye.

We use Access 2007 and are having many problems with running reports based on queries that get data from an Oracle database through ODBC.  We have used both the Microsoft and Oracle drivers to link.  Some reports run without problems and others take 10 to 15 times as long to run as they did on Access 2000.  It does not help to convert the files to 2007 format.  What is the question that I should be asking?

vtj: Have you tried minimizing the ribbon before you run your reports?  We've got some known perf issues in RTM that will be fixed by SP1.  

Thank for the suggestion.  Unfortunately it doesn't make any difference.  Two questions: When will SP1 be released and is there a better group to discuss this issue?  Thank You again!!

See my post @ http://blogs.msdn.com/access/archive/2007/10/12/do-you-sell-and-access-application.aspx#comments.

I desperately need you to do the following:

- FIX OLD BUGS!!!

- forms: incremental search (even for combo-boxes!)

- reports: automatic reports based on Form's Recordset (with grouping, totals, saving report settings, etc.); will send you some screen-shots of my A97 solution

- reports: selectively print a page of a "duplex-designed-report" (with page-breaks) using a non-duplex printer, see "Duplex printing" @ microsoft.public.access.reports (Dec 28th 2006 16:28) by Vladimír Cvajniga

- queries: double-click a query-window in design view should open an appropriate query in design view

- forms: achoring columns

- documentation: missing DoCmd.RunCommand commands documentation (SysCmd too)

- developer extension: support for more languages (I desperately need Czech!)

- VBA code composer (wizard - like in Access 97).

- make some research on database corruption and (finally) fix the MDB-project-corruption bug;

 if you are not able to fix the bug: think about storing project objects in ASCII-files instead of MDB to prevent corruprion

- create EXE instead of MDE

- forms: add a pure container control (and some shapes, too)

- enhance TabControl to become a true TabControl:

 - a tab should become a container for all controls including TabControl incl. .Move for all its components at once

- enhance OptionGroup so that it becomes a real container incl. .Move for all its components at once

- in new versions: new options should default to deafults of previous version!!!

- bring back A97-help system; A2002 was user un-friendly with many blank screens !!!

- enable column-locking in multirow forms (same as in datasheet view, see FrozenColumns Property)

- international versions: missing documentation of original English names (properties, menus, ...);

 it's very difficult to search for an appropriate help without correct translation

- change object size & font size according to screen resolution; don't forget bound & unbound pictures

- enlarge Access system windows so that they can display more than 4 rows of anything (selecting folders, etc.)

- enlarge controls in Access system windows so that they can display full path for long file names

- enhance ActiveX-list-tool

- help should include all topics, basic & advanced; all topics should explain functionality in detail !!! See VBE Property in A2002: help doesn't explain what is VBE !!!

- table designer: put all the stuff in one row so that there's no need to switch between fields (multiline) and tabs

- enhance documentation, eg. RunCommand, SysSmd, ...

- enable PDW internationalization!!! I desperatelly need Czech install for our customers in Czech Republic!!!

- translate VBA environment & help to other languages (Czech is preffered)

- form-design: double-click coud open code module in default event procedure

- desperatelly missing zoom in design view

- forms - export to XLS: exclude unbound controls placed in form's header/footer

- code: more rows for properties, events

- enhance compatibility among different versions of Access or Access RunTime on one PC

- enhance error handling; add error number to each error message box; tell us where exactly an error occured (which form/report/module, which sub or function)

- remove brackets from main Access window, eg. This is my application - [Some text here]; it's UGLY

- forms/reports: add a property SaveFilterOnClose (boolean)

- forms/reports: remove that stupid "save everything on close"-behaviour; do it as they do in VB - they never save dynamically changed forms

- options form: make Set Options strings selectable

- exact error identification !!! (form/report control event property, class module, function or sub name)

- add a tool to handle possible errors (see Total Access Analyzer, http://www.fmsinc.com/products/analyzer/index.html)

- forms/reports(?): add Reposition event (for main Access window too)

- missing an easy way to get measures of scroll bars and record selector (and maybe some other items based on system metrics)

- edit: add a possibility to put an expresion and evaluate it on exit, eg. =45*2 <Enter> equals 90

- enlarge MDB maximum size; 2GB is not enough in this modern era... 100GB shold be enough for another 10 years ;-)

- completely remove AutoCorrect since it makes Access BUGGY and UNSTABLE

- add interval keys

- add -1 to OrdinalPosition for non-existing member

- help: A2002 help is a crap as you may know... I'd appreciate working help with no blank screens

- tree structure projects, ie. from main project with common functions I can open a subproject (and then subsubproject, etc.); projects join in memory

 Main.mdb (or mde): Call SubProject1.mdb (or mde) in a subfolder SubProject1; return to Main when SubProject1 is closed

                               Call SubProject2.mdb (or mde) in a subfolder SubProject2; return to Main when SubProject2 is closed

                               etc.

 plus a catalogue functionality (paths stored in external Main.cat MDB), ie.:

                                Call SubProject2 from SubProject1; return to SubProject1 when SubProject2 is closed

 for tree projects see PC FAND: http://www.alis.cz/fand/index.jsp (Czech only; they have EN version of PC FAND)

 PC FAND is a DOS relational database system

Simon Francesco wrote:

"Access does not correctly recognize identity columns in multi-table views in SQL2005 so these are not updateable so avoiding table access permissions in your database seems impractical"

???

I don't have any issue between Access and SQL2005 Identity...

Bye

- Zoom in report design mode is my Chimera!

- Shapes in forms and reports

- really Office compatible Reports (export in Excel or word)

- visual dependencies in database form, forms and related queries, tables and dependent queries, and so on. Best would be a grafical interface than Relations.

I must first start by saying Access is the Best RAD program I have ever used.  As a sole developer in Access I was able to completely build, support and maintain a high end accounting application with over 350 tables, 1450 queries, 550 forms, 350 reports and 100 modules.  This started with Access 1.0 and evolved greatly when Access 97 shipped and has gotten better ever since.

Back in 2001 we decided to rebuild the Access based accounting application in VS.NET.  Today, we have a team of 25 programmers building and maintaining the equivalent accounting application in VS.NET compared to only 1 programmer in Access.  I can without a doubt say that Access is by far easier to develop in compared to VS.NET or any other program on the planet.  If Access was not part of Office and could be compiled as an EXE it might be taken more seriously by the developer community.

The only real limitation in Access for our application is the number of users that can connect to it over a network.  Most everything else we have been able to work around pushing Access to the extreme.  To this day we continue to sell and develop both our Access based accounting application and our .NET based Accounting application and have no plans on ever leaving Access because the development costs are so much cheaper than .NET.  That being said, there are several things I would like to see changed in Access one day.

There should be an option to remove the Ribbon control or give us 100% control over it.  Right now we have about 95% control over it and there are certain things that we cannot change which makes it hard to release a completely custom application.  Many of our customers do not even realize our Accounting application is written in Access.

Bring back the Access Database Window or allow the Navigation Pane to display more than 1 column.  With over 3,000 objects it's not very efficient to display them in the Access 2007 Navigation Pane.

The integration with SharePoint is very promising but there are still many limitations.  It needs to be able to handle referential integrity and primary key fields other than an ID field.

I have always thought the subform concept was incredible but today it does not compare with the .NET grid controls on the market.  With some work I am sure it could be just as powerful yet still 5 times easier to use in Access.  To this day I have not used any custom controls in our Access based Accounting application and for the most part have been able to accomplish what I needed with the built in controls.  Yet when working in VS.NET you are almost forced to use 3rd party custom controls for everything.  None of the base controls in .NET or VB6 seem to be able to do what the built in Access controls can do.  This is a big plus when you can simply use the built in controls for everything.

Access has one of the BEST report designers on the market with 2 exceptions. There is no way to save a report as a byte-array (text file) and there is no end user report designer included with the Access Runtime.  One of the most important things to an end users of our application is the ability to customize reports and sometimes even the forms.  If there was a way to save reports (and forms) as a byte-array (text file) then it would allow us to save custom reports easily to a table or as an external file when doing customization work for customers.  That way a user could take one of our original reports and customize it and save it directly into a table instead of the application MDB/MDE.  The advantage of this is if we deploy a new version of our application it will NOT overwrite their custom reports since it's stored along with their company data in a separate database.  Another benefit is that if a customer hires us to customize a report or form for them we could deploy it as an external file that they could import and run from a table.  This would make customizing an MDE possible and very exciting.

There are several companies offering royalty free report designers today (Developer Express, Data Dynamics, etc).  I don't think adding the report designer to the Access runtime would be a big deal it it was done a certain way.  It would be worthless in the Runtime by itself unless a programmer wrote an interface to it.  Another option would be to charge a royalty free license fee to developers who wanted to include it with their application (I would happily pay for that).  It could also be stipulated in the license that if a company wanted to deploy the report designer with the Access runtime that it must be part of an application and not simply a stand-alone report designer.

Even if you never decide to include the report designer in the Access Runtime it would almost be the same if you allowed a seamless way to save a report or form as an external file that could be compiled and run on the fly.  Data Dynamics allows this with their ActiveReports control and it is incredible what you can do with it.  Then the end user could purchase 1 full copy of Access to create custom forms or reports and save them as files that could be imported into another Access database or run on the fly.  This would greatly extend what you could do with the MDE.

In theory if you allowed this with forms as well, you could open a blank new form in a full copy of Access and import a previously saved Access Form.  In doing so it would recreate the form including all Code Behind.  Now if you gave us developers a way to load that saved file on the fly it would allow for all kinds of new flexibility and power.  This sort of touches on the pros and cons of a single MDB file containing all the objects.  I personally like the fact that Access stores everything in 1 single file as it makes backing it up and deploying it very easy.  On the flip side there are benefits of being able to save every form and report a separate file line in .NET.  If you created a way to optionally save those objects as special external Access files it would be the best of both worlds.

I know many developers have asked for a way to compile an Access MDB as an EXE including me.  I really do not see why this would be so hard to do since you already distribute the Access Runtime and have the ability to compile an MDB as an MDE.

Forms/Reports: bring back A97 subform/subreports design behaviour.

Forms'/Reports' class modules: bring back A97 behaviour.

Help: bring back A97 behaviour.

Database window must be always the "lowest" one (ZOrder). In A2002 (maximized mode) one never knows which form/report is open behind the database window.

Steve palms:

"Access has one of the BEST report designers on the market with 2 exceptions. There is no way to save a report as a byte-array (text file) and there is no end user report designer included with the Access Runtime.  One of the most important things to an end users of our application is the ability to customize reports and sometimes even the forms.  If there was a way to save reports (and forms) as a byte-array (text file) then it would allow us to save custom reports easily to a table or as an external file when doing customization work for customers.  That way a user could take one of our original reports and customize it and save it directly into a table instead of the application MDB/MDE.  The advantage of this is if we deploy a new version of our application it will NOT overwrite their custom reports since it's stored along with their company data in a separate database.  Another benefit is that if a customer hires us to customize a report or form for them we could deploy it as an external file that they could import and run from a table.  This would make customizing an MDE possible and very exciting"

I've found this in an italian area:

http://www.alessandrobaraldi.it/DettaglioFaq.asp?IdFAQ=198

http://www.alessandrobaraldi.it/DettaglioFaq.asp?IdFAQ=197

Complete pile of crap Access 2007. Functionality changed or missing for no obvious reason other than to allow Microsoft to rebrand a product.

Any reason for not being able to export a report to excel? Surely a new version of a product should at the very least have the same functionality as the previous version?

Yet again another example of Microsoft ignoring it's own customer requirements.

Still the one good thing is that it's prompted us to shift away from Microsoft's products as quickly as possible.

I am very unhappy with A2K7.  There are so many things that have not changed for the better.  Today, I installed a VS2005 add-in that put the database window back in (sort of) from http://www.avenius.de/index.php?Produkte:DBC2007.

I, too, have a mature app with thousands of objects.  The Nav pane doesn't cut it.  Visually, the cues that worked in picking an object out of the DB window (ie, second column, near the top, name started with rptSome... Ah, got it, double-click) are gone.  I need so many liitle things back.  I used to right click command buttons on an open form, and then open the events and look at the code.  Right-click doesn't expose the properties option anymore :(  Why?  That was good and productive for me.  I have dual monitors, so the code window is open on one, the app on the other.  Right-click cmdSomething, set some break points ect, click the button, watch the execution, mouse-over code variables and get text tips, adjust code, and get it correct.  How do I get back to that?  Why did it get taken from me?!  I have a report that works perfectly in A2K3, that now pulls a null recordset, and throws a run-time error.  Ok but now, in the code window, everytime I mouse over an uninitialized variable I get a POPUP MESSAGE BOX 'NO CURRENT RECORD' Duh.  Text-tip like A2K3 was okay, these popups are annoying.  How do I turn them OFF?  Then, stopping the debugger HANGS the app!  WTF.

Some of it may be me, but I have already had to kill the app ( which runs perfectly well in A2K3 ) a dozen times today because it is hung.

I want multiple taskbar objects, one for each open form/report.  We use those for navigating.  Under Vista, they are GONE (they aren't even grouped under 'like objects' there is just one taskbar icon.  How do I turn THAT back on :(((

There are just SO many little things that are NOT better :(

And then there are the things we all wanted as developers that we didn't get:

1) a nice Access Options checkbox to make new object names use Hungarian notation (ie new command button named cmd1, label named lblTheFieldNameOfItsAccompanyingTextbox  ect

2)The QBE to work for any kind of data exactly the same (visually) as for Jet.  Select tables, drag relationships, pick fields and view!  The QBE and the Reports Designer are two of the very BEST pieces of software MS has ever made.  I REALLY REALLY never want to HAVE to hand edit SQL again, just like I really don't want to edit <htmL></html> in Notepad anymore.

3) Import Word documents and Excel spreadsheets as FORMS or REPORT boilerplates.  The end-users build all sorts of stuff whose LOOK they like.  I want to be able to pull that stuff in, keep the formatting and the select what is labels, what is bound textboxes, datasources for same, what is static images, and then what stuff needs to be dynamic text created on the fly.  Recreating all the formatting is a pain but the end-users want the same LOOK, if they can get it.

4)  I recognize that MS is aiming at end-users (misguided though that is, because it takes more than end-user knowledge to create normalized data)  but give us an Access Options checkbox to optimize Access for the Pro Developer--and then solicit DEVELOPER opinions on what UI changes WE would like.  The UI changes done to A2K7 do not suit developers AT ALL

Nick67

Comments are disabled on the posting regarding the new UI flash tutorial. When I go to http://office.microsoft.com/assistance/asstvid.aspx?assetid=XT102389151033&vwidth=1044&vheight=788&type=flash&CTT=11&Origin=HA102388991033

all I get is a statusbar line saying "transferring data from office.microsoft.com."

Would love to see this. Any way to get a copy?

Margie

It takes a while to load, about 2 minutes on my pc.

Personally, I use Access just for the tables.  I find SQL too clumsy and bloated; Access does exactly what I need, which is a one-file simple database.  The ability to look at it and edit it in a stand-alone app is a major plus.  10+ years ago I used to spend tons of time writing C++ classes to hold data and do file I/O; but Access makes this sooo much easier!

One way we use it in a commercial app is to save data from Fortran (f90sql) for latter access by VB.NET.  VB.NET uses the data from the MDB to display a “movie” of what the Fortran app calculated.  The MDB is somewhat like just another output file.  I am mildly annoyed that f90sql is not being developed; I am even more so annoyed that JET is considered “outdated.”

Another use – we had a training course, and we had to test the folks on what they’ve learned.  I gathered up exam questions in plain-text, word, audio tape, every format you can imagine; I put together an MDB file, ran an ASP.NET web site, and had the site record all scores in the .MDB and print some certificates for the good folks who passed.

Another use for a commercial app – my friend has the user enter some data into VB.NET, save some pieces of it to the .MDB, and print out HTML reports.  The MDB file is again used as an application output file.

A great opportunity has just come up for you folks to get some feedback into the Access dev process. Dany Hoter, one of our product planners who is studying the Access business, is interested in talking to Access developers to understand better the kin