..:: Access-Freak :: Getting started with Microsoft(R) Access 2007 (Step by Step Tutorials/Samples...)

I will blog on this page about computer related topics like Microsoft®, Access, Java...as well as post things of personal interest. I will try to keep the blog as current as possible, so please check back every now and then. Entries are in order from newest to oldest from top to bottom.

April 2008 - Birthday Cake #4

This is the fourth cake my wife put together. Earlier ones included a Thomas, pirate and a birthday package cake. This cake pictures a barn yard/farm theme with a silo, barn and a bunch of farm animals.

April 2008 - Microsoft MVP summit

I will post pictures and comments at the following site. Check it out if you are interested:

MVP 2008 Summit

March 2008 - Microsoft Access 2007 Runtime updated

<quote:>

I know lots of you are very anxious to get your hands on this build. You can go download the SP1 runtime.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D9AE78D9-9DC6-4B38-9FA6-2C745A175AED&displaylang=en

This version of the Access 2007 Runtime contains files whose versions are slightly higher than the level of the same files that are updated by Office 2007 Service Pack 1 (SP1). For additional information about Office 2007 Service Pack 1 and the Access 2007 issues fixed by Office 2007 Service Pack 1, click the following article numbers to view the article in the Microsoft Knowledge Base:

936982 Description of the 2007 Microsoft Office suite Service Pack 1
942378 Issues that are fixed in Access 2007 by the 2007 Microsoft Office suites Service Pack 1

The Access 2007 Runtime updates files to correct some specific Access 2007 runtime issues that were not previously documented in a Microsoft Knowledge Base article:

* PDF/XPS Export: You may see a message similar to "can't save the output data to the file you've selected" or "The OutputTo action was cancelled", when you try to export an object to the PDF or XPS file formats from within Access 2007 Runtime.

* Text/HTML Export: You see a message similar to "can't complete the Output operation" when you try to export a report to the Text or HTML file formats from within Access 2007 Runtime.

* Pivot Table/Chart Views: Pivot Charts and Pivot Tables do not show the correct results when opened in Access 2007 Runtime.

* Access Data Projects: You see a message similar to "The record source [RecordSourceName] specified on this form or report does not exist", when opening a report in an Access project (*.adp) which is bound to a SQL Server object even though the record source exists.

The international versions of the runtime should be available in the next few weeks (I don't have a specific date as intl testing is ongoing). We will post download links as they come available.

Enjoy!

March 2008 - Access MVP T-Shirts

Get your custom Access MVP gear for the 2008 global summit. Stand out in the crowd with any of these t-shirts, polos...
Keep in mind that you can change/customize color/size/style of any of the shown templates. I cannot guarantee the quality/look of these since I didn't purchase each for myself.

You can browse through the products above or just go to my gallery to see more details. Order soon to ensure that the shirts arrive before the summit. The processing/printing/shipping can take up to 15 days.

March 2008 - Combobox backcolor randomly changing (Acc2007)?

Are you having an issue in Access 2007 where the backcolor of your combobox changes to the backcolor of the form section the combobox is housed in when removing focus from the control? This assumes that the backsyle property of your control is NOT set to transparent. For clarity this image demonstrates the problem:

Don't worry you are not going crazy and you are not missing any property that might be causing this. After some investigations I have found that this might be related to an applied AutoFormat. The behavior ocurres if you are using a 2003 AutoFormat. Open the form in layout view and go all the way to the right under the auto format group of the format ribbon tab. Use the pull down to expand the options and select AutoFormat Wizard...there select the Access 2007 AutoFormat in the left column. This will reset your format settings so you will need to reapply your custom background color but it should fix the combobox issue.

With the recent development in the brawl of HD-DVD vs. Blue-Ray it seems as if Sony will at least win one of its many format battles it fought over the years. Sony must have learned something in the defeats of Betamax, MiniDisc…and seems to come out on top with their support of the Blue-Ray next generation movie format.

After almost all the major Hollywood studios supported the format, other influential companies followed including retailers like WalMart and BestBuy as well as service providers like Blockbuster and Netflix. It even seems that one of the strongest supporters of the HD-DVD format, Toshiba has given up.

Now that we hopefully have the confusion over the two formats out of the way, we will probably soon see a major drop in prices and availability of HD-DVD products.

One announcement I’m really waiting for is the decision of Microsoft to manufacture an additional external Blue-Ray drive for the Xbox 360. Having initially gone with an external HD-DVD drive, which leaves the door open for later adjustments and hearing/reading positive statements about potential Blue-Ray support from Microsoft’s side, makes me optimistic for the near future. It might actually need to be a necessary move on their part if they want to continue to be a contender in the gaming console market.

So what’s up with shuffle algorithms? I’ve recognized that the Zune (80GB) doesn’t do such a good job of shuffling all its songs. With more then 5000 songs, it keeps coming up with the same artists and if it does play new albums then it is almost always the hits from those albums. It seems as if a star rating influences the queue that is created when using the “shuffle all” feature. I tried to get some feedback on this from the few existing Microsoft Zune MVPs but no luck.

I did learn that this is not unique to the Zune player. My SonyEricsson W580i (part of the walkman line) does a very similar thing. With more than 1000 songs, it keeps going back and forth between the same albums/artists. A quick fix for the phone was to actually turn off the shuffle feature but leave the queue in the play list. The phone will have sorted all songs by title, which turns out to be pretty random unless you listen to the Beach Boys who have plenty of songs that start with “Surf…” ;).

Back to the fact that this happens more then I thought it does. I was made aware that WMP (Windows Media Player) has the same shortcoming. This all leads me back to my original question…what is up with the shuffle algorithms? Why is it so hard to make the songs random and maybe implement a feature that does not play the song/artist/album again until all others have been played? What good is it to have 80GB of space when the shuffle feature keeps repeating the same old content? I could have fit 10-20 songs onto a 128MB flash player, too. Am I missing something? If anyone has any constructive comments or ideas on how to fix this problem, please email me from the contacts page.

Were you ever in the situation of supporting an application for several different screen resolution environments? Normally you had a few options you could use to make the application as visually appealing as possible. Some of these are either to just develop in the lowest common denominator (sometimes still a 800x600 resolution) to ensure that everything will fit on screen but also waste a lot of screen real estate for clients with larger screens. Another option would be to use a lot of code or third party implementations that detect the current screen size and adjust the control/object sizes and positions, which mostly costs a lot of unnecessary overhead. Yet a further option would be to create and deploy different frontends with different size adjustments to the specific clients, which results in a lot of development and future update time on your part.

Access 2007 implements a cool anchoring feature that could solve this problem for you all together without any effort on your part. You can find the feature under the Anchoring split button in the Size/Position group of the Arrange ribbon tab when in object design/layout view. You can somewhat see the effect in the little animated gif demo below:

This is obviously still not completely perfect since it doesn't resize the font/content of controls but rather just the controls itself. Though it is definitely a good step into the right direction and it makes it much more easier for the developer to deal with a mixed client environment.

A lot of people have been wondering lately why it is not possible in Access 2007 to export a report to the Excel format anymore. The Analyze with Excel feature has been completely removed. If you manually try to export a report to the Excel format you will find that the Excel button in the Export group of the External Data Ribbon tab gets disabled. If you try to do this through code e.g. with the OutputTo method you will receive a 2282 runtime error: "The format in which you are attempting to output the current object is not available."

There are several different speculations to the reasoning of the removal of the feature. Though no official statement has been made it sounds as if Microsoft had to remove it for legal reasons. Personally I think it was an unnecessary function in the first place. Reports are graphical representations of the actual underlying data. So if you want to output a report then it should probably be to a static format like PDF or SNP that will accurately duplicate the actual internal report representation. Reports can use grouping, filtering, sorting which all will effect the export. If you want to export to the Excel format or some other editable content then why not use the report's underlying source (query/table) instead. You can easily output them with either the TransferSpreadsheet, TransferText or OutputTo method.

Additionally, since we are on the topic of exporting data this is also mentioned in one of my tutorials:

If your Access 2003 application incorporates a custom command bar which includes the Export…/Import… command or you implemented the acCmdExport/acCmdImport from the AcComand Class of the RunCommand action anywhere within your application code you will receive an error within Access 2007. A workaround for this scenario would be to directly specify the export or import with the provided TransferSpreadSheet, TransferDatabase, TransferText, or OutputTo methods.

I recently built a somewhat simple but yet powerful budget tracking application. The goal was a personal tracking application which can be easily used and navigated by computer unsavy users. I opted to utilize the Ribbon in Access 2007 for this. It eliminates the need for a separate switchboard like form. The tabbed document feature additionally keeps everything on the same screen without having to switch back and forth between dialogs. I think the main goal has been accomplished and I wanted to share the resulting look and feel of the application with you. As mentioned it is nothing fancy but sometimes less is more:

Have you ever tried to apply some of the cool AutoFormats that are available in Access 2007 and they just didn't seem to stick correctly? Here are a couple of hints that might help you:

In ACCDB file format created with Acc2007:

- If in design view make sure to select the form you want the AutoFormat applied to. It has to have focus for all properties to carry over. If you want to utilize the formats of the form header/footer then show them first (Arrange Ribbon tab...show/hide group on the right...Form Header/Footer button in the top left) before selecting the specific AutoFormat.

- In Latyout view you can even have a control in focus for all the properties to carry over when selecting an AutoFormat.

- The AutoFormat features (backgrounds...) might not apply correctly. Create a new file in Access 2007 and import all db objects from the old file into it (External Data Ribbon tab...Import group...Access button...). You should now be able to specify everything as you want to.

This question recently came up on UtterAccess forums. The OP had recognized the weird behavior that the default compact and repair feature utilized in a custom Ribbon is removed when the ACCDB extension gets renamed to ACCDR. I've tested this and came to the same conclusion. So how are you supposed to compact an application when it is distributed to clients who use the 2007 Runtime edition? The OP wanted to give the client flexibility to execute the process themselves. Here is the XML I used to test it:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
< ribbon>
<tabs>
<tab id="tab1" label="My Tab">
<group id="grp1" label="My Group">
<button idMso="FileCompactAndRepairDatabase" insertBeforeMso ="FileCloseDatabase" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

The conclusion I came to was to utilize the QAT (Quick Access Toolbar) instead of a custom Ribbon command. Open up the application as an ACCDB with the full version of Access 2007. Next to the QAT in the top left (right of the office button) you will see a drop down arrow. Select the More Commands...option. In the "Customize Quick Access Toolbar:" combobox on the right select For: c:\...\yourApplication.accdb. In the "Choose commands from:" combo on the left select All Commands and find the "Compact and Repair Database" command. Double click on it or select the Add button to move it to the right list box. Close the Access Options dialog. Close the application. Change its extension to ACCDR and open it up. You should now have the Compact and Repair command in the QAT and it should function as expected. I'm assuming something similar should work with appropriate XML code in the Ribbon customization that modifies the QAT. Gunter's page will get you started with that.

There have been sevaral articles on the web lately rumoring that Office 14 will lack VBA (Visual Basic for Applications). This is obviously not true but the news spread like a wildfire. Doing some damage control there have been a few official releases from MS speaking the truth about the future of VBA. Quoting Clint Covington from the Access team blog:

<Quote:>
As someone who is working everyday on Office 14, I can assure you that VBA is not disappearing in the next release of Office. VBA will continue to be a valuable option for developers to customize Office solutions to meet their business requirements.
<End Quote>

Furthermore, Joseph Chirilov from the Excel blog:

<Quote:>
While it’s true that VBA isn't supported in the latest version of Office for the Mac and the VBA licensing program did close to new customers last year, we have no plans to remove VBA from future versions of Office for Windows. We understand that VBA is a critical capability for large numbers of our customers; accordingly, there is no plan to remove VBA from future versions of Excel.
<End Quote:>

Hopefully these news will clear up some confusion and spread just as fast as the initial false releases.

This time my wife wanted to go the elegant route when designing/building the birthday cake. Even though it was lacking a fun theme like the earlier Thomas or pirate cake it turned out to be the best one yet. The cake also allowed her to experiment with new techniques including airbrushing with food colors. As usual everything is edible including the large red bow made out of gum paste. The actual cake is red velvet with cream cheese icing.

Today this website reached a milestone with 10,000+ visitors since its official launch date on July 8, 2007. In case anyone cares I picked out some interesting statistical data collected from visitors:

I just received the good news that I have been re-awarded for yet another year as Microsoft MVP (Most Valuable Professional). If you are not familiar with the MVP program you might find this website interesting. It is a great honor to be associated with so many great minds. I personally want to thank all the Access MVPs for their great support and help through the past year. In particular I want to thank Rob (MS), Micheal (MS), Ken, Teresa, Ken, Robert (for giving me the opportunity to work with the best), Crystal for her everlasting kindness and friendship...Eric for his exceptional support as an MVP lead, Clint and Zac for their hard and dedicated work on awesome products and for pushing me to get done with school that much sooner ;). Last but not least special thanks to each member of UtterAccess forums. I would have not been able to do any of this without them.

With the good news out of the way I can finally take time to plan for attending the 2008 summit. Hopefully school will let me be flexible enough to see you all again.