The PowerPoint Team Blog
From the PowerPoint Team at Microsoft.

  • Microsoft Access

    Using OMPM to scan Access databases


    The Office IT Pro Blog has posted a good workaround for a couple of problems that sometimes occur when using the Office Migration Planning Manager (OMPM) to scan Access databases for migration to Office 2010. The trick is to make a couple of changes to the OMPM configuration file (offscan.ini).

    Read the full post here.

  • Microsoft Access

    Power Tip: Integrate Silverlight into an Access database (Part II)


    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
  • Microsoft Access

    New Year, new look for the Access Blog


    Happy New Year, Access aficionados! We are pleased to announce that this month, the Access Blog is officially joining the family of blogs. You may notice a few changes in the appearance of the blog, but your old links and bookmarks should continue to work, and you shouldn't notice any interruptions in RSS feeds or other service. Redirects are still being put in place, but you can check out the new blog at the link below. We'll have more information after the migration is completed, and we'll do our best to answer any questions that arise.

    Of course, the Access Blog will continue to be your source for the latest information directly from the Access product team, and we'll work to keep bringing you pointers to new Help articles, videos and training, plus Power Tips and other guest posts to help you get the most out of Access. As part of, blog posts will become searchable in the Office Help system, enabling us to spread the word about Access farther than before.

    Click here for more information about the Microsoft Office Blog. Thanks for reading, and we look forward to seeing you on the Access Blog on!

  • Microsoft Access

    UtterAccess extreme makeover with Access Wiki


    The extreme makeover of UtterAccess is underway! Access MVP and owner of UtterAccess, Gord Hubbell, has released a significant upgrade that improves the social tools available to the Access community.

    UtterAccess is known for its friendly community that strives to give people great answers to questions. The site offers a forum for many Access experts that openly help people do amazing things. The community of quality people have even better help tools. I’m personally excited about the long-term impact the site will continue to have in helping people create great databases, excel in their career, and build lasting friendships.

    There are two parts to this upgrade—a new wiki and upgraded discussion forums.

    Access Wiki

    For years, Access experts have created their own sites full of useful knowledge about how to maximize the benefit of Access. These sites are a significant piece of the overall value proposition of the Access product. If you build your app on Access, there are online resources and a community of experts to help get you realize your ideas. A few favorite sites include Allen Browne’s Tips,, and Now there is a new site for free how-to content about Access.


    I expect the Access Wiki to grow as one of the best free source of Access learning materials.

    What's a wiki? A good read can be had here: UtterAccess uses MediaWiki, the same framework as Wikipedia.

    For me, a wiki is a living topic that (unlike static personal web sites) is always undergoing continuous improvement. Mistakes are quickly corrected and unclear sentences or topics reworked. If you have a more clear way of making a point—by all means change it. If a useful link provides better resources—add it. Making changes to the topics is incredibly easy. You just need to sign-in start editing. Here is a get started overview and a syntax quick reference once you have moved beyond basics.

    Updates to the Wiki have already started to flow in. Here are a few topics that have already been created: Error handling, Relationships, Login securityRecord key, and Display related data. Take a few minutes and give it a read and update topics that leaves out an important point or correct grammar mistakes. As one of the Five pillars from Wikipedia says:

    “Be bold in updating articles and do not worry about making mistakes. Your efforts do not need to be perfect; because prior versions are saved, no damage you do is irreparable.”

    Contribute your insights to the Access Wiki today!

    Discussion Forum Upgrade

    For years, Gord has tuned and customized the forum software the drives UtterAccess. It has served the community well. When we talked about integrating MediaWiki, it was clear the biggest challenge was to integrate the two frameworks authentication systems and user experience. To bolt together the forum and MediaWiki was a significant rewrite. Gord discovered IP.Board and IPBWiki (by Peter De Decker in Belgium) provides a seamless user experience and shared authentication system between the forum and wiki. He describes the forum upgrade as going from Cessna to a Boeing 747.

    It was an uneventful take off last night but expect a few turbulences as crew and users get use to the new cabin. I expect members will quickly get use to the new user experience and enjoy discovering all the new features. Here are some of the personal profile improvements users will find:

    • Friends list - manage your friends for easier to send private messages, to see if your friends are currently online and display your friends on your profile
    • Profile comments - allow visitors to leave comments on your profile
    • Recent visitors - recent visitors are shown on your profile
    • Your content - show your recent posts / blog entries / gallery uploads on your profile
    • Personal statement - add a bio for others to read
    • Incredibly easy access to edit your location, age and IM details


    Thanks Gord and Community Contributors

    I can’t say enough how much we appreciate the work Gord and the UtterAccess forum administrators contribute to the community. The new wiki truly is (and will be) a collaborative contribution from many people.

    It is amazes me how many people give so freely of their time and resources. UtterAccess isn’t the only place people turn for answers. There are other communities such as Access World Forums, Newsgroups, LinkedIn, Tek-Tips, stackoverflow, and Experts Exchange. The service provided by these communities is also appreciated by the millions. Thanks for all you do!

  • Microsoft Access

    Access blog goes to China


    Lois Wang, a program manager on the team responsible for reports, has started the Access China blog. Lois will blog about Access and open a broader dialog with Access developers in China. If you read Chinese—check it out.


    Friends in China—enjoy!

  • Microsoft Access

    Power Tip: Create an AutoNumber field that starts with a number greater than 1


    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 video on their site.

    In Microsoft Access tables, the AutoNumber field type allows you to assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent (i.e. the value cannot be changed once it is assigned).

    For new tables, AutoNumbering begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1.

    To do this, create an Append query to insert the value of the ID one less than your desired starting number (or Seed value). For instance, assume that you want to start with the Seed value 1000.

    First, run this query:

    INSERT INTO tblName ( ID )
    SELECT 999 AS Expr1

    Where tblName is the table name, and ID is the name of the AutoNumber field.

    Then, run a second query to delete the row that you inserted:

    FROM tblName
    WHERE ID=999;

    (Alternatively, you can simply open the table and manually delete the row that you inserted.)

    Now, the next record added to the table is assigned the value 1000:


    This method works with a new table, or with a table whose AutoNumber has not yet reached the Seed value. (Of course, the ID field for existing rows will not be changed, but subsequent rows will start with 1000.)

    Send your Power Tips to Mike & Chris at
    Learn more about Access at and
  • Microsoft Access

    FMS Inc. announces updated products for Access 2010


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

    Total Access Admin 2010 Preview Version

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

    The new version supports Access 2010’s 32 and 64 bit versions.  FMS welcomes feedback from the community:

    Zip Code Database with Latitude and Longitude

    FMS has offered the Total Zip Code Database for many years. It is a subscription license from the US Postal Service that lists every zip code in the US and their corresponding city and state. It’s great for use as a lookup to validate zip codes and automatically fill in the city and state fields. Some zip codes have multiple acceptable city names, and it’s easy in Access to create a drop down list to offer the user all the valid city names.  FMS offers the database on an annual basis with monthly updates, which also includes lists of countries, states, and most recently, latitude and longitude coordinates for each zip code. A sample database shows how it works.  More info here:

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

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

  • Microsoft Access

    Switch the Property Sheet on and off from VBA


    Today's guest blogger is Pavlo Pedan of ARGO Business Corp. He has 15 years of experience with Access and has a great site of tips  at

    When you open a form or report in Design View, the Property Sheet is either displayed or hidden based on its state the last time you left Design view. However, you might want to ensure that the Property Sheet is always displayed (or hidden) when you switch to Design view. You might think to use the following command:

    DoCmd.RunCommand acCmdProperties

    Unfortunately, this command only works after the form or report is already open in Design View. To ensure that the Property Sheet is always displayed or hidden when you switch to Design view, use the function below. The function can be executed while starting an application (for example in the AutoExec macro), attached to the Close event of the form or report, or whenever necessary.

    For example, you might use the function to suppress the blinking of the Property Sheet when creating a form at a run-time using the Application.CreateForm method and opening it in a Form view.

    Here's the code, which has been tested in Access 2007.

    ' Module    : modSwitchPropertySheet
    ' Author    : Pavlo Pedan
    ' Date      : 31/07/2009
    ' Purpose   : DoCmd.RunCommand acCmdProperties only toggles Property Sheet in
    '           : Form/Report Design View on/off. Use function below to turn it
    '           : on or off
    Option Compare Database
    Private Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" _
        (ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
         ByVal lpszClass As String, ByVal lpszWindow As String) As Long
    Private Declare Function GetWindowRect Lib "User32" (ByVal hWnd As Long, _
        lpRect As RECT) As Long
    Private Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
    End Type
    Function fSwitchPropertySheet(blnON As Boolean) As Boolean
    ' Procedure : fSwitchPropertySheet
    ' Author    : Pavlo Pedan
    ' Date      : 31/07/2009
    ' Purpose   : switch Property Sheet in Design View (blnON = False to turn off)
        Dim frm As Form
        Dim hWnd As Long
        Dim rectControl As RECT
        ' create temp form
        Set frm = CreateForm
        ' find handle for Property Sheet window
        hWnd = FindWindowEx(Application.hWndAccessApp, 0, "MsoCommandBarDock", _
        If hWnd > 0 Then
            ' get coordinates of the window
            GetWindowRect hWnd, rectControl
            ' determine action
            If blnON Then
                ' if window width is 0, switch it ON
                If rectControl.Left = rectControl.Right Then
                    DoCmd.RunCommand acCmdProperties
                    'returns True if action made
                    fSwitchPropertySheet = True
                End If
                ' if window width is not 0, switch it OFF
                If rectControl.Left <> rectControl.Right Then
                    DoCmd.RunCommand acCmdProperties
                    'returns True if action made
                    fSwitchPropertySheet = True
                End If
            End If
        End If
        ' close temp form without saving
        DoCmd.Close , , acSaveNo
    End Function
    Send your Power Tips to Mike and Chris at
  • Microsoft Access

    Dynamically Resize a Subform Based on the Record Count


    Today’s guest blogger is Vinny Malanga. Vinny is the CTO of IMI Software, Inc. , which specializes in software development for the real estate and property management industries.

    Many Access developers implement subforms to display data. Most often, at design time, the developer sets the subform to a specific size. In many cases this is fine, but there are some instances where it would be preferable to dynamically resize the subform at runtime, based on the number of records that it contains, up to a maximum number. Once the maximum number of records is reached, a vertical scroll bar should appear allowing the user to scroll through the rest of the records. The maximum number is arbitrary and simply used to prevent the subform from exceeding a certain height.

    I’ve provided a sample database that you can download here. The database contains a table, two forms - a parent form and a subform - and a module with the resize method.

    In short, when the parent form loads, it calls a function on the subform which handles the resizing. It first retrieves the height of the header, footer and detail section of the subform. From there the subform is resized based on the height of the header, the height of the footer, and the height of the detail times the number of records. There are a couple of twips added to the total number to even out the border around the subform. Next, the record count of the underlying data source is retrieved. If the record count is greater than the maximum number of records you’ve defined, the ScrollBars property of the subform is set to 2 (Vertical Only), otherwise 0 or None. Lastly, in case there are any controls that exist under the subform, another function is called which repositions those controls based on the calculated height of the subform.

    I hope some of you find this technique useful.

  • Microsoft Access

    Jump start new Access databases with a custom blank database template


    Today's guest blogger is Access MVP Glenn Lloyd of Argee Services. Check out his Office help blog and Access Help and Tutorial Blog.

    There are certain features that virtually every Access database application includes. Certain tables like people’s names and addresses, certain forms for managing lookup lists and many to many relationships, custom splash screens, and on and on. Particular developers may prefer ‘off-standard’ property settings for forms and reports and for the database itself. Depending on the general type of databases a developer focuses on, the developer might set the same references in every project.

    Manually importing these common features can take a fair amount of time. Access 2007 introduced the ability to reduces that startup overhead literally to no time at all. All you need to do is put together a default Blank database template and store it in the right location. Then for your next new project create a new Blank database. The new database will inherit absolutely everything that the template includes. It will have exactly the custom property settings that you need.

    Including custom property settings in prototype forms and reports in the template will continue to yield time saving dividends, beyond what you have already saved in the creation of the database itself, because each form and report you create from those prototype objects will be populated with exactly the settings you want. Allen Browne, from whom I first learned of this capability, has some excellent tips on template contents and how to set up the template this article

    Give the idea a try. You will be glad you did!


    Send your Power Tips to Mike and Chris at
  • Microsoft Access

    SageKey special offer


    Steve Bailey recently emailed us about changes to the SageKey product line and a special discount they are offering:

    SageKey Software has been busy building new products to provide the right solution to the common installation issues. As usual we’d like to offer a discount to readers of this Blog.

    Not only have we added more functionality to our installation wizards as per customer requests but we’ve broken our products out into functional versions.

    One request we get a lot is to create a solution that can be slotted into an existing install. We now have a basic version of our tool which will give you all the SageKey benefits (no reconfiguration dialogs and no macro warnings etc) which can be deployed with any install including InnoSetup and an install built using the Access Developer Extensions.

    You can download a demo from the Sagekey website (

    We’ve fully tested these products with machines running the Office 2010 Technical Preview and so these tools will form the basis of the release for compatibility with machines running Access 2010.

    Until the end of September 2009 we offer our products to anyone who mentions they saw this Blog article with a 10% discount.

  • Microsoft Access

    New Access fan page on Facebook


    In our never-ending quest to spread the word about Microsoft Access, we now have an official fan page on Facebook! Our news feed includes links to articles on MSDN, TechNet, and Office Online, as well as posts on this blog. You can choose to see our posts in your Facebook news feed automatically, or read them only when you want to. Similarly, if you post something on the fan page, you can choose to be notified whenever someone responds to you.

    Here’s a quick walkthrough of how the Microsoft Access fan page on Facebook works:

    Join the Access fan page

    If you already have a Facebook account, sign in, and then follow these simple steps:

    1. Visit
    2. Near the top of the page, click the Become a Fan button.


    Browse through Access wall posts

    Once you’ve joined the Microsoft Access fan page on Facebook, you can begin browsing for posts right away.

    To switch the default view to the page wall, click the Wall tab on the top navigation bar near the Access logo:


    You can now see all of the wall posts by the administrator and by other Access users just like you. Wall posts are shown in chronological order and the newest posts appear near the top of the page.

    Join the conversation! Reply to an existing wall post that interests you, or make your own post to start a new discussion or to share something with the other members.

    Hide automatic Access fan page updates

    Depending on when you join the Access fan page, you’ll probably see automatic updates in your Facebook news feed within a few days. If you like seeing these updates, you don’t need to do anything else. Check out the ones that sound interesting and ignore the ones that you don’t care about on any particular day.

    If you don’t want automatic Access fan page updates to appear in your news feed, you can easily disable them by following these steps:

    1. While signed in to your Facebook account, go to the Facebook home page at
    2. Scroll through your Facebook news feed (the center column of your home page) and find any one of the Access fan page updates that have appeared.
    3. Move the mouse pointer over the Access post until you see a Hide link show up near the right side of the post.
    4. When you move the mouse pointer over the Hide link, it becomes a button. Click it.
    5. On the mini-menu that appears below the Hide button, click Hide Microsoft Access.

    Facebook will briefly display a yellow confirmation box, informing you that Access updates will now be hidden from view.

    Tip  While Access fan page updates are hidden from view in your Facebook news feed, you can still visit the Microsoft Access fan page at any time by entering Microsoft Access into the Search box near the top of any Facebook page and then clicking the search result. It’s a fast way to return to the fan page whenever you want to check for updates on your own.

    Restore hidden Access fan page updates

    If you previously disabled Access fan page updates from your Facebook news feed and you’ve changed your mind about seeing automatic updates, you can easily restore them by following these steps:

      1. While signed in to your Facebook account, go to the Facebook home page at
      2. Scroll to the very end of your Facebook news feed (the center column of your home page) until you see the navigation bar in the page footer:
        The bottom navigation bar in the Facebook stream
      3. Click the Edit Options link.
      4. Near the top of the Hidden from News Feed popup dialog box, click the Pages link.
      5. Scroll down until you see Microsoft Access, and then click the Add to News Feed button.

    When you have completed these steps, you will once again see automatic Microsoft Access fan page notifications in your Facebook news feed.

    Share what you know!

    Post to the wall! It’s just a conversation with like-minded people who enjoy and rely on Access in similar ways as you.

  • Microsoft Access

    Cool Tool Tips: A Better Control Tip


    Tony D'Ambra of, maker of Access add-ins and switchboards, wanted to share his method for providing help to users when and where they need it.

    In Access, the functionality of tool tips has not changed since they were introduced in Access 95 ten years ago.

    In a free sample Access 2000 database Cool Tool Tips, which you can download here, I have implemented a more functional and controllable paradigm, which also looks great. If you have an application that is to any degree complex to use, Cool Tool Tips is an easily-implemented solution that avoids the need for a Help file, and keeps tips on the screen for however long they are needed by the user.

    You can learn more about Cool Tool Tips here.

    Send your Power Tips to Mike and Chris at
  • Microsoft Access

    OpenGate releases new version of Designer


    Brandon from OpenGate recently sent email about a special offer for blog readers for the latest version of Designer for Microsoft Access. Check it out.

    OpenGate Software is offering 50% off their new version 3.0 of Designer for Microsoft Access.   Designer creates a normalized Access database after users follow a simple Q&A format wizard to define what they want to track in their database.  The newest release automatically generates complex data entry forms with all related information displayed in a single continuous form.  From now until September 10th you can buy Designer for only $99 (a 50% discount). Here is a little preview:

  • Microsoft Access

    Submit your Power Tips!


    The Power Tips feature of the Access Team Blog has been running for about 3.5 months now, so we thought it’d be a good time to remind everyone how the feature works.

    What’s a Power Tip?

    A Power Tip can be a code sample, some SQL, an expression, a series of property settings, or some other Access trick that you have learned along the way, and that you think might be useful to others. They can be old favorites or new discoveries.

    Who can submit Power Tips?


    Can I see some examples?

    Sure, click this link (or just click Power Tips over on the right).

    Who collects and posts the tips?

    Chris Downs and Mike Stowe, Access writers for Office Online and MSDN, respectively.

    How do I submit a tip?

    Just send it to us at We’ll help you flesh out the idea if you want. If it seems to fit the Power Tip criteria, we’ll post it and give you whatever attribution you prefer. If not, we’ll help you find the best forum for it. Video demos are welcome and encouraged.

    Hope to hear from you soon!


    Chris & Mike

  • Microsoft Access

    Use OMPM to Prepare for Migration to Access 2007


    Today’s guest writer is Shawn Sullivan from the Office Shared team. I have asked him to talk about the work he has done to help organizations move to newer versions of Office.

    In March, we made a post about Office Migration Planning Manager (OMPM), which is a suite of tools to help analyze your environment for migration to Office 2007. Its primary function is to scan Word, Excel, PowerPoint, and Access files in the Office 97-2003 binary file format, and provide analysis of any issues those files might experience being converted to the Office 2007 OOXML format, as well as any possible issues those files might exhibit simply being used in Office 2007.

    OMPM provides you with several techniques you can leverage specifically concerning migrating to Access 2007. Here’s a short list of how the tool can help you plan for migrating to Access 2007:

    Identify files are in your environment

    As the March post mentioned, OMPM can be used as a file inventory tool to identify all Access files (as well as Word, Excel, and PowerPoint) in your organization. Both the light and deep scan modes can accomplish this task.

    Analyze Access files for issues

    OMPM’s deep scan mode will actually crack open files from Access 95-2003 and scan them for potential migration issues. The full list of issues as well as pointers to documentation are at the bottom of this post.

    Develop plans to deal with issues

    OMPM can generate reports from scan data which show you what migration issues are present where in your environment. In combination with OMPM documentation, this allows you to test and plan for migration.

    OMPM can be downloaded here so you can try it out and see what it can do to help assess your Access 2007 migration. It requires no installation, although SQL 2000, 2005, or 2005 Express are required. SQL 2005 Express is available for free to help you get started looking at OMPM, and works well for smaller (<100,000 files) scan analysis.

    Screenshots and error details

    Here’s a few shots of OMPM reports (personally identifiable data obscured). First, a report showing all scanned files and any issues found:


    And second, a closeup of a report listing all issues found in a scan, and their frequency:


    Finally, here’s a list of the errors OMPM can generate about Access files.






    Too many code or class modules




    Sort order and system language do not match




    Replicated database




    Database is an MDE file




    DAO 2.5/3.5 compatibility layer




    Microsoft Jet SQL help




    Additional References




    Missing References




    Reserved Name in Form/Report/Macro




    Linked Tables




    Database in uncompiled state




    Backup database




    Sample database




    Old Database




    Secured Database: User Level Security




    Secured Database: Database Password




    System Database




    Unsupported legacy format




    Unsupported legacy format - forms, reports, and modules will be lost



    Errors 1-17 are the complete list of errors which can be generated by the Access Conversion Toolkit, OMPM’s predecessor. Documentation for 1-17 can be found in the Access Conversion Toolkit. The documentation is Flash-based and requires the installation of the toolkit from here.

    Errors 18 and 19 are new to OMPM and mean

    18 – Access 2007 no longer opens files created in Access 2.0.

    19 – Access 2007 will open a file that was created in Access 95, but will not read its forms, reports, or modules.

    Summary and links

    Here are links to useful sites with more info on OMPM:

    If you have any questions or comments, please feel free to leave them in the comments section of this post or on the wiki. And if you have helpful tips of your own about OMPM, please share them on the wiki!

  • Microsoft Access

    Create custom menus and shortcut menus by using macros


    About a month ago, we posted a Power Tip from Edwin Blancovitch about creating shortcut menus by using VBA. However, if for some reason you’re not interested in cracking open the Visual Basic Editor, there is a way to create shortcut menus (the menus that appear when you right-click an object) just using macros. See this article on Office Online to see how to get started.

    Have an Access Power Tip that you want to share? Send it to Mike and Chris at
Page 20 of 20 (492 items) «1617181920