Tuesday, December 21, 2010

10 Reasons for Access Developers and Users to be Thankful for SQL Server 2008 R2 Express

Brian Egler posted his Top Ten Scrooge List for SQL Server – “Polar Express” version to NetworkWorld’s Back to Microsoft Subnet on 12/20/2010:

image Last year, I listed my first annual "Top Ten Scrooge list for SQL Server". In the spirit of the season, I started thinking about those features that Microsoft charges top-dollar for with SQL Server 2008 Enterprise Edition that really should be included in the other editions.

image Well, now I know Bill Gates does not read my blog because only two of the items have been moved off the list. Let’s take a look at last year’s list and how they stack up today with SQL Server 2008 R2…

1. Data Compression - still Enterprise :(
2. Data Driven Subscriptions - still Enterprise :(
3. Database Snapshots - still Enterprise :(
4. Backup Compression - now Standard Edition! :)
5. Transparent Data Encryption - still Enterprise :(
6. Peer to Peer Replication - still Enterprise :(
7. Distributed Partitioned Views - still Enterprise :(
8. Fuzzy Lookups in Integration Services - still Enterprise :(
9. Scale-Out Reporting Services - still Enterprise :(
10. Infinite Clickthrough in Report Builder - now Standard Edition! :)

image OK, in the spirit of the season, this year, instead of listing features in the higher editions that should be in the lower editions, I’ll list the features that are in the Express edition that we are lucky to have for FREE. Scrooge would certainly approve of the price if he were paying…

Here’s my 2010 “Top Ten Scrooge List for SQL Server – Polar Express version” [with emphasis added]

1. An Express Database can be up to 10GB now (up from 4GB)
2. FILESTREAM Data is supported and does NOT count against the 10GB max
3. Database Mirroring Witness can be Express
4. SQL Server Import Export Wizard included
5. Change Tracking supported in Express
6. Column level Data Encryption included
7. Logon Triggers supported
8. Policy Based Management supported
9. Powershell support
10. Reporting Services in Express with Advanced Services edition (still FREE!)

I guess the glass is half-full after all. Let’s be thankful for that this holiday season.

Happy Holidays!

PS Here's a nice festive B-Tree with a Heap of presents! http://xkcd.com/835/
(Thanks to my students at www.rim.com)

I’m still waiting for the SQL Azure team to enable Transparent Data Encryption (TDE) and Full-Text Search (FTS) in SQL Azure.


Monday, December 20, 2010

Copies of “Microsoft Access 2010 In Depth” Are Arriving from the Printer

Loretta Yates, who’s Pearson Education’s Senior Acquisitions Editor for Microsoft Access 2010 In Depth, reported receiving a copy of the book from the printer on 12/20/2010.

This means copies should be available for shipment from Amazon.com, Barnes & Noble and other online booksellers, as well as on the shelves of brick and mortar retailers by 1/1/2011, as expected.

Keyboard Shortcut and Accelerator Key Combinations Aid Readers with Disabilities

A few days ago, I received a message from a blind reader about Microsoft Access 2010 In Depth’s Safari edition:

    • Microsoft Access 2010 In Depth
    • Keyboard Shortcuts in Access 2010         
    • Thank you for using keyboard shortcuts in your book. As a blind database developer, it is refreshing to read a book where I can go right to work instead of trying to figure out the keyboard shortcuts on my own because the book was written with only mouse users in mind. I use speech output and a Braille display to work with Access and your book will definitely make my databases easier to build and design. Thank you!

Here’s an example of shortcut key codes from the book’s Chapter 3, “Navigating the Fluent User Interface:”




















Most step-by-step examples also include shortcut and accelerator key combinations.

Download the Official “Microsoft Office 2010 Volume Licensing Guide”

image Download the “Microsoft Office 2010 Volume Licensing Guide” in .docx format here.

From the Summary:

The Microsoft Office 2010 Licensing Guide provides an overview of the core product offerings, licensing models, and Software Assurance (SA) migrations for customers with active Software Assurance.

Microsoft Office 2010 Product Snapshot

Microsoft Office 2010 provides a wide range of powerful new ways for people to do their best work from anywhere—whether they are using a PC, Smartphone, or Web browser.* To provide customers with more flexibility to best meet their unique needs, Microsoft offers a variety of Microsoft Office 2010 suites.

The Microsoft Office 2010 suites available through Volume Licensing include:

  • Microsoft® Office Standard 2010
  • Microsoft® Office Professional Plus 2010

Microsoft Office 2010 suites available through Retail and / or OEM:

  • Microsoft® Office Starter 2010 (OEM only offering)
  • Microsoft® Office Home and Student 2010
  • Microsoft® Office Home and Business 2010
  • Microsoft® Office Professional 2010
  • Microsoft® Office Professional Academic 2010

With the release of Office 2010, retail and reseller customers have two ways to purchase and activate an Office suite on new PCs pre-loaded with the Office image. In addition to the traditional disc product, consumers may choose the new Office 2010 Product Key Card. The Product Key Card and traditional disc are both available for Office Home and Student 2010, Office Home and Business 2010, Office Professional 2010 and Office Professional Academic 2010 (Academic is available through Authorized Academic Resellers only).

Office 2010 in Microsoft Volume Licensing

For Microsoft Volume Licensing customers, Office 2010 provides more business value in each product offering plus new, innovative integrated Microsoft Office Web App capabilities*. Software Assurance customers benefit from an efficient migration path to Office 2010 and can take advantage of training, deployment planning, and technical support benefits to get the most out of the software.

*An appropriate device, Internet connection, and supported Internet Explorer®, Firefox, or Safari browser are required. Some mobile functionality requires Office Mobile 2010 which is not included in Office 2010 applications, suites, or Web Apps. There are some differences between the features of the Office Web Apps, Office Mobile 2010 and the Office 2010 applications.

From the Table of Contents:

  • Summary 4
  • Microsoft Office 2010 Product Snapshot 4
  • Office 2010 in Microsoft Volume Licensing 4
  • Microsoft Office 2010 Volume Licensing Offerings 5
  • Software Assurance Migration 7
  • Office Web Apps in Office 2010 7
  • Licensing of Office Web Apps 7
  • Key Dependencies 7
  • Office 2010: Enhanced Business Productivity through Server Integration 8
  • Enterprise Search 10
  • Volume Licensing Programs and Activation 11
  • Software Assurance Benefits for Microsoft Office 12
  • Enrollment of OEM Office 2010 Licenses into Volume Licensing 12
  • Volume Activation Benefits 13
  • Office 2010: Key Takeaways 14
  • Frequently Asked Questions 15
  • Appendix 17
  • Microsoft Office Licensing Glossary 17
  • For More Information 18

Friday, December 17, 2010

Adding Silverlight Content to Access 2010’s Web Browser Control

The Microsoft Access team posted Power Tip: Integrate Silverlight into an Access database (Part II) on 12/17/2010:

As you may remember from their previous post, software developer Derrick VanArnam and DBA Russell Fox have developed a way to integrate Silverlight into an Access database by using the Web Browser Control that is included with Access 2010. Since that first post, they have been working on ways to improve the coding experience to make the Silverlight/Access integration faster and easier, and they now have a new version of their free sample database available for download. Check out their post here!

Have a Power Tip you'd like to share? Send it to us at accpower@microsoft.com.

See the Integrate Silverlight into an Access Project with a Web Browser Control post of 11/17/2010 for the first episode in this series.

Tuesday, December 14, 2010

SharePoint Developer Blogs Consolidated

Beth Massi (@bethmassi) announced on 12/14/2010 the consolidation of various Microsoft SharePoint blogs into a New SharePoint Developer Team Blog:

image[15] To make it easy for developers to make sure they’re getting all the latest technical information around developing SharePoint solutions, the SharePoint, Visual Studio and Office User Assistance teams have decided to combine their blogging efforts into the SharePoint Developer Team Blog. Think of it as your one-stop shop for developer-centric SharePoint information, straight from the product teams and user assistance folk responsible for SharePoint Foundation, Server, SharePoint Online, and the SharePoint development tools in Visual Studio.

Here’s what you can expect from this blog going forward:

  • Regular, detailed technical information on SharePoint development, such as walkthroughs and code samples, from the people who designed those product features and tools
  • Early, first-look versions of content being prepared for publication to MSDN
  • Cross-posts and pointers to SharePoint-centric developer content on other specialized blogs
  • Announcements of interest to SharePoint developers, such as content updates, service releases, or developer events.

So we’re hoping you’ll subscribe, and pass the following easy-to-remember URL on to your SharePoint developer friends and colleagues:


And if there are particular things you’d like to see covered or discussed, by all means leave them a comment.


Sunday, December 12, 2010

Simba Technologies Announces ODBC Driver for Microsoft HealthVault

Simba's ODBC Driver for Microsoft HealthVault is an easy to install ODBC driver for your PC. It allows you to access your HealthVault data using popular desktop applications like Microsoft Excel® and Access®.

Microsoft HealthVault is a privacy and security-enhanced data storage and sharing platform that helps consumers enable an easy, ongoing exchange of data among different health applications and devices in a centralized location. There is an entire ecosystem of smart health devices and software applications that interface with and enable central information storage on HealthVault.

Simba Technologies has developed a custom ODBC driver that connects a multitude of familiar applications, including Microsoft Excel and Access, to HealthVault. Using Simba’s innovative ODBC driver, users gain a convenient way to access, view and use data uploaded into a HealthVault account from various health and fitness devices.

Simba’s ODBC Driver for Microsoft HealthVault demonstrates the features and flexibility of Simba’s data connectivity solutions. Try it for free and experience how easy and seamless it is to use a wide range of standards-based reporting and analysis tools to access your data.

Do Even More with Your Data Using Familiar Applications

Simba’s ODBC Driver for Microsoft HealthVault provides users with a choice in which application they choose to review and track their health data. Users can upload data into HealthVault from a wide variety of smart health devices, including heart rate monitors, pedometers, glucose meters and asthma inhalers, and then pull the data into applications to graph and review it. For example, a user can test their blood sugar levels with a glucose meter, sync the data to HealthVault and then use an application such as Excel to graph it and look for patterns. Similarly, with patient permission, a doctor can use an application to review and chart a patient’s data.

Simba’s ODBC Driver for Microsoft HealthVault makes HealthVault data look as if it is stored locally in Microsoft Access or SQL Server. You can see the organization of your data as tables and columns, as if you were looking at a Microsoft Access database schema. With reporting and analysis tools like Microsoft Excel and Access, you can connect to HealthVault through the driver and create queries to retrieve your health information for further review.

The Simba ODBC Driver for Microsoft HealthVault is a simple driver that expands the possibilities of HealthVault. It can be enhanced and extended to add greater functionality. Similarly, Simba can help you build a custom solution specific to your needs.

An Easy Data Access Solution from the Data Connectivity Experts

Simba’s ODBC Driver for Microsoft HealthVault leverages Simba’s expertise in building connectivity solutions. The driver is based upon Simba’s proven ODBC data connectivity technology.

We have helped thousands of businesses unlock their data. If you need help building a driver for your device, or assistance with a data connectivity or software development project, we can help. Contact us to learn more.

Download the Driver for FREE

Download and install the Simba ODBC Driver for Microsoft HealthVault for free. Try for yourself the convenience of reviewing non-standard database data using standard tools like Microsoft Excel. Installation is easy, and the installer will help you register with HealthVault if you don’t already have an account.

Friday, November 26, 2010

Migrating Microsoft Access to Visual Studio LightSwitch Projects

Mauricio Rojas described Lightswitch Experiences: Migrating Access to Microsoft Lightswitch in an 11/25/2010 post:

image22242222Microsoft Access was (and is still) very used for simple data entry applications.

But Microsoft Access (despite all its good) is kind of an old technology and there was no clear alternative for a simple mortal [modern?]  development environment… until Microsoft LightSwitch.

This series of posts illustrates the experiences of migrating Microsoft Access applications to LigthSwitch.

This post shows the result of migrating all Northwind Access example tables:

The following is Microsoft LightSwitch Solution explorer showing all Tables:


And these are some of the Northwind tables in LightSwitch







The migration process is straightforward. Text and Memo fields can be migrated to string, Numbers can be migrated to Int32 or Int64, Currency is map to Money. Yes\No can be mapped to boolean with a Value List.

There is support for specifying a caption (Display ID in Lightswitch), Description, and validation rules.

I will keep posting about Query migration, form migration and also report migration. Stay tuned.

Mauricio’s posts would be more useful if he described the migration process as well as the results. I assume he migrated the Northwind Access database to an SQL Server 2008 [R2] database with the SQL Server Migration Assistant for Access, because LightSwitch Beta 1 doesn’t support an Access *.accdb (or *.mdb) file as a data source.

I’ll add Mauricio’s future articles as he posts them.

Thursday, November 18, 2010

When Your DSL Connection Acts Strangely, First Reboot Your Modem/Router

If you encounter unexpected behavior or inability to contact specific Internet hosts with a DSL connection, reboot your DSL modem or router before exploring other solutions.

Yesterday (Wednesday 11/17/2010) afternoon, my wife and I encountered a slowdown in connecting to certain Internet hosts, such as microsoft.com, msdn.com, yahoo.com, cloudapp.net (Windows Azure) and others with our ATT DSL connection. Other hosts, such as google.com and twitter.com, behaved as expected. I attributed the problem to temporary congestion or a routing mixup, but didn’t investigate further and turned the four computers in the house off.

This morning, I started my Windows Server 2003 R2 domain controller, as well as Windows Vista and Windows 7 clients and found none were able to connect to microsoft.com, msdn.com, yahoo.com, cloudapp.net and att.com. I didn’t receive immediate 404 errors; IE 8 remained in a “Connecting” state for several minutes. But google.com, twitter.com, and a few other hosts continued to behave as expected. Gmail and CompuServe (AOL) mail worked as usual, but Microsoft Online Services wouldn’t connect.

I believed that ATT’s DNS servers were the source of the problem because it affected only specific hosts. I contacted ATT Internet customer service, who claimed that “customers were experiencing problems with IE 8,” asked me to try Firefox (same problem), and then escalated the issue. When I describe the problem to the next-level agent at (877) 825-6033, she said it wasn’t an ATT problem and offered to connect me to a technician for a prepaid charge of US$139.00. I declined the offer.

I use Windows Server 2008 R2 Routing and Remote Access services to provide Network Address Translation (NAT) to my internal network, so I opened a command prompt and issued an ipconfig /flushdns command to make sure some rogue site hadn’t poisoned my DNS cache. That didn’t solve the problem.

Next, I checked the TCP/IP properties (settings) on the NIC connected to the router. The single DNS server address was (loopback) instead of the original (preferred) and (alternate) entered when I configured the server. I set those DNS addresses, rebooted the server, but still no luck with the affected hosts.

image Headslap! I hadn’t rebooted the old-timey Cayman 3220-H modem/router provided by Pacific Bell; rebooting usually was required to restore dropped DSL connections. I pulled the power cable, waited 15 seconds, plugged it back in, and received the expected “three green” (same as for aircraft retractable landing gear in the down and locked position.)

Eureka! The reboot solved the problem. All hosts behaved as expected. I have no idea why the router had problems with only certain hosts.

Note: I was one of Pacific Bell’s first DSL customers in the East Bay (CA) region and have a set of fixed IP addresses. I suffered through routing table hell during PacBell’s transition to Southwestern Bell and Yahoo! as the service provider (which is why I have a sbcglobal.net email alias), and then through the ATT name change.

Wednesday, November 17, 2010

Integrate Silverlight into an Access Project with a Web Browser Control

Chris Downs posted Power Tip: Integrate Silverlight into an Access database on 11/17/2010:

Derrick VanArnam and Russell Fox have developed a sample Access database that shows how to integrate Silverlight into an Access database. The sample demonstrates interoperability between Access and a Silverlight Treeview control, but you can also use other Silverlight controls, such as the Treeview, DataGrid, Video, and charts.

The basic method is to place an Access Web Browser Control onto a form, and then load an HTML page containing the Silverlight control. Read the full post and download the sample!

Silverlight Treeview on an Access form

Tuesday, November 16, 2010

New Microsoft Access 2010 Resource Center

Chris Downs reported a New Access 2010 resource center for IT professionals on TechNet! in a 11/15/2010 post to the Microsoft Access team blog:

image TechNet has just published a new Access 2010 Resources page full of information about migrating, deploying and securing Access 2010 databases. Learn how to plan and implement an Access Services site, and get links to developer resources, training, videos, and how-to articles. Definitely a good one to add to your Favorites!

Access 2010 Resources page on TechNet

Monday, November 15, 2010

Enable External Users to Connect to SharePoint Online 2010 Lists

Danny Burlage posted External users in SharePoint Online 2010 #office365 on 11/15/2010:

image SharePoint Online allows you to collaborate with users outside of your organization by giving them permission to access your internal organization resources. These users are called external users. If the site collection administrator or site owner wants to allow external users to access a site, the SharePoint Online Administrator must first enable the access in the SharePoint Administration Center this can be done when you set up a site collection or after it is set up. After the ability to invite external users is set up, the site collection administrator can invite authenticated external users to access the site via e-mail. The external user then accesses the site by using Windows Live ID user name and password.

Support partners are business and technical professionals, including Microsoft, who provide support services to Microsoft Online Services users. By default, the Microsoft Online Services administrator grants support partners access to the SharePoint Online Administrator Center from the Office 365 portal. However, if you want a partner to have access to a site collection or site, you must explicitly grant the partner access. You can do this at any time by changing the setting on the site collection in the SharePoint Online Administrator Center.

This new concept which will be launched in SharePoint Online 2010, part of Office 365, will enhance the capabilities for granular corporate governance to great extent. We are really happy with this feature because it will allow us to manage environments of our customers without them needing to worry about us getting access to all their data.

I expect customers to be really excited about this feature because it will allow them to give access to both partners and customers in a manner which is closely controllable.

Saturday, November 13, 2010

Should Microsoft’s Office Group Market Visual Studio LightSwitch?

Gary Short asserts Microsoft’s Office group should market LightSwitch in his TechEd Europe Day 2 – Is LightSwitch in the Wrong Product Group? post of 11/12/2010:

image22242222I spoke to a number of attendees who had been to the Lightswitch session today. They told me that the presenter walked the audience through Lightswitch and it’s place in the Visual Studio family of products. It quickly became clear to the attendees that there was a certain amount of push back from developers in the audience, the general feeling was that Lightswitch is not a developer tool. The feeling is most certainly that Lightswitch is a power user tool and does not belong in the Visual Studio stable. I mean, what does Lightswtich do? It helps you create Silverlight applications, right? Well I’m a developer, if I want to create Silverlight apps I have ways of doing that already.

image[28] So, if Lightswitch shouldn’t be in the Visual Studio stable, where should it be? Personally, I’d like to see it in the Office stable, as that’s where the power users on the Microsoft platform hang out. People who are already creating “applications” using Excel and Access should now have the ability to create Silverlight applications using Lightswitch for Office. If it were in the Office stable, Microsoft would get a double win. Firstly, devs would get off their backs about there being a “toy” in the Visual Studio line up, and secondly, power users would win because they could stop writing mission critical software in Excel and Access and get their applications on a more “professional” footing.

So why do so many power users write so many apps in Office tools? Simple. A great many enterprises have outsourced their IT capacity. So now to get even the most simple application written someone has to fill out a form in triplicate, submit it to four different people and pay through the nose for it. Not to mention that when the application finally gets delivered, it’s “a day late and a dollar short”. And why do they go through this pain? Well they have no choice normally as the terms of the outsourcing agreement means that all development and support work on applications must be done by the outsourcing company.

However, under many such agreements, Office is deemed to be “software of personal productivity” and so falls outside of the agreement, mainly because Office is on every desktop in the enterprise and if the outsourcing company had to support it, they’d never been done fielding calls of the “how to I make titles bold?” variety. However, this loophole means that if someone want a new application, this side of Armageddon, then they can write it immediately using Excel or Access.

So what do you think, am I right, should Lightswitch be in the Office stable? Leave you comments in the… well, comments.

The session discussed is Drew RobbinsDEV206 - Building Business Applications with Visual Studio LightSwitch.at TechEd Europe 2010.

image I described LightSwitch as a potential Access 2010 competitor in my forthcoming Microsoft Access 2010 In Depth book.

Must Office 365 Enterprise Subscribers also Subscribe to Office 2010 Professional Plus to Obtain Access Services?

I posted the following question to the Microsoft Online Services TechCenter > Microsoft Online Services Forums > Transition Forum on 11/12/2010:

image The preceding thread starts here. Here’s the link to the Office 365 Fact Sheet of 10/19/2010.

I’ll update this post when I receive a response to my question.

Saturday, October 30, 2010

Microsoft Announces Cloud Essentials for Partners with Free Windows Azure and Office 365 Benefits

• Updated 10/30/2010 9:30 AM PDT for the alternative Microsoft Cloud Accelerate program (see end of post).

Haris Majeed, a Microsoft Windows Azure Admin, sent the following Status Update message to contributors to the 2nd-ranked Continue Azure offering free for Developers idea on the Windows Azure Feature Voting Forum on 10/29/2010 at ~4:00 PM PDT:


Here’s the original idea:


Note that the existing MSDN Subscriber benefits were extended by eight months on 10/25/2010 (see the article in the Live Windows Azure Apps, APIs, Tools and Test Harnesses section of my Windows Azure and Cloud Computing Posts for 10/25/2010+ post.

And here are the details of the Cloud Essentials Pack from the new Microsoft Cloud Partner site:


image Note that the availability date has been updated from 2011H1 to 1/7/2011, but BPOS hasn’t been updated to Office 365. The Office 365 licenses should be of interest to Microsoft Access 2010 developers (and users) who want to host multiuser Web Databases on SharePoint Server 2010 in the cloud or upsize an Access database to SQL Azure and link it to an Access front-end.

Note: According to the Office 365 SharePoint Online Technical Overview.pptx presentation’s “Search, Insights & Composites” slide, Access Services are one of the the “What’s In” items. (So are Excel and Visio Services:)


Business Connectivity Services (BCS) are scheduled for release in Microsoft FY 2012.  PowerPivot connections to external services is targeted for release in FY12.

The sign-up process for existing Microsoft Partners (such as me) wasn’t working this morning “due to high demand.” When it resumes operation, I’ll update this post.

Alternative Microsoft Cloud Accelerate Program for “Competency” Partners “Already in the Cloud”

If you’re a current partner with “competency” qualifications and “already in the cloud,” check out the alternative Microsoft Cloud Accelerate Program:


This screen’s designers should have hired a proofreader: see “competncy” and “Compentency” [sic].

This article was originally posted to the OakLeaf Systems blog on 10/30/2010.

SharePoint 2010 Easy Setup Script for 64-bit Windows 7

Mithun Dhar’s SharePoint 2010 Easy Setup Script… post of 10/28/2010 takes the drudgery out of installing SharePoint Server 2010 under 64-bit Windows Server:


Problem: Installing SharePoint Or Setting up a Dev Environment for writing SharePoint applications. (Install SharePoint directly onto your machine running Windows 7 64bit). The downside to installing it on your Windows 7 box is that it requires a steep learning curve to understand how to setup and install all of the bits and pieces to have a full SharePoint developer environment. This is where the Easy Setup Script comes in. The goal of the Easy Setup Script is to reduce the learning curve and time for a casual developer to get started.

Solution: We are addressing this problem by providing you a SharePoint 2010 Easy Setup Scrip that automates this process. *ta da* :)

How: In order to make this easy Our Resident SharePoint experts have created a series of PowerShell Scripts that automate the entire process. Of course, these scripts are highly configurable and can be tweaked to make it more customized. But right out of the bat,

    • They will download and install all of the Required Software (Including Pre-Req’s)
    • Install and download all of the SharePoint Pre-Req’s
    • Install other software such as Visual Studio and Office
    • Of course, this script will also install and configure SharePoint 2010
    • And finally, it will provision a site for you to work with.

Please note that this script will download evaluation copies of the products it installs (or use fully licensed product bits you supply), install them either locally or in a user supplied Windows 7 VHD & set that VHD up for duel boot using the Windows 7 VHD native boot feature.

In case you are wondering about what specific software will be installed, here’s a list:

  • SharePoint Server 2010 + pre-requisites (Standalone)
  • Visual Studio 2010 Ultimate Edition
  • Silverlight 4 Tools for Visual Studio
  • Expression Studio 4 Ultimate
  • Open XML SDK
  • Visual Studio SDK
  • Visual Studio SharePoint Power Tools
  • Office 2010 Professional Plus
  • SharePoint Designer 2010
  • Visio 2010

Also to help you visualize what’s happening, here’s the Easy Setup Script Process Flow…


Click to view full size

To begin Download the Script

As soon as you download and install you will be presented with this screen…


If this is not an option, do try out the Information Worker VHD – This has everything Setup and Configured but of course to use this VHD you will need a machine capable of running Hyper-V and it should have at least 8 GB RAM.

Alternatively, you can remote desktop into a hosted instance of SharePoint. But this is not the most desired solution for developers…

Here’s a really useful presentation to understand what’s going on…you get this from the script.

SharePoint 2010 Easy Setup

View more presentations from Mithun Dhar.

Fore more updates, follow Chris Johnson’s and Paul Stubb’s blogs on MSDN. They are our resident experts and SharePoint evangelists.

And as always, if you have specific questions – feel free to email me. I modified the Config file and installed SharePoint 2010 using the Easy Setup Script.

-Mithun Dhar

Without this script, installing SharePoint Server 2010 under 64-bit Window 7 is a nightmare.

Wednesday, October 27, 2010

Patrick Wood Requests Votes for Access 2010 ADO Connections to SQL Azure

In his You Can Vote to Get ADO for Microsoft Access® to Connect to SQL Azure™ post of 10/9/2010, Patrick Wood wrote:

image Microsoft has put much more emphasis lately on listening to developers and users when they ask for Features to be added to Microsoft Software. This has led to many improvements in new software releases and updates. Microsoft is really listening to us but you have to know where to give them your requests.

A few days ago on the SQL Azure Feature Voting Forum I put in a request for “Microsoft Access ADO–Enable Microsoft Access ADO 2.x Connections to SQL Azure.” My comment was “This will enable more secure connections to SQL Azure and provide additional functionality. ODBC linked tables and queries expose your entire connection string including your server, username, and password.”

On Friday Roger Jennings posted about this on his Roger Jennings’ Access Blog, “I agreed and added three votes.” He also wrote about it on his software consulting organization blog, OakLeaf Systems and added a link to the The #sqlazure Daily which I have found to be a great place to keep up with the very latest tweets, articles, and news about SQL Azure. Roger Jennings has written numerous books about programming with Microsoft Software, a number of which have been about Microsoft Access. His books are among the very best about Access and contain a wealth of helpful detailed information. I have never had a conversation or any correspondence with before now but I greatly appreciate his support in this matter.

Why do we need ADO (this is not ADO.Net) when we have ODBC to link tables and queries to SQL Azure? If you are just planning to use SQL Azure for your own use you can get by fine without it. But if you distribute Access Databases that use SQL Azure as a back end then you have to be very careful because an ODBC DSN is a plain text file that contains your server web address, your user name, and your password. If you use DSN-less linked tables and queries another programmer can easily read all your connection information through the TableDef.Connect or QueryDef.Connect properties even if you save your database as an accde or mde file.

ADO allows you to use code to connect to SQL Server and if we had the same capability with SQL Azure this would allow us to keep all of our connection information in code. Then using accde or mde files will provide us with better security. ADO also provides additional functionality which can make developing with SQL Azure easier.

We have already picked up a few votes for ADO but we need a lot more to let Microsoft know there are enough of us developers who want ADO to make it worthy of their attention.

You can vote for ADO here. You can use 3 votes at a time and your support is greatly appreciated.

You can read more about SQL Azure and Microsoft Access at my Gaining Access website.

Happy computing,
Patrick (Pat) Wood
Gaining Access

AccessHosting’s Larry Strange to Present at SharePoint Summit 2011

Larry published SharePoint Summit 2011 on 10/21/2010 (missed when posted):

I am pleased to announce that I will be presenting at the SharePoint Summit on January 31st, 2011 in Toronto. If you are looking for information on Access Services from the SharePoint administrator point of view (vs. the Access developer) then this session is for you.

You may also like:

I featured AccessHosting’s SharePoint Server 2010 hosted service in Chapter 23, “Sharing Web Databases with SharePoint Server 2010” of my forthcoming Microsoft Access 2010 In Depth book:


Service Pack 1 coming for Office 2010 and SharePoint 2010

The Microsoft Office Customer Programs Team sent messages to Office and SharePoint 2010 technical beta testers  on 10/26 requesting applications to participate in a forthcoming (calendar year 2010) Beta program for Service Pack 1:

Hello Valued Microsoft Customer,

We are contacting you about an opportunity to participate in an upcoming Beta testing program. You may be a previous tester of Microsoft products, or someone who has been nominated. Beta programs are unique ways to experience product updates and provide feedback to the development teams. Later this calendar year we will begin an invitation-only beta testing of the Service Pack 1 for Office 2010 and SharePoint 2010. Service Packs contain product updates since the product released. We would like to invite you to participate in the private testing of that service pack.

Call to action

If you are interested in participating in this Beta, and if you are already using Office 2010 or SharePoint 2010, please complete the application survey by following the link below. Upon completing the survey, your participation status will be set to ‘pending’, while we review your submission. If you are selected to participate, you will receive a formal invitation to join the program. Due to the limited number of seats in this private Beta, we may not be able to accommodate all interested testers.

If you are already registered on Microsoft Connect, make sure to sign in with your existing registered Windows Live ID account to submit this survey. If you are not registered yet, you will be prompted to do so.

Hopefully, Service Pack 1 will enable Access 2010’s Macro-to-VBA Converter to work with embedded macros. This feature suffered from a regression bug that wasn’t fixed in the RTM version.

Saturday, October 23, 2010

Download the New Free Version of the Access and Outlook Appointment Manager

Patrick Wood offers a New Free Version of the Access and Outlook Appointment Manager in this 10/21/2010 post:

You can now download the new free Access and Outlook Appointment Manager for either Access 2007 or 2003.

Easily export your Access Appointments or dated events to the Outlook Calendar. You can create and manage Appointments in Access and export any Access Appointment to Outlook, creating a new Outlook Appointment. Select any Outlook Calendar in the Outlook folders on your computer.

This is not a demo. You can use the free Access Appointment Manager as often and as long as you like.

You can download both the Production version or try out the Appointment Manager with sample data to see how it works.

The code is made available for you to see how to create an Outlook Appointment and make sure the dates and times are correct.

Also available on our website is our free internet based SQL Azure and Microsoft Access cloud computing demonstration application. You can download it and learn more about it from our SQL Azure and Microsoft Access page.

Other free downloads are available.

Happy computing,

Patrick (Pat) Wood

Tuesday, October 19, 2010

Marshall Wright Commends Office 2010

Marshall Wright posted Office 2010 – The First new version worth getting in a decade! on 10/19/2010:

Microsoft just released Microsoft Office 2010.  Unlike the last release of Microsoft Office, Office 2007 whose claim to fame was changing the user interface and confusing people, Office 2010 builds on the previous version and adds many features.  Outlook includes new organizational tools and direct links to social media web sites like LinkedIn.  Word and Excel now include links to FREE document storage at Microsoft.  This free service may be used for document collaboration that may have profound effects on how business is conducted.  PowerPoint improves through the addition of a graphics editor and the ability to publish presentations for sharing directly to the Internet.  Click here for more details on these and other new features.

I agree with Marshall, but am surprised he didn’t mention improvements to Microsoft Access 2010, such as Web Databases and connectivity to SQL Azure, as well as other Professional Edition programs and features. Maybe he doesn’t have Office 2010 Pro.

Friday, October 8, 2010

Vote for More Secure ADO 2.x Connections to SQL Azure in Access 2010

Patrick Wood wrote: 

imageLet's Vote to get Microsoft Access ADO 2.x Connections to SQL Azure on the SQL Azure Feature Voting Forum http://tinyurl.com/2e3m978 ! ODBC linked tables are a security risk and we need ADO features!

image This will enable more secure connections to SQL Azure and provide additional functionality. ODBC linked tables and queries expose your entire connection string including your server, username, and password.

I agreed and added three votes.

You can add your vote(s) for this feature by clicking here.

Note: Microsoft Access 2010 In Depth’s Chapter 28, “Upsizing Access Applications to Access Data Projects and SQL Azure,” includes step-by-step instructions for linking to SQL Azure databases with ODBC.

Saturday, October 2, 2010

Finished Updating All 33 Chapters of “Microsoft Access 2010 In Depth”

Hooray! Yesterday, 10/1/2010, I sent the updated 53-page manuscript of Microsoft Access 2010 In Depth’s Chapter 3, “Navigating the Fluent User Interface,” and its 53 illustrations to Pearson Education’s Senior Acquisition Editor Loretta Yates (@loyates). (Chapters 1 through 3 were dependent on information from many of the other 32 chapters, so they were last in line for updating.)

Here’s the final Table of Contents from the Excel spreadsheet I use to track progress:

Microsoft Access 2010 In Depth


Front Matter




Getting Acquainted with Access 2010


Access 2010 for Access 2007 Users: What's New


Building Simple Tracking Applications


Navigating the Fluent User Interface


Learning the Fundamentals of Access Databases


Exploring Relational Database Theory and Practice


Working with Access Databases and Tables


Entering, Editing, and Validating Access Table Data


Sorting, Finding, and Filtering Data


Linking, Importing and Exporting Data


Transforming Data with Queries and PivotTables


Designing Queries for Access Databases


Understanding Access Query Operators and Expressions



Creating Multitable and Crosstab Queries


Working with PivotTable and PivotChart Views


Creating and Updating Access Tables with Action Queries


Designing Forms and Reports


Creating and Using Basic Access Forms


Designing Custom Multitable Forms


Working with Simple Reports and Mailing Labels


Preparing Advanced Reports


Adding Graphs, PivotCharts, and PivotTables


Programming Databases with Macros


Automating Access Applications with Macros


Emulating Table Triggers with Access Data Macros


Collaborating with Access Data


Linking Access Front Ends to Access and Client/Server Tables


Collaborating with SharePoint Foundation 2010


Sharing Web Databases with SharePoint Server 2010


Working with HTML and XML Documents


Importing and Exporting Web Pages



Integrating with XML and InfoPath 2010



Creating Access Front Ends to SQL Server Databases


Exploring Access Data Projects and SQL Server 2008


Moving from Access Queries to Transact-SQL


Upsizing Access Applications to Access Data Projects and SQL Azure


Programming and Converting Access Applications



Learning Visual Basic for Applications


Handling Events with Macros and Procedures


Programming Combo and List Boxes



Understanding Data Access Objects, OLE DB, and ADO


Upgrading Access 2003 and Earlier Applications to Access 2010


Back Matter

App A

What Was New in Access 2007 to Users of Access 2003 and Earlier

App B



In the spirit of conservation, QUE minimized the number of trees cut down to publish this detailed book. Parts VII through X/chapters 24 through 33 and the appendices (in green-colored type) aren’t printed. Instead, they’re included in the PDF file that you can download (along with the sample code) from QUE Publishing’s Web site when you register your book. Electronic-only chapters cover less-widely-used technologies and those deprecated by Microsoft, such as VBA in favor of Access macros. The PDF includes all the book’s text and illustrations.

QUE also helps conserve your cash: The US list and Amazon prices for this edition are US$39.99 and US$26.99; previous editions were US$49.99 and US$31.99.

Alternatively, you can purchase the ePub version of the book, which works with all popular eBook readers. Details will follow.

Thursday, September 23, 2010

Laura Rogers Describes Access Services in SharePoint 2010 Server for SD Times on the Web

Software Development Times (@SD Times) on the Web published a brief Access Services in SharePoint 2010 article by Laura Rogers on 9/23/2010:

image There is a new feature in SharePoint 2010 called Access Services, but the integration story between SharePoint and Access goes way back to SharePoint and Office 2003. Since SharePoint 2003, there has been the ability to link SharePoint lists and libraries to Access so that they are represented as tables in a database. Queries and relationships can be created, as well as nice-looking reports. This method of reporting has been used by small companies that do not have more high-end services.

imageBy using SharePoint as a Web front end to an Access database, users who are inputting data do not need to have Access on their computers, and they do not need to take turns opening a database file. But the problem with this method is that the database still needs to be stored in a file share for the people who run the reports.

In SharePoint and Access 2007, some new integration capabilities were added. One of these features is a new type of view, called an Access view, that can be created for any list or library. People who have Access installed on their computers when creating a new view will see this view type option.

In Access views, create any type of object, such as Tables, Queries, Forms or Reports. Then, when this view is published to SharePoint as a view, there will be a new item with the name of that Access item in the Views drop-down box. Any other people who have Access installed on their computers can view those Access objects.

With SharePoint and Access 2010, you get all of the great capabilities that existed in 2007, and more. SharePoint lists and libraries can still be connected and used inside of databases, but with the Enterprise version of the product, Access Services can be used. With Access Services, the entire Access database can be published to SharePoint as its own entire sub-site. The tables in the database become SharePoint lists, the forms become Web-based, and even the business logic in the database is translated to become workflows in SharePoint.

Once the database is published to Access Services, the original database file can be thrown away because it is not referenced anymore. Note that there is a sometimes painstaking process of going through the Web compatibility checker before a DB can be published, but this process completely depends on the complexity of the database.

All in all, if your company’s departments have various databases floating around, they will all have the ability to put them in SharePoint 2010 and take advantage of Access Services. Also, smaller companies can still use Access as the reporting tool for data that is in SharePoint, even if they do not own the Enterprise version of the product.

Laura Rogers is a consultant with SharePoint911.

image Chapter 23, “Sharing Web Databases with SharePoint Server 2010,” of my forthcoming Microsoft Access In Depth book covers Web Databases, the official name of Access 2010 projects published to SharePoint’s Access Services. Web Databases are a totally revamped version of the Data Access Pages (DAP) feature offered by Access 2003 and earlier. (Access 2007 and 2010 don’t support creating DAP.)

DAP were intended for intranet (private) access only and were very difficult to adapt to secure Internet deployment. If you you have a Web-facing SharePoint Server (SPS) 2010 Enterprise edition, you can take advantage of the SPS security infrastructure for Web Database user authentication and authorization.

If you don’t have an on-premises  SPS 2010 Enterprise farm, you can publish Web Databases to hosted SPS instances. Chapter 23 includes a section with the details for publishing an Access *.accdb database to AccessHosting.com’s multi-tenanted SPS service “in the cloud.” AccessHosting (@accesshosting) offers a free 30-day trial of their service.

Saturday, August 21, 2010

Migrating a Moderate-Size Access 2010 Database to SQL Azure with the SQL Server Migration Assistant

The SQL Server Migration Assistant for Access (SSMA) v4.2 is a recent update that supports migrating Access 97 to 2010 databases to SQL Azure cloud databases in addition to on-premises SQL Server 2005 through 2008 R2 instances.

Luke Chung’s Microsoft Azure and Cloud Computing...What it Means to Me and Information Workers white paper explains the many benefits and few drawbacks from using SQL Azure cloud databases with Access front ends. Luke is president of FMS, Inc., a leading developer of software for Microsoft Access developers, and publishes products for the SQL Server, Visual Studio .NET, and Visual Basic communities.

Note: You cannot migrate SQL Server databases for Access Data Projects (ADPs) to operable SQL Azure tables because SQL Azure doesn’t support OLE DB connections currently.

image It’s common to use the Northwind or smaller sample databases to demonstrate SSMA v4.2’s capabilities. For example, Chapter 28, “Upsizing Access Applications to Access Data Projects and SQL Azure,” of my Microsoft Access 2010 In Depth book (QUE Publishing) describes how to upsize Northwind.accdb to an SQL Azure database. Most departmental and line of business (LOB) databases have substantially more records than Northwind, so it might not demonstrate performance issues that could arise from increased data size. This article uses the Oakmont database for a fictitious Oakmont University Department-Employees-Courses-Enrollments database, which has tables illustrated by this Access 2010 Relationships diagram (click image for full size, 1024px, capture):

imageThe following table lists the database tables and their size:

Table Name Rows
Courses 590
Departments 14
Employees 2,320
Sections 1,770
Enrollments 59,996
Students 29,998
Grades 59,996
Student Transactions 45,711

image The size of the Oakmont.accdb Access database is 16,776 KB with one query and form. It has been included as Oakmont,mdb or Oakmont.accdb in the downloadable code of several of my books, including Special Edition Using Micorosoft [Office] Access 97 through 2007 (QUE Publishing), the forthcoming Microsoft Access 2010 In Depth (QUE Publishing), and Admin 911: Windows 2000 Group Policy (Osborne/McGraw-Hill). Steven D. Gray and Rick A. Llevano created the initial version of the database for Roger Jennings' Database Workshop: Microsoft Transaction Server 2.0 (SAMS Publishing).

Update 8/21/2010: Alternatively, you can link your Access front-end to tables in a local (on-premises) SQL Server 2008 [R2] database and then move the database to SQL Azure in a Microsoft data center by using the technique described in my Linking Microsoft Access 2010 Tables to a SQL Azure Database post of 7/28/2010. Luke Chung describes a similar process in his August 2010 Microsoft Access and Cloud Computing with SQL Azure Databases (Linking to SQL Server Tables in the Cloud) white paper.

Upsizing the Oakmont Database to SQL Azure with SSMA for Access v4.2

To upsize the Oakmont database to an SQLAzure database named OakmontSQLAzure, do the following:

1. Download SSMA for Access v4.2 as described in my Installing the SQL Server Migration Assistant 2008 for Access v4.2: FAIL and Workaround post of 8/15/2010.

2. If you don’t have a Windows Azure Platform account, create a SQL Azure account and database with a One-Month SQL Azure Pass as described in my Opening a SQL Azure and Windows Azure One Month Pass or Introductory Special Account post of 8/16/2010. Name the database OakmontSQLAzure instead of NwindSQLAzure.

3. Download and decompress OakmontMdb.zip from a SkyDrive public Oakmont SSMA Upsize folder.

image 4. Launch SSMA for Access v4.2 from the Start\Microsoft Access SQL Server Migration Assistant for Access\Microsoft Access SQL Server Migration Assistant for Access menu to display the Migration Wizard’s first dialog (click image for full size, 1024px, capture):


5. Click Next to open the Create New Project dialog, replace the project Name with OakmontSQLAzure, and select SQL Azure in the Migrate To list:


6. Click Next to open the Add Access Databases dialog. Click Add Databases to open the Open window, navigate to the folder to which you extracted Oakmont.accdb, and double click the icon to close the window and return to the dialog:


7. Click Next to open the Select Objects to Migrate dialog, expand the node hierarchy, and select Tables to migrate all tables. Migrating the single query is optional:


8. Click Next to open the Connect to SQL Azure dialog, open the SQL Azure tab of the Windows Azure Developer portal where you created the database, and copy the auto-generated server name to the clipboard:


9. Return to the Migration Wizard, paste the Server Name to the Server Name text box, type your Administrative User Name and Password, click Browse, and click OK to dismiss a warning to open a two-item list:


10. Click OakmontSQLAzure to select it and click Next to open the Link Tables dialog:

image 11. Mark the Link Tables dialog and click Next to open the Migration Status dialog:


and, after a brief delay, the Synchronize with the Database dialog, which shows that the SQL Azure database has no existing objects. Optionally expand a few of the Local Metadata items.


12. Click OK to close the Synchronize with the Database dialog and return to the Migration Status dialog, which continues adding data to the SQL Azure tables and takes several minutes to complete:


13. When data migration completes, the Connect to SQL Azure dialog opens:


14. ODBC connection strings for SQL Azure require a fully qualified login ID. Add @ and the Server Name to your Administrative User name to create a fully qualified SQL Azure login ID, SQLAzureAdmin@nd02dcs58a for this example, and type your Administrative User password:


15. Click Connect to connect to your new SQL Azure database. Click Yes to close the Security Warning message and dismiss the Wizard:

image If you click No, you won’t create Access links to the SQL Azure tables. 

16.  Expand and compare the Access Tables nodes and Tables of SQL Azures dbo schema (click for full-size, 1024px, capture):


17. Return focus to the Migration Wizard and review the Migration Status dialog:

image The warnings result from nullable primary key columns and other non-fatal issues. Informational messages result primarily from naming issues which don’t affect table use.

18. Click the Convert Selected Objects item link to review the warnings and informational messages in the Error Report (click image for full-size, 800px, capture):


According to SQL Server Management Studio, SSMA changes nullable primary keys to NOT NULL (see step 22 below).

19. Click Close and click Report to review a summary report of the upsizing process (click image for full-size, 950px, capture):image

20. Click Close and close SSMA’s window, saving metadata if it’s reported missing. Open Oakmont.accdb in Access 2007 or 2010 and view the Tables group in the Navigation pane (click image for full-size, 800px, capture):

imageGlobe icons denote linked tables and the Wizard adds a SSMA$ prefix and $local suffix to new names of the original ACE tables.

21. Open the SQL Azure database in SQL Server Management Studio 2008 R2 [Express] by typing the login information shown here:


22. Expand the Tables, dbo.Courses, Columns nodes and note that the CourseID primary key column is now flagged NOT NULL click image for full-size, 1024px, capture):


23. Close SSMS 2008 R2 and open the linked Course table in Access 2007 or 2010 Design View:

image The ODBC connection string (Description property value is: ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=nd02dcs58a.database.windows.net; UID=SQLAzureAdmin@nd02dcs58a;APP=SSMA;DATABASE=OakmontSQLAzure;;TABLE=dbo.Courses and it does not expose the password.

24. Return focus to the Windows Azure Developer Portal’s SQL Azure Database page and check the size of the upsized database: 43.8 MB, which is almost triple that of the Access database:

imageThe increase in size might be the result of the three replicas that SQL Azure creates for data reliability. A Why are SQL Azure Databases Migrated from Access Database with SSMA v4.2 Three Times Larger? thread is pending a response in the SQL Azure – Getting Started forum.

Update 8/21/2010: Brent Stineman suggested I compare the result of executing SELECT SUM(reserved_page_count) * 8192 FROM sys.dm_db_partition_stats against the database, which returns 40,034,304, less than 10% less than the size from the portal.

Note: The Migrating a Moderate-Size Access 2010 Database to SQL Azure with the SQL Server Migration Assistant post to the OakLeaf Systems blog updated 8/21/2010 is a copy of this article.