Tips and tricks for designing forms and reports - part 3

re: Tips and tricks for designing forms and reports with Access 2007

grovelli — Sat, 24 Feb 2007 17:49:04 GMT

Hi Clint, what do you think of the new multivalued datatypes considered harmful in Access 2007?

http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/

Has the Access 2007 team made any more modifications/improvements to Access SQL and its adherence to ANSI 99 and 2003 SQL standards?

For example:

Although in ANSI SQL (and SQL Server) you can reference an output-column-name anywhere within an expression, Microsoft Access supports this only within the <field list> of a SELECT statement. Access does not support references to named expression columns in GROUP BY, HAVING, ORDER BY, or WHERE clauses.

Any chance of having Full Outer Joins and Cross Joins in the future?

re: Tips and tricks for designing forms and reports with Access 2007

David McCook — Sat, 24 Feb 2007 20:59:29 GMT

Hello Clint

I like your blog .... very much! ;-)

But I don't like Access 2007: I've tried it, but it's unusable ... and not only for the Ribbons.

I think Microsoft developers have tested this product only with Northwind.mdb on IBM Blue Gene mainframe.

Access 2007 doesn't work properly: too much memory usage, an annoying new look and low, low, low, low interface performances.

I've converted a big, well written Access XP/2003 application to Access 2007 (an evaluate version):

PC: Pentium III 1.5 Ghz, 512 MB of RAM, Win XP S.P2

Front End .MDE 2003 version:

Tipical memory usage: 25 to 30 MB

performances: good performances (acceptable also on Pentium II architectures)

...............................................................................................................

Front End .ACCDE 2007 version:

Tipical memory usage: 50 MB and more

performances: low performances (disappointing also on Pentium IV)

re: Tips and tricks for designing forms and reports with Access 2007

Clint Covington — Sat, 24 Feb 2007 22:19:45 GMT

grovelli,

I don't really want to re-hash the MVF arguments here. From my personal perspective--I find them incredibly useful for many people that would otherwise store "string; string; string."

I can't comment on Access 14 plans. Things change way to much to make any promises.

David,

Thanks for the email--lets try to keep this blog respectful and not full of rants. I want to keep it fun for me :-).

We do a ton of perf testing with dedicated perf PM, development and test engineers. They run hundreds of test cases that stress key scenarios. Given the vast surface area of the product and the richness of the OM we aren't able to cover every scenario--that would be impossible. Beta releases area great opporunity for people to provide the team feedback and bugs on situations like this. We did fix hundreds of issues reported by customers in the beta. This was the most highly tested beta ever with over 3 million people downloading it. But some issues do slip through...

Your experience doesn't reflect what I commonly hear from developers and our internal perf tests. There might be something going on in your application. We typically try to fix these types of issues in service packs.

Lets take this offline and see if we can isolate the problem and potentially get a fix into the SP. Use the comments option to send me your email address with some prelimiarly perf numbers. Open your db in 2003 and copy down the working set numbers (you can find that in task manager) for your key scenarios. Then open the application in 2007 and copy down the working set numbers. This will give us a baseline for comparison. The next step would be to open a support case and have our development take look at the issue.

Sound fair?

re: Tips and tricks for designing forms and reports with Access 2007

Ananda Sim — Sun, 25 Feb 2007 10:57:50 GMT

I just started reading your blog Clint - seen your name a lot from the old days of MSDN - always had healthy respect for the volume and quantity of your writing.

I like your response to David - professional, to-the-point and human. Keep up the good work.

With regards Office 2007, Access 2007, I haven't had enough time to play - I'm running it in a virtual machine so that my production Access, Excel multi-version environments don't get contaminated. I use vms and Altiris SVS to keep things sane and tidy. My previous tests with Altirisi SVS is that it copes with Office 97, 2002, 2003 but fails with Office 2007.

I see Access 2007 has woken up (and so has interest from within Microsoft) from the slumber post Access '97. Here's hoping for good success in the Year of the Pig.

re: Tips and tricks for designing forms and reports with Access 2007

Clint Covington — Mon, 26 Feb 2007 18:10:59 GMT

thanks for the props Ananda. Your name seems familiar. Did you use to post in the Data Access Page newsgroup?

You know, I always feel terrible when we ship bugs in the product. People spend hours and days trying to work around stuff--the team feels that responsibility. Unfortunately, it is part of shipping any software especially something as large as Access. If there is something we can do to fix a perf issue for David--that would be great.

It is exciting to see renewed interest in Access at Microsoft.