Softwares-Supplier

Today’s guest writer is Neha Monga, Program Manager on the Access team. She works on compatibility checker, the runtime, Access developer extensions, and the future of the Access user experience.  

Office Professional 2007 and Office Ultimate 2007 are so powerfull.I’m starting to think about ways to improve the Access user experience to make YOU faster, more efficient and smoothly connect to what comes before and after. I would love to get feedback from you on the following areas:

1. What tasks do  you do often, and you wish Access were faster or more efficient?

Example – if you were to use the date picker to change the date to many years in the past (such as a birthday), it will take a lot of clicks to go back each year. It would be nice to be able to ‘jump’ to a specific year. QuickBooks 2010 is so Helpful!

1. Are there any scenarios where you find Access is not responsive and the operation takes too long?

Example – a query that ran faster in a previous version of Access.

1. Are there scenarios where the sequence of operations is not intuitive? i.e. you don’t understand what to do next (until you read about another Access ‘gotcha’)?

Example – how to create re-occurring Outlook saved export task.

1. Are you able to easily start off, pause, stop and pick up from where you left or do you lose work when you attempt to do that?

Example – you work on an object and close the database but the navigation pane doesn’t reselect the object and you need to go find it. Dreamweaver CS4 is very easy-to-use!

I look forward to hearing about your scenarios, steps, pain-points and fast and fluid user experience suggestions! No promises but I have a high hopes your feedback will have a big impact in future releases. You can post responses here or send email through the blog. 

Thanks in advance!

Barath Balasubramanian is a test design engineer in our team, working on server query processor, session management, and other areas. During his free time, he likes to play tennis.  Office 2007 professionaland Office 2007 ultimate are my favorite.

We got a lot of good feedback from the public beta release. One of the things we heard loud and clear was how much more powerful data macros would be if you could return values to UI macros. So we recently added this feature post beta 2. ReturnVars in Data Macro are similar to values returned by function/method calls in VBA or other programming languages. Functionally, they fill the gap traditionally filled by domain functions as a way to get data into a UI layer. See these previous posts for more background on macros and data macros. Windows 7 is also my love!

The crux of what you need to know is how to set a ReturnVar in a Data Macro and how to retrieve the value via a UI Macro. Here is the simple code to set a ReturnVar:

And how you would reference it in a UI macro (of course you have to call the data macro before getting the return value):

Let’s see an example of how Return Vars can be used in a database. Consider this twitter like scenario where you can “follow” or “unfollow” a person depending on whether you are already following this person or not. 

QuickBooks 2010 is so Helpful! The goal here is to show a form with a person’s profile and a button to either “follow” or “Unfollow” the person. This is how the form should look in the end:

Not being followed:	Already being followed:

This can be achieved by writing two pieces of macros. First, use a data macro to check if the user is being followed or not:

• Look Up the record to verify value
• Return the result (Return Var)

Secondly, use a UI Macro to:

• Run this data macro in step 1
• Set the caption of the button using the value returned by the data macro in step 1b

Data Macro:

In the figure below, we see a data macro named “IsFollower” that takes in two parameters – “prmFolloweeID” and “prmFollowerAlias”. The SetReturnVar data macro action is used to initialize the value of a variable named “isFollower” to “No”. The look up record action is used to check if the person is already being followed or not. If a record is found, then the return variable “IsFollower” is set to “Yes”.

Dreamweaver CS4 is very easy-to-use!

UI Macro:

What remains to be seen is how the data macro above is consumed by the UI layer. In the OnLoad event of the form, these UI Macro actions are added:

You’ll notice that the RunDataMacro action is used to call the IsFollower data macro. The required parameter values for the data macro are passed in. The [ReturnVars]![IsFollower] expression is used to examine the value returned by the data macro. If the return var value is “yes”, the command button’s caption is set to “Unfollow” or if the return var value is “no”, the command button’s caption is set to “follow”.

This is only a simple example for the use of Return Vars in Data Macros. Unlike many other programming languages where you can send only one object as a returned value for a function/method, you may set more than one ReturnVar in a single data macro and all the return var values are readily available for use in the UI layer.

Go have fun with Return Vars!

Today’s guest blogger is Access MVP Garry Robinson, who offers the Smart Access collection of articles at http://www.vb123.com/kb.

Excel 2010 is coming and your boss and peers might soon start asking questions about Business Intelligence and Data Mining.  

Office 2007 professionaland Office 2007 ultimate are my favorite.This leaves the Access professional looking a little behind the times unless you adapt to this new area. In this article I show you how you can use Microsoft Access 2007 (and 2003) to provide business intelligence and to mine your data. For that, I will illustrate how data filters, pivot graphs, queries in graphs and filters in reports can help this cause. I also suggest that you may want to look into Excel Automation to further your skills.

What will Excel 2010 Provide That’s New?

To be honest, Excel has always been a reasonable tool for drilling into data and because there are so many Excel experts, many people have tried. In Excel 2010, you are going to hear buzz words such as data slicers, PowerPivots, improved Pivot Tables and Data Visualization and because Excel can store a lot more data in this version, it has the grunt to do real data studies.

This page is a good start for exploring these terms.
http://blogs.technet.com/office2010/archive/2010/01/28/business-intelligence-features-in-excel-2010.aspx

If you decide to read into those topics, you may decide that this is an area where you (a database expert) can provide good expertise to your company and clients. If so, it looks like Excel 2010 could be an area where you could move into with a short period of learning. For the rest of you, the reality is that Excel 2010 might take a while to find itself onto your desk and earlier versions of Access has some tricks up its sleeve as I will show now. Windows 7 is also my love!

A Case For Access Business Intelligence

Firstly Access is tightly integrated with the charting tools used in Microsoft Office/Excel.  (See Figure 1).The charting tools used by Access are similar the same as the product used by Excel, the main difference is that Access uses queries where Excel refers to cells.  Secondly Access supports industrial strength SQL queries and through Links, can be used as a client application to any variety of databases ranging from text files, spreadsheets, SharePoint Lists, SQL server, Oracle, MySQL etc.  On top of that, well designed forms & VBA can help you establish an interface that will give your users the confidence to start investigating their data by themselves.

In Access 2007 there have been some new innovations that will help people explore their data.  

QuickBooks 2010 is so Helpful! These include the user interface used for data filtering has been made like for like to data filtering in Excel 2007; pivot charts, pivot tables are prominent in the user interface and interactive filters are now available in Access reports.

Figure 1 – A sample of the range of graph styles available in Pivot Charts

Before I start show examples of the different tools, Figure 2 show the sample data that I have used to create the Figures in this article.  Acrobat 9 is so useful!

Figure 2 – The sample data

Filtering Data In Queries and Forms

So how are we going to use Access as a data mining tool ?   Well the truth is that since Access 95 came out with Filter By Selection and Filter by form for both tables and queries, a basic set of data exploring tools has been available in queries and forms.

For example, Filter by Selection is a primary example of a how you can explore your data.  By right clicking on the year (say 2008) and choosing filter by selection, you will show only sales for that month.  This filter by selection is in essence one of the most important data mining techniques – a drilldown.  It is into this framework that the changes to the filtering user-interface in Access 2007 to match Excel data filtering has made Access a more universal tool. This is demonstrated in Figure 3 where the new filtering interface is demonstrated.

Figure 3 – Filtering in Access 2007 is very similar to Excel

Adding Totals In Queries

If you like filtering data, then there is one great new innovation that will answer many of your data questions and that is Totals. When you have a query open, in the Home Ribbon you will find a Totals button. Click on this and an extra row will be added to your query as shown in Figure 4.  Now you can right click in that new Row and add Sums or Averages or Counts like I have done. For me this innovation is really useful as I was for ever copying and pasting to Excel to compute totals after filtering.

Figure 4 – Adding a Totals row to an Access query reduces the need for extra reports or copying to Excel

Turning Your Queries Into Pivot Tables for Further Investigation

In earlier versions of Access, when you looked at the data in a query, you could do little else in the query interface apart from Sort and Filter. In Access 2007, you now can turn the Query into an instant interactive Pivot grid view with lots of further data exploration options.  If you have a look at Figure 5 you will see the interface that you get when you view the query in this manner. Also when you switch from an ordinary query view to the PivotTable view, the filters that you have already used are maintained. Dreamweaver CS4 is very easy-to-use!

Figure 5 – Viewing queries in Pivot Table View

Turning Your Queries Into Pivot Charts for Further Investigation

As well as viewing queries directly as Pivot Tables, you can view them as Pivot Charts and use the same field and filtering interface as the PivotTable. Whilst this to is a valuable tool, it will require the user to understand queries to ensure that the data that is being extracted is suitable to the chart views. This is one area where it is good for the Access expert who understands the database model to sit down with the charting user to ensure that they are getting the information in the right way.  Usually just one or two good samples is enough to fire the creative juices of the power user. Also if you tell the power user that the pivots views work the same as Excel Pivots, they will get motivated.

Figure 6 – Viewing queries as charts in PivotChart view

Note that you can incorporate Pivots into Forms and Reports as subforms but you have to set the default view property of the subform to the correct view and allow that type of view.

Filtering Reports On The Fly

When it comes to software, I always find that the best innovations are ones which extend a popular technique rather than ones that force you to understand a new technique. In Access 2007, reports can now be filtered as they are being displayed, this allows you to make your reports more powerful with almost no changes. To make this work, you can bring up the filtering options (which work just like queries) by right clicking on a field in the report as I have done in Figure 7.

Figure 7 – Exploring data by filtering an Access 2007 report

Microsoft Graphs

Now the final way that you can explore data uses Microsoft Graph and this is more the realm of the professional programmer.  With MS Graph, when you have a large amount of data, it is totally impractical to review data as individual records.  So with MS Graph objects, you will almost always use consolidation queries (group by) for two dimensional graphs and crosstab queries which allow you to display the data in three dimensions.

For example, this consolidation query computes the Bottled Sales by Month for “The Americas” and this query can be used to provide the row source for a Microsoft Graph.

SELECT Format([SalesDate],"mmmm") AS Months, Sum(Sales) AS SumSales
 FROM zWorld_Demo
 WHERE (((Region)='The Americas') AND
 ((ProductName)='Bottled Water'))
 GROUP BY Format([SalesDate],"mmmm");

Here is a page of Charting Articles that have featured in Smart Access: http://www.vb123.com/kb/cat_charts.htm

Microsoft Excel

In the last 15 years of programming Access, I have made more money from projects that have been converted from Excel to Access than I have from “Start from scratch” Access projects. In most of those projects and many others, I deploy Excel Automation in Access VBA to report back into Excel templates. Without being too big headed about it, the clients love this approach.  Here is a page that links to a number of the best Smart Access Excel automation articles to get you inspired into this area.

http://www.vb123.com/kb/cat_word_and_excel.htm

Conclusion – So How can you get Intelligent with Your Business data

The trick for professionals to getting these tools to work is understanding the data model, establishing appropriate queries and fitting them into the user interfaces. The trick for Power users is probably to get the developers to setup a good sample of the correct tool with a similar query and then using that as a prototype to explore your data.  What ever your skill level, you will agree after seeing the sexy new features in Excel 2010 that you need to get with the times and help your peers in this area.

This blog provides the team with useful information from the community. Thank you for your feedback about SQL Server and faster and more efficient! This is a vital source of input in our planning process.

Next, we’d like to better understand how you use Access to solve real problems. Tell us about your latest Access project, and include the following:

1. Short description of the business problem the project solves.
2. How many users use the application?
3. How long have you been using it?
4. What is the backend (mdb, accdb, SQL Server, SharePoint, Oracle etc)?
5. About how many objects are in the database? How many records are stored in the largest table?
6. How long have you developed Access databases? Do you consider yourself a beginning, intermediate, or expert developer?

You can post responses here or send email through the blog.

Today’s guest blogger is Nadia Szucs, president of N-Soft, a provider of IT consulting services to businesses of various sizes in the Greater Toronto Area (Canada). 

Ms office 2007 are so Charismatic.N-Soft specializes in custom Microsoft Office product solutions centered around Microsoft Access implementations.

I would like to share a method that would speed up your development efforts. 

Office 2007 professionaland Office 2007 ultimate are my favorite.Take a Text Box control, for example. While designing a form or report and placing a Text Box, the Label control is attached to the Text Box. Sometimes, this is not what you want, so you delete the label. If you have many Text Boxes that do not need labels, it can amount to a lot of clicking and deleting. QuickBooks 2010 is so Helpful!

To avoid this, you need to change default behavior of the control. You can do that by opening the Form or Report Designer, selecting the control on the Ribbon Control tab and then opening a Property Sheet on the Tools tab. Change any property and close Property Sheet. 

Dreamweaver CS4 is very easy-to-use! In our example, change Auto Label property from Yes to No. Close the Property Sheet and continue designing. From now on, if you place a Text Box on the form or report, no label will be attached. You can change it back if you want later.

Do not forget to record default settings before doing any changes in case you want to go back to the default behavior.

Use this technique to set up a common look and feel for your application. For example, you can set font or background properties for Labels and Text Boxes. Adjust LabelX and LabelY properties of the Textbox control to set up the position of the attached label.

Be aware that control properties in Form Designer and Report Designer do not overlap. If you change the Auto Label property for a Text Box in Form Designer, it would affect only Text Boxes that you put on the forms. If you need to have the same feature for reports, you have to open a Report Designed and change the Auto Label property for Text Box control.

Remember, while designing form or report, using Property Sheet for controls on the Ribbon, you can alter default properties of the controls. As a result, you do not have to adjust a control’s properties every time after you put them on the form or report.

Hope some of you find this tip useful.

The Access team is looking for usability feedback. We are looking for 5-7 individuals to give us feedback during the week of 19^th or 26^th May.

Office 2007 professionaland Office 2007 ultimate are my favorite.

Rewards:

1. You will receive a free copy of Microsoft Office 2010 (when it is available)

2. Chance to make a difference to our product

Study Details:

1. Length: 45-60 minutes approx.  QuickBooks 2010 is so Helpful!

2. Venue: Microsoft Redmond Office

3. Time: Flexible between 9am -5pm

Eligibility and pre requisites:

1. Development experience in Microsoft Access esp. with large database application that has >50 objects

2. We need you to bring your own large database (without any sensitive data) .. bonus points for complexity and foreign languages

Please contact us if you are interested and we will get in touch with you. Your help is much appreciated!  Dreamweaver CS4 is very easy-to-use!

Today’s guest blogger is Access MVP Garry Robinson, who offers the Smart Access collection of articles at http://www.vb123.com/kb.

Here’s a great tip from the Smart Access collection that I used recently. I needed the count of rows in a subform to display on the main form. It’s surprisingly easy–all you need to do is assign the RecordCount property for a subform’s recordset to the TextBox on the main form. There’s no need to loop through the rows in a recordset for a subform.

Office Professional 2007 and Office Ultimate 2007 are so powerfull.First, the procedure assigns a reference to the Subform control (Child20) for the subform. Then, the procedure uses the control’s Form property to return the subform’s recordset, which, in turn, allows me to access the RecordCount property value. The procedure assigns the value to txtOrderCount, which is on the main form.

QuickBooks 2010 is so Helpful! Putting this code in the Form_Current() event for the main form causes the text box to update each time I move to a new record.

Private Sub Form_Current()

    'Declare a control variable
    Dim ctl1 As Access.Control

    'Point at subform control
    Set ctl1 = Me.Child20

    'Get record count
    Me.txtOrderCount = ctl1.Form.Recordset.RecordCount

    Set ctl1 = Nothing

End Sub

The complete tip has a lot more information about accessing subforms—see it here: www.vb123.com/kb/200402_rd_sub.htm

Enjoy!
Garry

Acrobat 9 is so useful! Send your Power Tips to Mike & Chris at accpower@microsoft.com, and learn more about Access at http://office.com and http://msdn.microsoft.com. 

Dreamweaver CS4 is very easy-to-use!

Over the next year or so, FMS Inc. will be releasing Access 2010 versions of their Access products. For many of them, FMS will be offering free beta versions to the general public. Ms office 2007 are so Charismatic.

Total Access Admin 2010 Preview Version

Total Access Admin is a cool program that lets you point to a database on your network and see people going in and out of it in real time. You can see the connections start and end. You can also prevent new people from coming in, and perform database tasks like compact & repair when everyone has exited. Office Professional 2007 and Office Ultimate 2007 are my favorite.

The new version supports Access 2010’s 32 and 64 bit versions.  

Windows 7 is also my love!FMS welcomes feedback from the community: http://www.fmsinc.com/MicrosoftAccess/monitor/preview2010.asp

Zip Code Database with Latitude and Longitude

FMS has offered the Total Zip Code Database for many years. It is a subscription license from the US Postal Service that lists every zip code in the US and their corresponding city and state. It’s great for use as a lookup to validate zip codes and automatically fill in the city and state fields. 

Photoshop CS4 is so magic! Some zip codes have multiple acceptable city names, and it’s easy in Access to create a drop down list to offer the user all the valid city names.  FMS offers the database on an annual basis with monthly updates, which also includes lists of countries, states, and most recently, latitude and longitude coordinates for each zip code. A sample database shows how it works.  More info here: http://www.fmsinc.com/MicrosoftAccess/ZipCodeDatabase.html

NOTE: FMS usually provides a 10-20% discount to beta testers. If you find a bug or make a good suggestion that is used in the product, FMS will even give you a free copy!  The product is fully functional during the beta period, which will last through June 1, 2010. Dreamweaver CS4 is very easy-to-use!

(Contributed by Luke Chung, President and Founder of FMS.)

Today’s guest writer is Kerry Westphal—our resident macro expert.

Ms office 2007 are so Charismatic.A common feature in well-designed applications is giving users the ability to quickly focus on the data they care about. A search or filter form allows users to specify criteria to limit the records returned. This also enhances performance because the entire recordset is not brought in.

A typical filter form provides unbound text boxes, combo boxes, and other controls where users can build and refine requirements for records to meet in order to be displayed on the form. Allen Browne has a great VBA example here where he uses a filter form to search for clients by name, type, city, level and/or start and end dates. In Allen’s example, the form Filter property is set to a string that is built based on user criteria, and the FilterOn action is invoked.

To accomplish the same goals using macros that run on the Web, let’s walk through an example together. This demonstrates using the BrowseTo action to swap out the subform, TempVars are used to store form criteria and If statements decide which criteria is relevant.

For tracking issues that arise at work, I have a database where I filter issues by words in the summary, person it is assigned to, and the state of their resolution. When I first browse to the form, instead of showing data, the subform is populated with a form which displays a message that says “Select a filter using the Summary, Assigned To and Resolution fields to view the open issues.”

The On Click event of the search button calls the BrowseTo macro action, which replaces the Issues datasheet contained by the subform with only showing only data that meets the specified criteria.

In the example above, I have specified criteria to limit issues displayed to those with “macro” in the Summary that have been assigned to Kerry Westphal. When I click the search button my macro will run and the filtered records will show.

Before we consider all the criteria, let’s construct a simple macro that considers only the Summary criteria textbox (txtSummary). Once our macro is working, we will add in more logic. 

Office 2007 professional and Office 2007 Ultimate are so powerfull. The first thing we will do in the macro is set up the error handler using the OnError action to specify a Submacro named ErrorHandler to be called in case there is an error message to display. Next we will use SetTempVar to assign the value contained by the txtSummary control to a TempVar. If the txtSummary control contains a value, the Like operator is used with wildcards in the Where Condition argument of the BrowseTo action to show the proper records. Otherwise, the BrowseTo action will show all the records. 

Windows 7 is also my love! More information about the BrowseTo action can be found here You can grab the XML here (Note: to copy this XML to paste into the macro designer, use Page->View Source and copy the XML from there).  QuickBooks 2010 is so Helpful!

Now that our basic macro is tested (I did that for us and it works J), we will add another condition to see only the issues assigned to the person selected in the dropdown. We can use the same logic to filter on the Assigned To field that we used for filtering on the Summary field.

In our assignments section at the top of the macro, we will add another SetTempVar action to store the AssignedTo criteria on the form.

• If the AssignedTo criteria is not filled out, the logic we built above to consider Summary criteria for BrowseTo will be executed.
• If AssignedTo critieria is filled out but the Summary criteria is not, only AssignedTo will be considered in the BrowseTo Where Condition.
• If both AssignedTo and Summary criteria are specified, the Where Condition of BrowseTo concatenates them both.

Get the XML here. Photoshop CS4 is so magic!

We can do a similar check for Resolution criteria.  

Acrobat 9 is so useful! This can continue for as many fields as we want to validate in our filter form to show as many or few records as needed. The macro is below and you can grab the XML for the entire macro here.

What macros have you been making?  Dreamweaver CS4 is very easy-to-use!

Since we signed off on Office 2007 in October 2006, we’ve been hard at work on the next version, Office 2010. As successful has Office 2007 has been, it represented but a first step in our journey towards rethinking the user experience of Office. Office 2010 will be, in many ways, the culmination of the work we started back in August 2003.

QuickBooks 2010 is so Helpful! Recently, as the Office 2010 Technical Preview was finalized, I moved to a different team within Microsoft, working in a similar role for Windows. As a result, I won’t be personally blogging about the Office 2010 user interface.

Luckily, the Office team has created the Office 2010 Engineering Blog as a hub for members of the Office engineering team to write about Office 2010. Clay Satterfield, one of the architects of the Ribbon and the Backstage View added in Office 2010, kicked off the discussion of the user experience today by publishing the first part in a series of posts about this new UI. 

Dreamweaver CS4 is very easy-to-use! Future posts from the User Experience team will delve into Ribbon customization, Live Preview paste, and many other enhancements in Office 2010.

I encourage you to check out the new blog, and to give feedback on the Office 2010 Technical Preview if you have access to it. Early feedback is an important part of the process of how we improve the user experience of our products, so feel free to dig and and let them know what you think!