Jun 14

New Technical Support Forum and Ticket Tracking for FMS

We are very pleased to announce our new technical support site (http://support.fmsinc.com) to provide forums and the ability to submit technical support inquiries.

Our new site lets you submit requests and respond to them via email with our support team. It also lets you visit our site to check the status of your requests and their entire chain of communications. You can login directly or use affiliated logins from Facebook, Google, and Twitter.

You can also read information and ask questions to the community on topics related to Microsoft Access, Visual Studio, LightSwitch, and SQL Server. We hope you’ll join us.

Additional support resources are available here:

Jun 07

Comparison of Microsoft Access, LightSwitch and Visual Studio Platforms for Database Developers

Last month I spoke at the Portland Access Users Group Conference at Silver Falls State Park. I gave a presentation introducing Visual Studio LightSwitch and how it could be used for SQL Server applications deployed on a variety of platforms. As a follow-up, I’ve created a summary matrix and discussion that highlights the features and limitations of the variety of platforms from Microsoft Access, Visual Studio LightSwitch, and Visual Studio.


Microsoft Access started at the beginning of the Windows revolution 20+ years ago and became the most popular database of all time. More recently, additional technologies have become significant, so it behooves the Microsoft Access community to be aware of the trends and options.

Database Platform Matrix

Ultimately, it’s about being able to create solutions that help you and/or your users accomplish their mission. Sometimes the user’s platform is critical, sometimes, it’s the data source, and other times it’s the permissions you have to deploy a solution. A variety of platforms and options are available with benefits and limitations with each. Meanwhile, Microsoft Access is also evolving with their latest Access 2013 version offering new web based solutions.

We’ve written a new paper, Comparison of Microsoft Access, LightSwitch and Visual Studio Platforms for Database Developers  that summarizes what we’re seeing and experiencing.

Mar 20

Microsoft Access Query Sorting on Multiple Date or Numeric Fields with Blank Values

Read our new paper, Microsoft Access Sorting on Multiple Date (or Numeric) Fields with Blank Values.

Learn how to sort on multiple date fields when null values exist by using a calculated field. By using the IIf function with Is Null, you can easily sort to see the most recent (or oldest) records.

Microsoft Access QueriesWe’ve included information on avoiding the IsNull function to maintain SQL Server compatibility. Additional information and examples are provided to show why using the NZ function (NullToZero) is not equivalent and returns the wrong results.

This new paper is part of our Microsoft Access Query Help Center.

Mar 13

Mistakenly Blaming Microsoft Access instead of the Developer

In this March 10th Washington Post article, Alexandria tailor weaves custom solution for taking orders, a local firm is mentioned having struggled with Microsoft Access and being forced to migrate to a new system due to problems with their Access database. In particular, their database couldn’t provide multiuser support and lost data when more than one person used it.

Unfortunately, stories like this perpetuate the myth that Microsoft Access features are limited rather than the lack of skills of the developer who tried to customize it. It’s a shame the business owner and developer weren’t aware that Access could address the multiuser issues they encountered; thereby saving time, money, and headaches from having to migrate to a new platform.

Microsoft Access is Multiuser Ready

Microsoft AccessThe reality is that Microsoft Access is fully capable of providing multiuser support if it’s designed properly. For basic database solutions with under 1GB of data (maximum 2GB), Access comfortably supports up to 200 simultaneous users with a properly designed solution.

Microsoft SQL ServerAs the number of users and data expands, Access makes it relatively easy to migrate the data storage from an Access database to SQL Server, while maintaing the application layer (forms, reports, code, etc.) in Access. This also lets you share the SQL Server data on web sites and other platforms. That means supporting two users in a tailor shop would be trivial with MS Access.

Split Database Architecture for Multiuser Solutions

People sometimes treat Access databases like Excel spreadsheets and want each user to open and close the same file. That’s not the way to support multiuser data sharing in Access. A split database architecture is needed to separate the application layer from the shared database. Each user gets their own copy of the front-end database application that links to the tables in the shared database.

Microsoft Access includes a built-in wizard to split the database and another wizard to link the front-end database to back-end tables. We wrote a paper about this years ago called, Microsoft Access Split Database Architecture to Support Multiuser Environments, Improve Performance, and Simplify Maintainability.

While having a web application has its role and advantages, the article mentions their internet connection isn’t reliable and their business is negatively impacted when that happens. That’s an unfortunate result of their new platform. There are ways to create hybrid solutions to provide on premises support with shared web solutions, so these issues need to be considered when creating business critical solutions.

Using Microsoft Access Strategically

Small businesses often have very limited budgets and time to understand technological options. Completely eliminating Office and Access as viable solutions for incorrect reasons is wasteful. Microsoft Access addresses an important segment of database needs, and offers small businesses and information workers the ability to make modifications and extensions that other platforms do not allow so easily. Understanding where and how to use Microsoft Access effectively along with its limitations offers organizations of all sizes a competitive advantage. We’ve helped many small businesses, non-profits, and multi-national companies properly use this technology very effectively. Here’s our article on Microsoft Access within an Organization’s Database Strategy that discusses this in more detail.

Conclusion

There are lots of terrible applications created on every technology platform whether it’s Microsoft Access, Excel, Visual Studio, Java, Oracle, SAP, etc. In this case, the skills of the Access developer were clearly lacking. Getting that confused with the technology is misguided.

Additional Resources

For additional resources to build robust Microsoft Access solutions and understand what’s possible, visit our:

Feb 18

Attending the Microsoft MVP Summit in Bellevue/Redmond, WA

I’m attending the annual Microsoft MVP Global Summit this week in Bellevue and Redmond, WA. This is actually my first experience at this event as I was awarded the MVP title this past summer for my support of Microsoft Access.

Over the years, FMS has had several Microsoft MVPs for Access including Dan Haught, Steve Clark, and Jim Ferguson who was one of the original MVPs when the program started 20 years ago. Book author Alison Balter and Portland Access User Group leader Jack Stockton join me as new MVPs this year. Last night we had a nice kickoff event with fellow Microsoft Access MVPs.

The MVP conference brings together 1500 professionals from across the world to this conference. The MVPs cover all the different product groups for Microsoft which offers a wonderful mix of expertise and enthusiasm. Over the next few days, the different Microsoft product groups will be providing presentations to attendees in an NDA environment. Sorry, I can’t share the content, but I can say it impacts our future planning.

Yesterday, they had a showcase of a variety of technologies from MVPs in the US plus companies from Taiwan, Japan, Germany, China, India, etc. It’s great to see the global impact of Microsoft.

How do you become an MVP? The usual path is to be involved in public forums answering questions and becoming an expert in the field. You don’t need to own a business to be an MVP. Other ways to be selected are to increase your professional visibility through products, writing books, blogs, etc. The MVP program is designed to recognize individuals who influence the market and help the community maximize the value of Microsoft products. So whether it’s XBox, Bing Maps, Dynamics, Exchange, Office 365, Excel, Outlook, PowerPoint, SharePoint, Visual Studio, Windows Phone, Word, etc., if you have a passion, expertise, and a willingness to share, the MVP community could be part of your future. Good luck!

Feb 12

Portland Oregon Microsoft Access User Conference

Silver Falls State Park, Oregon
May 4-6, 2013

FMS President Luke Chung is one of the featured speakers at this annual Microsoft Access conference hosted by the Portland Access User Group. This will be his third year speaking at this wonderful event.

Enjoy an amazing, rustic getaway to a beautiful state park with fellow Microsoft Access enthusiasts. Book early so you can stay at the limited number of cabins available at the conference center. The conference fees are amazing low and includes meals.

LightSwitch and Microsoft AccessLuke will participate in various talks on Microsoft Access development, running a business, and creating solutions using Visual Studio LightSwitch. He’ll also be staying at the site during the entire conference, so you’ll have plenty of opportunity to meet him formally and informally.

For more information, visit the conference site.

Feb 11

Migrating BlogEngine.NET Posts to WordPress with Translations Using Microsoft Access

Migrating Our Site from BlogEngine.NET

We could have started our new blog from scratch but since our existing blog existed for many years, we wanted to migrate it with all the comments from our BlogEngine.NET host to WordPress. That turned out to be a tricky process but we managed to do so. To help others who might be facing the same situation, here are the steps we followed so you don’t have to make the same mistakes we did:

Prepare the Existing Blogs for the Migration

The first step is to make sure your existing BlogEngine.NET blog is working properly and ready for export. One of the tricky and time-consuming parts of this is the reference to graphic files. BlogEngine stores its embedded graphics in its own structure using syntax similar to this (our blog was in the BLOG folder):

src=”/blog/image.axd?picture=banner.jpg”

Note that this only impacts graphics that were uploaded into BlogEngine. If you referenced images that already existing on your website, those references are fine and do not need to be modified.

To fix the image.axd? references and eliminate future dependencies, it’s best to store these graphics in your website explicitly. Once you save the graphic files, you can update your blogs to reference them. Saving the individual pictures is a manual process and you’ll need to decide where to store them on your website. You can then manually update the affected blog topics. Alternatively, you can do a search and replace later after exporting the blog’s XML file. We did a combination of both.

Export the existing BlogEngine.NET data to an XML file

Export the existing BlogEngine.NET data to an XML file. This is available as the last option under Settings from BlogEngine. The default name is BlogML.xml

Unfortunately, even if you fixed the picture image references, you’ll still need to translate the file to a format that WordPress can import. That requires making many changes. We actually exported the XML file, then parsed it to find the references to

src=”/blog/image.axd?

to identify any image references that were still in BlogEngine. That gave us the choice to either fix the original blog and re-export, or to fix it directly in the XML file.

Preparing WordPress

The WordPress import tools are under Tools, Import. To import the BlogML file, you need to install the appropriate WordPress PlugIn. The BlogML plugin that worked for us was BlogML-WordPress-Import.zip which can be found here. You’ll need administrator write rights to your WordPress folders to install this.

Before you modify the BlogML file, you may want to import it to see the problems that need to be addressed in WordPress. You can do so and trash them in WordPress without any harm.

Using Permalinks with Post Names

By default, WordPress saves and displays its posts by ID number in the URL. If you want posts to have more meaningful names which also helps with SEO, you should set the preference under Settings, Permalinks, and choose Post. We set this but the pages triggered a 404, Missing File problem.

We discovered that this translation didn’t work on our WordPress host (Windows using IIS) unless we added a web.config file in the root of the blog with this information:

<?xml version="1.0"?>
<configuration>
<system.webServer>
<rewrite>
<rules>
<rule name="Main Rule" stopProcessing="true">
url=".*" />
<conditions logicalGrouping="MatchAll">
<add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
<add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
</conditions>
<action type="Rewrite" url="index.php/{R:0}" />
</rule>
</rules>
</rewrite>
</system.webServer>
</configuration>

Translating the BlogXML file with Microsoft Access

Now that we established the foundation to import the XML file and display the posts with the proper Permalinks, we could see several things still need to be fixed. It was relative easy to do with multiple search and replace terms. We did this in Microsoft Access:

  • Create a table with two text fields. One for the Original value and one for the New value to replace it. We then populated the table with the terms to translate:
    • Hyperlink references. Since we migrated our blog from a subfolder (www.fmsinc.com/blog) to its own subdomain (blog.fmsinc.com), we needed to modify all the hyperlink references that were pointing to our web pages to explicitly point to our www.fmsinc.com web site. That meant, we needed to adjust our href=”/ syntax to “href=”http://www.fmsinc.com/”, so we added these two values to our table.
    • Existing Image references. Similarly, we needed to adjust our image src=”/ references for graphic files to “src=”http://www.fmsinc.com/”, so they were added. Note, we didn’t search for “img src”  because many references included style settings between the “img” and “src”.
    • New Image references. This is also the time to add any explicit image.axd? references to the new location of the graphics if you didn’t want to manually edit the original posts.
  • Total Visual SourceBookBlogEngine saves category names as GUIDs and references the GUIDs in each post. If you don’t translate these, they’ll be imported into WordPress with the GUID rather than readable category name. We used the CXMLSettings class from Total Visual SourceBook to read the categories section of the XML file so we could pair the GUID and category names.
  • Perform the Search and Replace
    Once the table contains all the terms to translate, we wrote a simple routine to read the XML file into a variable, then go through the table and use the VBA REPLACE function for each record. When we were finished, we wrote the text to a new XML file for WordPress to import.
  • From WordPress, import the new file using the BlogML import plugin.

Because we programmatically perform the translation process, it was easy to test, run, and refined the entire process when things didn’t work correctly. It took us a few iterations but we were pleasantly surprised how well the posts came across.

We found that we needed to manually touch up some of our posts. The HTML in WordPress doesn’t require the use of paragraph styles (<p> </p>) to define each paragraph and automatically strips them out. Unfortunately, it displays the line breaks in paragraphs which is normally ignored in HTML syntax. We had to manually edit and delete those so the posts properly word-wrapped.

Dec 31

Total Visual CodeTools 2010 Updated for Microsoft Access Office VBA and Visual Basic 6

Microsoft Access 2010 VBATotal Visual CodeToolsMicrosoft Access VBA, VB6 Module Coding ToolsModule Code Cleanup and Builders

Visual Basic 6An update of Total Visual CodeTools 2010 is released with support for Microsoft Office/Access 2010, 2007, 2003 and Visual Basic 6.0. Several enhancements were made to improve the New Procedure Builder, VBE color scheme builder, icons for the toolbars, setup program and IDE integration, etc. Here are the details on the latest enhancements of Total Visual CodeTools.

Total Visual CodeTools is an add-in to your Office/Access/VBA and VB6 integrated development environment (IDE) that helps you write, clean-up and deliver better solutions. Available from the IDE menu or a toolbar, a variety of tools help increase your efficiency and consistency when writing code and taking over someone else’s work. Features include:

  • Code Builders to create new procedures, properties, ADO and DAO recordsets, message boxes, select case statements; convert SQL strings, copy control event procedures, use the Format and DateDiff functions, comment blocks of text, etc.
  • Cleanup Existing Module Code by adding Option Explicit, standardizing formatting, adding error handling to procedures that lack it, rename variable names to your convention, etc.
  • Deliver Modules with Line Numbers plus obfuscation
  • Find Unused Variables to pinpoint unnecessary code
  • Macro Recorder for playing back keystrokes while editing

Registered customers should have received email notification with download instructions of the update.

Dec 26

Total Access Statistics Update for Microsoft Access 2010, 2007, and 2003

Microsoft Access 2010

We are very pleased to release an update to Total Access Statistics for Microsoft Access 2010, 2007, and 2003. If you are an owner of version 14.0, 12.8, and 11.8 respectively, you can download the update at no charge.

Total Access Statistics is the most popular data analysis program for Microsoft Access. It extends the data analysis capabilities of Access queries to let you perform advanced numerical analysis on your data. Use any Access table, linked table, or query to perform calculations such as percentiles, regressions, frequency distributions, t-Tests, correlations, non-parametrics, rankings, moving averages, etc. It can also perform data normalization and let you select random records. As you would expect in a query, you can specify Group By fields so analysis is performed on each set of records with identical group fields. Total Access Statistics runs within Access with all output in Access tables. It supports MDB, ACCDB, and ADP databases.

The update includes these enhancements:

  • Significant performance improvements when processing large numbers of records
  • While analyzing records, a new status form appears with an option to cancel the process
  • Setup program offers machine or current user installation options
  • Resolves all known issues

For additional information, visit the Total Access Statistics Update page.

Oct 22

Microsoft LightSwitch Application Development with SQL Server

Microsoft LightSwitchMicrosoft Visual Studio LightSwitch for Microsoft Access, SQL Server, and Visual Studio .NET Database Developers

The Visual Studio team has introduced a development platform called LightSwitch which simplifies the creation of database applications in Visual Studio. This rapid application development environment lets you create solutions that can be easily deployed on Windows or Mac platforms from a public web site or Intranet.

We’ve been using LightSwitch for the past year and find it to be ideal for a particular class of SQL Server based solutions. Here our first article providing an overview of the benefits and limitations of the LightSwitch platform for the Microsoft Access community. The paper includes:

  • Sample screens
  • The Visual Studio .NET Platform
  • Working with SQL Server and SQL Azure
  • Rapid Application Development
  • Silverlight Dependency and Limitations
  • Supporting Windows, Mac, and Web Browsers
  • Future Support of HTML5
  • How Microsoft Access Applications can Evolve into LightSwitch

If you’re interested in learning more about our help developing custom solutions based on LightSwitch, visit our LightSwitch Consulting page.