Microsoft Access

Download the free Access 2010 Migration Guide

cdowns — Tue, 07 Sep 2010 21:45:55 GMT

If you’re considering migrating from Access 2003 to Access 2010 (or perhaps you’re already in the process of doing so), there’s a new resource on Office.com that gives you a high-level overview of the changes in the new version: the Access 2010 Migration Guide.

The guide walks you through new features such as the ribbon interface, the Navigation Pane, where to find Access Options, and so on. See the other Office 2010 Migration Guides, and collect all six!

Follow-up paper from FMS, Inc. on a popular topic: SQL Azure and Access

cdowns — Wed, 01 Sep 2010 17:14:41 GMT

Two recent papers written by Luke Chung of FMS, Inc. about SQL Azure and Access have created a lot of interest on his blog, as well as a few linked forum discussions. In response, Luke has written a follow-up paper about deploying an Access database once it’s linked to SQL Azure. He has also revised his original paper about linking to SQL Azure to clarify that you only need to install the SQL Server 2008 R2 Management Services program (SSMS) and not SQL Server itself.

Thanks again, Luke!

Luke Chung, President and Founder of FMS, Inc., has written and presented a wide range of topics related to Access over the years. In addition to their many Access related products, FMS offers a wealth of great Access papers, tips, and video on their site.

Power Tip: Scale Access Chart objects at runtime

cdowns — Thu, 26 Aug 2010 18:02:50 GMT

Today’s guest blogger is Peter De Baets of Peter’s Software, providing Microsoft Access tools to developers for over a decade including ShrinkerStretcher, KeyedAccess and many other products and free downloads.

I've recently discovered an easy way to scale Access Chart objects at runtime to fit the form window, and I'd like to share it with you. Scaling an Access Chart means that when the end-user resizes the chart form window, the chart resizes to fit the new window size. It also means that when the Access window and form are maximized, the chart will fill the screen at any screen resolution. Well, it will mostly fill the screen, as we'll see below. Anyway, scaling an Access Chart is a no-brainer - it's a great way to add some WOW to your application with very little effort.

(Before)

(After)

This tip is for forms that contain one and only one chart control (Unbound Object Frame containing a Microsoft Graph Chart object), and are intended simply to display that one chart.

I thought this would be fairly easy - you know, just set the chart control Height and Width to the form InsideHeight and InsideWidth property values in the form OnResize event procedure - but it turns out that there is a hitch. The chart object scales smoothly as the chart control increases in size until the width exceeds about 9.7 inches. Then the chart object undergoes an anomalous growth spurt and part of the chart object resizes beyond the edge of the chart control hiding some of the chart data! Come back, my chart!

The only way to handle this, as far as I know, is to limit the width of the chart control to a maximum of 9.7 inches so all of the chart data remains visible. Limiting the width of the chart control means there may be some empty form space on the right, so the code below also centers the chart control on the screen to make up for this.

Without further ado, here is the code to place in your chart form OnResize event procedure. Remember to insert the name of your chart control where indicated:

Dim wd As Long
With Me!OLEUnbound0 '<-- Your chart control name goes here
    '* Make sure that the chart control size 
    '* mode is set to 'stretch'
    .SizeMode = acOLESizeStretch
    
    '* Make sure that chart fonts scale 
    '* with the chart object
    .Object.ChartArea.AutoScaleFont = True
    
    '* If window resizing causes horizontal 
    '* scroll bar to appear, 
    '* then move chart control out of the way prior to 
    '* setting left, width values.
    If Me.Width > Me.InsideWidth Then
        Application.Echo False
        .Left = 0
        .Width = 0
    End If
    
    '* Set the new width of the chart control.
    '* Chart object can resize beyond the chart 
    '* control when control is wider than ~9.7 inches, so
    '* limit chart control width to 9.7 inches max.
    wd = Me.InsideWidth
    If wd > 9.7 * 1440 Then
        wd = 9.7 * 1440
    End If
    
    '* Set chart control width and height
    .Width = wd
    .Height = Me.InsideHeight - .Top
    
    '* Center the chart horizontally on the form
    If Me.InsideWidth > .Width Then
        .Left = (Me.InsideWidth - .Width) / 2
    End If
    
    '* Set the form width and height
    Me.Width = .Left + .Width
    Me.Section(acDetail).Height = Me.InsideHeight
    
    Application.Echo True

End With

Going beyond Web Macros: Using Event Receivers & .NET with Access Services

cdowns — Tue, 24 Aug 2010 21:36:43 GMT

Today's guest blogger is Access MVP Ben Clothier. Ben is an independent contractor working with J Street Technology and Advisicon and was Technical Editor for the Access 2010 Programmer's Reference.

One challenge in Access Services is ensuring that the logic behind various objects will be portable between Access itself and the SharePoint site hosting the Access web database. To that end, the Access team has used macros to support the goal of portability. In practical terms, this means we cannot use VBA behind the Access objects in context of a web browser.

This in turn posted a new challenge for all of us as Access developers: what if the web macros cannot perform a required functionality for our client?

I was delighted to learn from Richard Fennell, a Visual Studio Team System MVP who has written about his experiences with Access 2010, that we can use SharePoint object model and Visual Studio 2010 to introduce business logic to the applications. With access to the .NET framework and a full-bodied language, web databases need not be limited by the macros. I went about creating a proof of concept to demonstrate how we can use SharePoint's events to provide extended functionality for our new Access web databases.

To that end, I’ve also created a video showing a case study of the solution applied included with this blog post. The video is about 10 minutes long and can be downloaded here: AccessServicesEventReceiver.mov (64 MB).

Visual Studio 2010 and SharePoint Templates

One big enhancement featured in Visual Studio 2010 was how much easier it is now to work with SharePoint object model - there are many templates ready made for different applications within SharePoint including but not limited to web parts, workflows, and event receivers. As shown in the video, Visual Studio 2010 now supports automating of what used to be a tedious and manual process of packaging the solution, adding it to a feature then activating the feature among other things. The setup effort is relatively simple – use GuidGen.exe (a part of Visual Studio tools) to create a new GUID to uniquely identify the assembly then identify what lists and events you want to customize, enabling you to focus bulk of effort toward coding the custom business logic. Once done, one can then choose "Deploy the Solution" and Visual Studio will automatically perform all tasks required to deploy the updates into the SharePoint and thus make that solution usable by all other parts that consumes this solution.

Of particular interest here is a template known as "Event Receiver". In a normal SharePoint setting, you would select a site then choose a list to associate the event receiver with, then choose which events you want to receive and start coding away. Though an event receiver can only be associated with single list, it may support any events built-in to the list and for case where more than list requires custom functionality, we can add as many event receivers as many list we need to associate with into the same project.

When we publish an Access web database to SharePoint, the web tables get transformed into SharePoint lists, enabling us to use SharePoint object model & Visual Studio to customize the operations. Mr. Richard Fennell was gracious enough to share his code required to associate the events with a SharePoint list, which is done manually rather than depending on a wizard which will not work for this application. Using his code, we can then choose any Access Services' list(s) to associate various events with.

Attaching events to the list

In this demo, we want to be able to read data from a MySQL database into Access web database, and we need to be able to do so whether we're using a web browser or a rich client. In rich client, the solution is simple: we link the table. Done. In web browser, we are limited to only lists within the published application and web macros does not allow running code or linking to external data (Note: the absence of linked tables in a web database is something Access team has reassured that they will address in a future version). Thus, we attach a receiver for two events; ItemAdding and ItemAdded. The event receiver is then associated with a list, "Action". On the first form, the HR staff is presented with a filter form where the Human Resources (HR) staff enters the filter criteria to determine what subset of HR data should be retrieved from MySQL. When the "Load" button is pushed, this saves the new filter criteria into the Action list, thus triggering the ItemAdding event.

When the C# code within the event is triggered, We can then examine what was just added, open a connection to MySQL database and feed the filters into MySQL's stored procedures. We can then use DataReader class and in this event, read only one Human Resources record. This then completes the ItemAdding event. By design, all “-ing” events in SharePoint object model run synchronously so the user has to wait for it to complete.

When ItemAdded is fired, it will be running in its own thread asynchronously separate from the thread driving the application, whether running in Access client or inside a web browser. Picking up the DataReader from ItemAdding, it will then continue to add records. In practical terms, this means the user only has to wait for single record to be loaded before the Access Services' own macros will resume after the SaveRecord command, invoking the subsequent BrowseTo macro to open the form bound to the HR records. By the time the form has loaded in the user's browser, at least one records has been fetched from MySQL and inserted into the Access Services' lists, ready for the user's browsing & editing. As the user reviews the HR records, the ItemAdded event continues to run and the user can click on the "Get More Records" to reload the form with new records added by the ItemAdded event.

The best part of this all is that this requires no explicit code to manage threading - this is already handled by the SharePoint, freeing the developer to focus on the business logic within the events. With Visual Studio 2010's enhancements for SharePoint, developing a SharePoint solution targeted toward Access web applications is quite very feasible and a viable choice for developers who requires more customizations and power beyond what the built-in web macros will allows.

Communication Across the Boundary

One challenge that came up was figuring out a way to communicate the progress and the result of the .NET code execution back to the application. As noted earlier, the web application cannot interact with the outside world and .NET code is outside. The solution to that dilemma was to make use of Action list to store information so the code can update it as it progresses through the steps. Here's a glimpse at the Action list layout:

the video will show that as the .NET code steps through a complete personnel record (note that an employee record has three child relationships, so all related records from those three child tables must be fetched to consider it 'complete'), it increments the 'fetched' and update the Action list before it proceeds to the next personnel record. Side note: for performance reasons, it may be better to update the Action list in batches rather than for each record so that should be considered when moving the code to production.

Within the web form's footer, it references back to the Action list and report different status depending on which part of code is being executed. While code is running asynchronously, the footer will display this:

The user is able to press the button to refresh the web form and thus see the latest records fetched by the code since last reload. This basically function as a static progress bar.

Once the code has completed collecting all matching records, it updates the 'matched' column with the final number that also serves as a sanity check that all fetched records were in fact fetched. The footer will then look like this:

This provides the user with very useful feedback on how things are progressing as the user reviews the employee's history. Also, all of this is accomplished using the web macros & examining the Action list's row.

Access Client & Offline mode

Because the event receiver is associated with the SharePoint List, the code will run regardless whether we access the application via a web browser or Access client (or any other client for that matter). However, Access client has the capability to work in an offline mode. When Access client is working in offline code, this may mean the code attached to the event receiver will not execute until the next synchronization. In this demo, this means offline mode would not function for the HR users because they'd be then taken to the employees review form that's blank because the code hasn't fired and won't process until the lists are brought back online Therefore, we must plan for the events where the process depends on code being triggered or not and handling the case when offline mode is enabled.. perhaps something simple as alerting the users that this operation is not available in offline mode, for instance.

Ramifications on Concurrency

One issue we need to consider carefully is how the code will function in multi-user settings. SharePoint's object model is well-designed for multi-users settings, but it remains the developer's responsibility to verify that code will run in such fashion that the changes made to other lists does not interfere with other users' workflow, at least in an undesirable manner. It should be also understood that the changes the code within the Event Receiver makes to any other SharePoint lists is effectively viewable by all other users, which may be desirable or not.

In order to keep things simple, I chose to use a dedicated table solely to receive filter criteria and use only Insert event, which frees me from the need to worry about update conflicts when two users modify two rows at near same time. Another possible approach is to dedicate a row to the user and allow that user to update only their row. Furthermore because the aim of this demo was to provide each user a "workspace" with their own filter, it is necessary to filter which data belong to what user and how to handle the case where a user requests an employee record already "checked out" by another user. Therefore, if this were to go to production, it would be necessary for me to implement some kind of audit trail upon the employees table to identify which row was called by which action_id and thus effectively associating an employee record with the user's requested criteria represented as a row in the Action list. In different applications, it may not be necessary but the onus still remains- the developer must verify that the code will work well in multi-user settings.

Caveats

With great power comes great responsibility. There is a reason for loading only one record in the ItemAdding event, and it's the same rationale Access team used for limiting web macros to precious few actions within the Before Change event of the data macros: ItemAdding is synchronous and excessively long operations can cause timeouts among other problems. This is especially true in context of browsers working in a disconnected state - if it does not get a response from the server, it may time out and users may have to re-perform the actions. Not a pleasant state of affair to be in! There is also another reason for narrowing the scope of web macros: SharePoint server is a shared resource and a solution that requires intensive processing will hurt SharePoint's performance,harming all users' experience. Though SharePoint team has provided many features such as sandboxing and throttles to handle such scenarios, you do not want to be sitting across the table with the system administrator, furtively explaining why you did what you did!

As will be shown in the video, Visual Studio makes it possible to debug and step through the code by attaching the solution to an IIS worker process (w3wp.exe) and you can observe the code in action. However, if breakpoints are set and you step through code, this may cause time out and Access Services will then disconnect the tables in the client and assume that the recent save has failed. Once the code has been stepped through and Access client has been directed to reconnect the tables, it will ask you to re-attempt previous save, but that is actually redundant because save did succeed; just not within the timeout length.

Another anomaly I've observed is when stepping through ItemAdded event (which should run asynchronously) the web browser will continue to wait for the ItemAdded to complete should a breakpoint be raised within ItemAdded. However, it does run asynchronous when breakpoints are not enabled or set within the ItemAdded.

Conclusion

With all this in mind, should our client ever ask us this question, "Can Access Services do this?" we need not confine ourselves to only web macros. For vast majority of solutions out there, web macros will do the job quite admirably. For that last ten percent of the solution, on the other hand, we can tell clients that we can turn to .NET to take us all way home.

Although the demo provides a way to "link" into external data source, which has been indicated that this will be made a standard feature in future version, this isn't the only possible use. Here are few other possible uses to help spur the imagination:

a) Using SQL Server Reporting Services to generate a complex report and email it to the users asynchronously. They only need to fill out a web form then few minutes later, it'll be in their mailbox.

b) Provide hooks into existing SharePoint workflows so Access web applications then participate in a longer and complex workflow.

c) Communicating with other web services.

d) ________ (left blank for you to fill in)

Power Tip: Update front-end database files that have been “pinned” to the taskbar

cdowns — Mon, 23 Aug 2010 15:08:27 GMT

Today’s guest blogger is Tony Toews, a Microsoft Access MVP since 1999. Tony is owner of Tony's Main Microsoft Access pages, Tony's Microsoft Access Blog, and developer of Auto FE Updater and Granite Fleet Manager.

Windows 7 and Windows Server 2008 R2 have some very interesting UI features including the ability to pin files, such as Word .docx, Excel .xlsx, Access .accdb or even Notepad .txt files to the taskbar. Fellow Access MVP Glenn Lloyd wrote an excellent article describing this feature at Windows 7 – Pinning Applications to the Task Bar. Taskbar pinning works well for user-created files including Access database files, but it will cause problems for Access database front end (FE) database files created and distributed by developers.

Front end (FE) database files are frequently distributed as .MDE, .ADE, or .ACCDE files in order to keep users from making too many changes to the forms, reports, macros, and VBA code. Developers can use a wide variety of means to distribute the FE database files from the server to the users workstation. Taskbar pinning means users can then start the Access FE file directly, but unfortunately this can bypass any Access FE file version updating logic you may have.

Bob Larson's free Front-End End Auto-Update Enabling Tool helps solve this problem. His code is imported into your Access FE and is executed on the Access FE database file startup.

My free Auto FE Updater tool now handles this problem with some VBA code that you import into your Access FE database file and run in your startup routine.

Whichever solution you use to distribute your front end Access databases, you’ll likely want to find some way handle this situation as Windows 7 or Windows Server 2008 R2 is rolled out into your workplace.

-Tony Toews

Send your Power Tips to Mike and Chris at accpower@microsoft.com.

Two new white papers about Access and SQL Azure from FMS, Inc.

cdowns — Fri, 20 Aug 2010 16:43:08 GMT

Luke Chung has published a couple of white papers about Access and SQL Azure. You can read Luke’s blog post here, or link to the papers directly here:

Here’s an excerpt from the second paper:

“Cloud computing will be a huge benefit to the information worker and Access community. Instead of worrying about the hardware and deployment issues around applications, one can focus on building the solution and using the enterprise quality cloud platforms which previously didn't exist or were prohibitively expensive and difficult to use. With Microsoft Access 2010 and SharePoint 2010, Access applications (in limited form) can be deployed over the Internet. With Microsoft Windows Azure and SQL Azure, one can create .NET applications and/or SQL Server databases in the cloud.”

Thanks Luke!

Luke Chung, President and Founder of FMS, Inc., has written and presented a wide range of topics related to Access over the years. In addition to their many Access related products, FMS offers a wealth of great Access papers, tips, and video on their site.

Easily migrate Access data to SQL Azure

Russell Sinclair — Thu, 12 Aug 2010 18:23:00 GMT

A couple of months ago, we posted a blog entry on how to use Access 2010 with SQL Azure. At that time, the easiest way to migrate your Access data to SQL Azure was to first migrate the tables and data to a non-Azure SQL Server then to use SQL Server tools to migrate these items to a SQL Azure database.

Today, we are pleased to let you know that the SQL Server team has released a new version of the SQL Server Migration Assistant (SSMA) for Access that will allow you to migrate your data and schema directly from Access to SQL Azure, as well as SQL Server 2005, 2008 and 2008 R2. Just like with SQL Server, you can use your Access 2010 application as the front-end to your SQL Azure database.

More details on the release are available on the SQL Azure Team Blog and Zane Adam’s Blog , or you can download SSMA for Access directly from the Microsoft Download Center.

Power Tip: Use dynamic event callbacks to simplify complicated applications

cdowns — Tue, 10 Aug 2010 15:59:35 GMT

Today’s guest blogger is Joel Graff, Operations Field Engineer with the Illinois Department of Transportation.

Many of the Access projects I’ve developed have used VBA extensively, and the more I became involved in developing user interfaces, the more I saw a need to customize the behavior of the controls that Access provides. Developing this sort of solution gave me the architecture to be able to develop much more complicated control interactions and aesthetics without getting lost in a mess of spaghetti code, ambiguous function references, and endless form/control qualifications.

A demo database is available here, so if you’re interested, download a copy and read on!

Here’s how the demo database works:

Rather than trapping a control’s event in a form module, I created a class module (referred to as a “control event handler”) which has all the event-handling stubs contained within. Of course, the main member of this class is a local WithEvents reference to the form’s control.

Once the event is triggered in the control event handler, the handler then uses the Application.Run method to execute a callback function stored in a separate module. The name of that function is passed to the event handler on initialization and can change at any time in the course of the program. The control event handler passes to the callback the reference to the control itself, an enumeration indicating the control’s type (in lieu of the ControlType property, which not all controls support), and a string describing the event which was triggered. After that is a series of optional parameters containing anything that is passed back from the original event call (e.g., MouseMove has Button, Shift, X, and Y for parameters).

So why do it this way? In most cases, you probably won’t want to. But here’s why I like it:

Once you’ve pushed the callback to a different module, you have to make sure the parameters you need are passed, and you’ll likely need to reference the original control. This architecture allows me to standardize my callback declarations (or ‘signatures’). That is, every callback has three basic parameters: a reference to the control, an enumeration describing the control type, and a string of the event name. Further parameters can be added to the signature for values that are returned by the event, or a blanket series of optional parameters can be added to every callback signature just in case.
I don’t have to load a ton of functions when the form loads. By separating the callbacks into separate modules, I reduce form-loading time.
I can reuse my callbacks. Of course, I can do this the standard way, as well. But the standard way requires me to manage passing the parameters and control references from the form-level code to the callback. These classes do all that for me. I just need to provide a callback with standard signature and make sure I accommodate whatever extra parameters the target event will likely pass to it. Further, the callback’s standard declaration provides me three things: a direct reference to the control, an enumeration of the control’s type, and a description of the event, simplifying the logic required to have a common callback process multiple events from multiple controls.
Global callbacks can be implemented. That is, if I want to implement a global callback that gets called anytime one form’s controls get called, or one type of control is called, or perhaps whenever *any* control is called (or any combination of these), I can do that.
Type-checking / object validation / error-trapping, etc, that I don’t have to implement in every function that takes calls from an event.

Some of the drawbacks include:

Increased complexity in the class implementation. This comes especially because you cannot define one event handler to catch events from multiple controls. For example, CommandButton, OptionGroup, and Form all have Click events, but I’d have to create a separate event handler class for each control in order to trap their separate Click events. However, the callback for their click events could all be the same function. I should point out that once the classes are implemented, using the classes isn’t particularly complicated.
Additional function calls. Some would say this would cause performance bottlenecks, which may be the case. As I see it, however, user-triggered events don’t happen very quickly, so I don’t see the additional overhead of the extra classes or using the *really* slow Application.Run method as really posing any problem. In fact, in my general experience, even the MouseMove event (which would trigger most frequently) doesn’t really appear to suffer from this overhead.
Application failures don’t recover very well. Debugging is especially problematic as, quite often, when an error is encountered, VBA may let you fix it and continue, but the event handlers will quit responding.

The demo database gives a fair idea of how it works, but you need to explore the code to really understand it. I have tried to comment it as thoroughly as possible. Note the relative simplicity of defining an event handler and callback in the Startup module. Also note the sparseness of code in the Form module. In fact, the Form module could be completely empty in many cases. However, you must have, at very least, an empty module for this to work.

The demo demonstrates changing a control’s callbacks on-the-fly, using a global callback, managing events at both the form and the control event handler class level, and managing all events through one common call-back function.

Joel Graff

Send your Power Tips to Mike and Chris at accpower@microsoft.com.

Web Database Conversion Service

Kerry Westphal — Mon, 26 Jul 2010 16:18:20 GMT

SmithBridge(the guys behind AccessHosting.com) have a new offering that will take the work out of converting your existing Access databases to Web databases that can run on SharePoint. They will check your database to see if it conforms to meet the SharePoint requirements, analyze the data and update the tables to conform to the Compatibility Checker’s rules. The result is a database that is fully compatible with Access Services and ready to be run on SharePoint. The offering really makes getting started with Web databases on SharePoint much faster and easier.

They have some great video tutorials on the conversion process. Check out the service here!

Power Tip: Exclude data from a query by using a table

cdowns — Thu, 22 Jul 2010 19:29:36 GMT

Today’s Power Tip comes from Bob Larson of BTAB Development. Bob is a former Access MVP (2008-2009, 2009-2010), and offers a host of tips and code snippets on his site.

If you have a long list of items to exclude from a query, and you don’t want to type a big IN statement in the query to exclude them, you can use a table to do the exclusions instead. I have posted a short screenshot-based “Quick Tutorial” on my site that shows you how:

http://www.btabdevelopment.com/ts/tbl2exclude

Enjoy!
Bob

Send your Power Tips to Mike and Chris at accpower@microsoft.com.

Access Junkie has 70+ tips for Access 2010

Ryan McMinn — Wed, 21 Jul 2010 16:26:25 GMT

Jeff Conrad, our resident “Access Junkie” on the test team, has updated his website to include lots of information concerning Access 2010 and Access Services. He has added new MSDN article links, Access blog links, Access online forum links, and a new Frequently Asked Questions (FAQ) page on Access 2010.

You can find Jeff’s website at the following location: http://www.AccessJunkie.com

Jeff’s new Access 2010 FAQ page has answers to over 70 common questions concerning Access 2010. Many of these FAQ pages include screenshots and helpful tips to help guide you through issues you might be encountering with Access 2010 and Access Services.

You can find Jeff’s new FAQ page on Access 2010 topics at the following location: http://www.AccessJunkie.com/faq2010.aspx

The Access Show: Developing Access 2010 Hybrid Apps with Dick Moffat

Ryan McMinn — Wed, 21 Jul 2010 00:20:00 GMT

Dick Moffat is a professional Access developer who has been using Access 2010 and SharePoint Server 2010 to build databases that run on Windows but live in SharePoint. We have a chat about how you can leverage your existing Access skills and bring your current databases into SharePoint to make sharing databases and working with users in remote locations fast & easy.

If you are building something cool with Access/Access Services we would love to feature you on The Access Show.

Get to Access Services tables with OData

Ryan McMinn — Tue, 20 Jul 2010 15:42:15 GMT

OData is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. There are already several OData producers like: IBM WebSphere, Microsoft SQL Azure, SQL Server Reporting Services; and live services like: Netflix or DBpedia among others. SharePoint 2010, is an OData provider as well and this enables Access Services as an OData provider as well. The following walkthrough shows how to extract data using OData from a published Access Northwind web template and consume it using Microsoft PowerPivot for Excel 2010.

Publishing Northwind

First, we’ll need to instantiate the Northwind web database and publish it to SharePoint.

Accessing OData

Access Services 2010 stores it’s data as SharePoint lists; therefore, in order to retrieve tables through OData we’ll need to follow the same recommendations that apply for SharePoint lists. There are a couple blog posts with more details on SharePoint and OData here and here. For our Northwind application, the main OData entry point is located on http://server/Northwind/_vti_bin/listdata.svc. This entry points describes all tables provided by the OData service, for instance, in order to retrieve the Employees table through OData, we would use http://server/Northwind/_vti_bin/listdata.svc/Employees. Additional OData functionality is described in the OData developers page.

Consuming OData

One of the applications that consumes OData, is Microsoft PowerPivot for Excel 2010. In order to import data from Northwind into PowerPivot we can follow these steps:

a) From the PowerPivot ribbon, “Get External Data” section, select “From Data Feeds”.

b) Enter the OData entry point, for this scenario: http://server/Northwind/_vti_bin/listdata.svc

c) Select the desired Northwind tables in the “Table Import Wizard”.

d) Finish the wizard to retrieve the data from Access Services.

The Northwind data should be now imported in PowerPivot and ready to be used from Excel.

Northwind 2010 Web Database is now available

Ryan McMinn — Mon, 19 Jul 2010 15:44:10 GMT

We’re excited to announce that a longstanding Access tradition continues. A new version of the Northwind sample database has just been released. The best way to get to the template is to start up Access 2010, under Office.com Templates, click on Sample Templates, and then click on Northwind Web Database.

You can also get the template here: Northwind Web Database.

As before, Northwind shows you what an Inventory/Orders system might look like for a mail order dry goods company. For this version, we brought forward all the functionality introduced in the 2007 release, while transitioning Northwind to a web database.

The result is a database that shows off a bunch of interesting web database concepts, like using data macros to maintain inventory levels and aggregates.

Enjoy, and please use the comments section to provide feedback/ask questions.

Power Tip: Navigate around a ‘Save as’ roadblock

cdowns — Fri, 16 Jul 2010 20:34:52 GMT

Today's guest blogger is Luke Chung, President and Founder of FMS, Inc. Luke has written and presented a wide range of topics related to Access over the years. In addition to their many Access related products, FMS offers a wealth of great Access papers, tips, and videos on their site.

It seems so simple and you've done it many times before. You have a Microsoft Access ACCDB database (from Access 2007 or 2010) and want to save it (convert it) to a Microsoft Access MDB format database so it can be used by Access 2003 or earlier:

Unfortunately, when you try this in Microsoft Access 2007, sometimes this error message appears:

You cannot save this database in an earlier version format, because it uses features that require the current file format.

This could occur if you added new features of MS Access 2007/2010. For instance, tables with the new multi-value or attachment field types. In those cases, you wouldn't expect to convert the database to an MDB. So you check and verify that none of your tables have these field types and you still have this problem which doesn't explain what features you're using which require the new format.

It turns out this error can still occur even if you didn't add new ACCDB features. What's going on?

The Problem: Microsoft Access 2010 Adds the MsysResources Table

This problem occurs if the database was opened with Microsoft Access 2010. MS Access 2010 stores themes in an attachment field in the MsysResources table, and MDBs don't support attachment field types. So even though you didn't add this incompatible field type to your Access database, Access sees this table and field and and concludes the database can't be converted.

The Solution: Delete the MsysResources Table

It wasn't easy figuring out the cause of the problem. Fortunately, the solution is fairly simple:

In Access 2007, right-click the top bar of the Navigation Pane, and then click Navigation Options.
In the Navigation Options dialog box, under Display Options, select the Show Hidden Objects and Show System Objects check boxes, and then click OK.
Select the MsysResources table, then press DELETE.
Save the ACCDB as an MDB.

Note that if you're in Access 2010, you can simply save the ACCDB as an MDB because Access 2010 removes the table automatically in the conversion process.

Got a great tip you’d like to share? Send it to Mike & Chris at accpower@microsoft.com.

Power Tip: Take advantage of Rich Text textboxes in Access

cdowns — Wed, 07 Jul 2010 16:26:55 GMT

Today’s Power Tip comes from Philippe Riondel of text2texte. Philippe is an English to French certified translator based in Québec.

I am currently developing a terminology managing database for my main occupation, which is translation. The interface is mainly made of 2 parts, one for the search and one for the display of the found information. That second pane can typically include one or several of entries, plus one or several topics, links to Web pages, etc., like in the image below :

The question is: how many controls do you see in the right-hand pane above? The answer is: one.

My challenge was the following: I needed to show all the information relating to a term/concept with different formatting, according to the kind of information (English entry in dark red, French entry in dark blue, domain, source, definition, comment and context incorporating some bold and in different colors and sizes, etc.), and without setting in advance the number of entries or topics that would populate each record nor their respective lengths, in order to avoid to show blank controls or areas in between. In other words, I would like a somewhat linear presentation, like a Web page, as in the picture above. By the way, I also wished to be able to highlight the terms in the result that would match the words searched in a full text search, like in the picture below, and if possible, to have working hyperlinks.

In HTML, it would be a trivial problem, but how to manage it in Access? Obviously, most of you have an idea of the answer, which resides in the rich text formatted textbox, that was introduced in Access 2003, if I am not wrong. If the feature is not that new, I do not remember to have found much literature about taking full advantage of it on the Internet or elsewhere. My intent here is not to speak of the century’s discovery or of a revolution, but I found interesting to share my experience here, to give to some of you some ideas about using Rich text textboxes and to show how powerful they are. I stayed long without using them, but, when I started, it was a revolution for me. As I wrote it above, even the Web hyperlinks work!

Typically, I feed that single control with my mass of information just as I would code an HTML page, since the control is based on like-HTML tags. After, it’s just about using some queries to extract my data and loops to process them, in order to format them in the intended way. To do that, I use a single WriteContent() function, that is hosted in a module and returns a string incorporating the formatted content aimed to become the RTF textbox’s value. That content looks like this (looks really like HTML, doesn’t it?):

That code, when setting the textbox.TextFormat property to “Rich Text”, gives the result below. As one can see, the same function can be used to populate the same formatted text box in a report only made of the same control, called from the icon at the top right of the screen:

A document containing the code for the WriteContent() function, as well as an image of the database relationships, can be found here.

Have fun!

Philippe

Power Tip: Password protect your VBA project

cdowns — Mon, 28 Jun 2010 15:58:00 GMT

A question came in recently regarding whether you can password protect just the VBA code for an Access database, but not the rest of the database. The answer is yes!

Here’s how to do it:

In Access, press ALT+F11 to open the VBA editor.
On the Tools menu, select <Database Name> Properties…
On the Protection tab, select the Lock project for viewing check box.
Enter and confirm a password.
Click OK, and then close and reopen the database.

Next time you go to view the VBA code, you’ll be prompted for the password.

Note that there are a lot of free or inexpensive tools that allow you to “recover” lost passwords, so protecting your VBA by using this procedure should not be considered a fool-proof security method. However, it’s a good way to keep people from accidentally changing the code. A more robust way to protect your VBA code is to convert the database to an .accde or .mde file, as described in this Office.com article.

Thanks to Kevin Bell on the Access team for his help with this tip, and be sure to keep sending your favorite tips to Mike and Chris at accpower@microsoft.com!

Access 2010 is now available!

Kerry Westphal — Wed, 16 Jun 2010 19:09:12 GMT

We are thrilled to announce that Access 2010 is available in retail stores and online. The team has worked very hard to make it easier to track and report on your information, build your databases faster and easier than before, and access your data in new ways. Over the last few months, we have shared our excitement about the new features of Access 2010, including Web databases, data macros, calculated columns, improved layouts, intellisense, themes, an updated navigation model, BCS(Business Connectivity Services), the macro designer, backstage and x64. You can read more about our favorite program and its new features and benefits here.

Thank you to all those who downloaded our Beta and provided feedback. Get a trial or buy Access today at office.com!

FIFA Tournament Brackets Template

Ryan McMinn — Sat, 12 Jun 2010 00:06:00 GMT

Today’s guest writer is Barath Balasubramanian —a Design Engineer in the Access test team.

As the excitement about the 2010 world cup in South Africa intensified, I set out to build an application to gather predictions on the results of the tournament from my team mates. Since all of my team mates have access to a SharePoint server, I decided to build the application using Access 2010. In less than two hours I was able to create a cool looking application that can not only track the 2010 FIFA world cup predictions, but can also be customized to fit any tournament format and runs in IE, Firefox and Safari.

The application has simple UI to perform the following tasks:

· A form to make predictions and to create brackets.

· A continuous form to list all predictions.

· A datasheet form to expose the raw data (this can be used to export data to external reporting tools)

Creating the bracket:

Using combo box controls, text box controls and UI Macro actions, I was able to quickly stitch together a simple UI to create tournament brackets. Since the data is driven via tables, it is very easy to re-use this form to track any tournament.

Once you pick the 1^st/2^nd place teams the first column in the bracket tree will automatically be filled out, then you can click the team you think will win in order to fill out the bracket tree.

Once you fill out the tree you simply click save.

Tracking the Brackets:

Once people start filling out the brackets, they can be tracked via this continuous form, which provides a ‘View’ button next to each bracket. A UI Macro action hooked to the ‘View’ button, navigates he page to show details of a specific bracket.

Exporting Data for further processing:

Depending on what tournament is being tracked, one may want to export the data into other systems like Excel charts/Power Pivots and hence a datasheet view of the Raw Data is provided via the UI.

Create your own:

To help jumpstart customization of this template, I added a form with instructions to download and to play around with the application.

Here is a link to the template.

You can either open in via Access 2010 client or directly instantiate the template via Office SharePoint Server 2010. There are multiple other ways to create this template and I would love to hear how you customized this template to suit your needs. Feel free to ping me at Barath.Balasubramanian@microsoft.com

Access 2010 New Features Demo

Ryan McMinn — Wed, 09 Jun 2010 15:23:46 GMT

Last week I had the opportunity to film a video for Channel 9 all about some of the new features of Access 2010. Hope you enjoy it.

Access 2010 and SQL Azure

Ryan McMinn — Mon, 07 Jun 2010 22:48:45 GMT

This post comes from Russell Sinclair who is in New Orleans showing off the newest Access 2010 features at TechEd.

Microsoft SQL Azure is a new cloud-based relational database built on SQL Server that provides highly available, scalable, multi-tenant database services hosted by Microsoft in the cloud. It eliminates the need to install, setup and manage SQL Server, freeing IT to handle day-to-day operations rather than having to manage and maintain an on-premise server. It also enables access to the database from any location with an internet connection, providing the ability to create connected applications that are accessible from anywhere.

Access 2010 supports connections to SQL Azure over ODBC, opening up opportunities for Access users and technology providers to create rich experiences using cloud computing. For Information Workers, this gives an easy way to connect directly from Access to cloud-based relational databases, enabling ease of use and providing flexibility to IT. Access users now have more choices to integrate rich client-server applications that can connect directly to both on-premise or cloud databases, creating unique and agile solutions.

Getting Started with SQL Azure

In order to use SQL Azure, you need to create an account and choose a plan that suits your needs. For a limited time, Microsoft is providing an introductory special that is free of charge and includes access to a single 1GB SQL Azure database, Windows Azure and AppFabric services. This is a great way to try out the features available before committing to a monthly plan.

SQL Azure requires that you specify the IP addresses that have access to the database. You can set the valid IP addresses in the Firewall Settings area of the administration tools provided on the SQL Azure site. Adding your current IP is easy as the dialog will show you this information. Check with your network administrator if you want to open a range of addresses that corresponds with the IPs on which your users might connect.

The SQL Azure administration site also allows you to create your database and users, but you will need to use SSMA or another SQL management tool in order to create your tables and other database objects.

Access supports connecting to SQL Azure over ODBC (linked tables and pass-through queries), but you need to use the “SQL Server Native Client 10.0” driver available as part of the SQL Server 2008 R2 release. You should install SQL Server 2008 R2 Management Studio (SSMS) as well since it provides updated functionality for working with SQL Server in a mode that is compatible with SQL Azure. The easiest way to install the ODBC drivers and SSMS is to download SQL Server 2008 Express. You can choose not to install the database engine but you may find it useful as you will need access to a SQL Server instance in order to migrate your database from Access (see details below). A redistributable version of the updated ODBC driver is available as part of the SQL Server 2008 R2 Feature Pack.

Connecting from Access 2010

Once you have completed the necessary installations and configuration, you can connect to your SQL Azure database using a standard ODBC linked table. ODBC links to SQL Azure have a couple of requirements of which you need to be aware. The following points detail the settings you should use when creating an ODBC DSN to a SQL Azure database.

· Use the SQL Server Native Client 10.0 driver, updated with change in SQL Server 2008 R2

· Use SQL authentication when connecting. SQL Azure does not support NT authentication.

· Login ID should be in the format “user@server”, where server is the server name specified in the SQL Azure administration site without the “.database.windows.net”. I.E. If your assigned server is “abcserver.database.windows.net”, and your Login ID is “myuser”, use “myuser@abcserver”

· Set the default database to the database you want to use for this connection. This will ensure that the connection goes to the right database instead of the master database if the login has access to multiple databases on the account

· Check the option to use strong encryption for data. SQL Azure will automatically enforce this option but you might want to set it explicitly

With the ODBC DSN successfully configured, you can now link from Access to any tables or views in the SQL Azure database as you would to any SQL Server database, or create pass-through queries to execute custom SQL on the server.

Migrating to SQL Azure

When migrating your database to SQL Server 2008 or SQL Azure, we recommend that you use the SQL Server Migration Assistant (SSMA) for Access, available from the SQL Server Migration webpage, instead of using the Upsizing Wizard included with Access. This tool is similar to the Upsizing Wizard but is tailored to the version of SQL Server you are targeting. SSMA will not use deprecated features in the migration and may choose to use some of the new features such as new data types included with SQL Server 2008.

In the summer of 2010, the SQL Server team will release an updated version of SSMA 2008 that will support migration of Access databases directly to SQL Azure. Until that time, migrating your database is a simple, three-step process

· Migrate your database to a non-Azure SQL Server (preferably 2008) database

· Use SQL Server 2008 R2 Management Studio to generate a SQL Azure compatible database creation script from your database

· Run the scripts created in SSMS on the SQL Azure database

The Upsizing Wizard included with Access is a general-purpose tool to help migrate to SQL Server and does not target a specific version of the server. Some of the features used by the Upsizing Wizard have been deprecated in SQL Server 2008 and are used in order to maintain backwards-compatibility with previous versions. Although SQL Server 2008 does not prevent you from using many deprecated features, SQL Azure directly blocks them.

Once your database is migrated to SQL Server you can generate scripts to recreate your database and the data it contains in SQL Azure. Open SSMS, connect to your local server, right-click the database in question and choose Tasks – Generate Scripts to open the Generate and Publish Scripts wizard (note, do not select the menu option “Script Database As…” as this does not provide the same functionality). Step through the wizard, selecting options appropriate to your migration, until you reach the Set Scripting Options screen. On this screen, click the Advanced button to show the Advanced Scripting Options screen shown below. There are three important settings in this dialog:

1. Script for the database engine type: SQL Azure Database. This setting is essential when scripting your database to SQL Azure. It specifies that the script should only use SQL Azure-compatible scripting, avoiding the use of unsupported deprecated SQL Server features or language.

2. Types of data to script: Schema and data. This setting specifies that the script will include the information necessary to recreate all of your data on the SQL Azure. This saves you from having to migrate the data manually. You can set this to “Schema only” if the data is not required.

3. Script Triggers: True. If you want your triggers to be migrated set this option to True as it will default to False the first time you run the tool.

Once your script is created, open Management Studio, connect to the SQL Azure database and run the script against that database to complete the job.

You’re now ready to use the full functionality of the SQL Azure database from Access.

Unsupported Features

Unfortunately, there are a few Access features we cannot support on SQL Azure due to various restrictions or issues:

· The Upsizing Wizard does not support directly upsizing to SQL Azure. As stated above, you need to migrate to an intermediary SQL Server database first and we suggest you use SSMA to do so

· ADPs are not supported as they rely on features that are not supported in SQL Azure (including ADO 2.x connections)

· ODBC connections are not supported in Access versions prior to Access 2010

· Exporting tables to SQL Azure using the export functionality built in to Access (export to ODBC or DoCmd.TransferDatabase) will fail if the tables contain any date/time fields – use SSMA

In addition to these restrictions, you should read through the SQL Azure help to determine what features of SQL Server 2008 are and are not supported. SQL Azure explicitly blocks use of features that are considered deprecated in SQL Server 2008. There are also many features that are not supported in the current release, such as extended properties and cross-database queries.

Exciting Features

We feel that SQL Azure and Access 2010 are opening some great new scenarios for Access users. We can’t wait to see what solutions you come up with.

Power Tip: Simplify development, testing, and troubleshooting multiple versions of Access with Windows 7 Virtual PC

cdowns — Fri, 04 Jun 2010 17:51:04 GMT

Today’s guest blogger is Brandon with OpenGate Software, offering Microsoft Access tools and Access templates for users of every experience level.

While we’d all like our customers stay up to date with their software releases, inevitably you may find yourself supporting users on Access 2000, 2002, 2003, 2007, and now, 2010. How best to manage the number of Access versions without breaking the bank buying PCs for each version, or dealing with the headaches of installing several versions on a single machine? The new Windows 7 Virtual PC feature is an excellent solution that allows you to develop and test in any version of Access on the same machine.

Getting Started

1. First, you’ll need a copy of Windows 7 Professional, Ultimate, or Enterprise. This will give you the ability to create one or more instances of Windows XP that will run on the Windows Virtual PC engine. Your machine should have 2GB memory.

2. Download the Windows XP Mode (500MB), Windows Virtual PC, and XP Mode updates.

http://www.microsoft.com/windows/virtual-pc/download.aspx

3. Install the programs in the order specified.

4. Select Windows XP Mode from the Start Menu after the installations are complete.

5. Give your XP instance a descriptive name, like “Access 2000” or “Access 2003 and 2010 parallel install.”

6. Install the Access version(s) on the virtual PC instance (you will have the ability to access your computer’s USB, DVD, and hard disk drives from Windows Explorer within the virtual PC. Note: If you need to create multiple XP Mode instances to support different Access versions, wait to install Access until you have created all the instances you will need. To create multiple instances, follow these instructions: http://www.sevenforums.com/tutorials/21904-virtual-xp-machine-copy.html

7. Develop, test, and troubleshoot as though you had a separate machine right in front of you! When you are done, just close the window and the virtual PC instance will go into a hibernation mode until you open it up again.

Above: Virtual Machine management window with a virtual PC instance running in the foreground

Limitations

The Virtual PC does not support 64-bit Office 2010.
The operating system versions can matter, of course. Running Access 2003 on Windows Vista with another installed version of Access can mean your graphs won’t display correctly. Using the XP Mode method above will not catch some of those nuances.
Each virtual PC can be configured to consume a specific amount of disk space and memory. Memory is only used when the virtual PC is running of course, and a minimum of 2GB on your machine is recommended (the default consumption for the virtual PC running is 512MB of memory).

The Power Tips feature relies on you! Send your tips to Mike & Chris at accpower@microsoft.com.

AccessHosting.com launches Developer Sandbox

Ryan McMinn — Tue, 01 Jun 2010 14:51:41 GMT

Larry and the folks over at AccessHosting.com have launched a new plan for developers to us Access Services and prototype solutions for customers beyond the 30 day trial.

You can learn more about it here.

Power Tip: Distinguish between an unsaved record and a saved one

cdowns — Wed, 26 May 2010 19:43:27 GMT

When you are in the process of entering a new record in an Access database, you can press the Escape key to cancel the entry of the record. However, some events, such as clicking or tabbing into a subform on the main form, can save the record on the main form. At that point, you can't press Escape to cancel the entry—you must use the Delete command to delete it. The end-users of your database might not understand the difference, and would probably want a single button or keyboard shortcut that just gets rid of the record, regardless of whether it’s been saved or not. To do this, you need to be able to which of these two states the record is in:

a) The record is being built up, has been assigned an AutoNumber ID, but hasn't been committed to the table yet
- OR -
b) The record has already been committed and the ID indicated is already persistent.

The solution is to add a module-level variable (mboolIsInsert ) to the parent form. The variable is set to True in the BeforeInsert event on the form, and reset to False on the OnCurrent event. The OnCurrent event doesn’t fire for the parent form when you click or tab into the subform, only when you directly navigate on the main form. You can then check the value of the module-level variable in your code to see if you’re in an insert event and respond as appropriate.

Option Compare Database
Option Explicit 

Private mboolIsInsert As Boolean

Private Sub Form_BeforeInsert(Cancel As Integer)
    mboolIsInsert = True
End Sub

Private Sub Form_Current()
    mboolIsInsert = False
End Sub

Public Sub CancelOrDeleteRecord()
   If mboolIsInsert Then
        'Add code to perform escape-equivalent cancel
   Else
        'Add Delete command to delete the current record
   End If
End Sub

Running the CancelOrDeleteRecord() procedure (for example, from a button on the main form) provides a more usable form by always getting rid of the record, regardless of whether it's been saved or not.

Thanks to Access PM Russell Sinclair for the tip!

The Power Tips feature relies on you! Send your tips to Mike & Chris at accpower@microsoft.com.

Free preview of FMS Inc.'s Total Access Statistics for Access 2010

cdowns — Wed, 26 May 2010 18:38:24 GMT

Luke Chung at FMS has let us know that they are now offering a free preview of their Total Access Statistics data analysis program for Access 2010. They have updated the product to support both the 32-bit and 64-bit versions of Access. The preview program is fully functional through August 1, 2010.

Thanks Luke!