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.
Luke 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.
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):
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
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.
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:
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.
BlogEngine 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.
An 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.
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
Microsoft 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.
When you try to open a database, you may encounter this error message: Could not use [Database Name]; file already in use.
This error occurs if another user (or instance of Access) has opened up the database exclusively and you try to open it. Maybe the other user opened the database in exclusive mode or made some modifications such as editing modules, which put the database in exclusive mode.
However, that may not be the case. This error can occur even if the other user has the database opened without opening it exclusively. What’s going on? Why would the second instance think it’s being opened exclusively?
What if the Other User isn’t Opening it Exclusively?
It turns out that another cause of this error is not immediately obvious and is related to workgroup security files. This applies to MDB files (not ADP or ACCDB formats). If two instances of Microsoft Access (DAO database engine) are using different system MDW files, a conflict may occur where the database appears to be exclusively locked by the other instance. The MDW file used by your instance of Access can be checked in VBA by examining the value of DBEngine.SystemDB.
Another copy of Access or DAO may have that database opened using another MDW file causing this conflict. This can come from another instance of MS Access, a Microsoft Access add-in or library reference, a VBA Add-in, or other program such as VB6, .NET, etc. that’s connecting to that database.
To avoid this, if you’re using workgroup security, make sure every instance is pointing to the same shared MDW file. This is often defined in the shortcut used to launch Microsoft Access, or if you are opening a different database programmatically within your VBA code.
If you’re interested in seeing who’s going in and out of your database in real-time, check out our Total Access Admin program. It’ll let you monitor multiple databases across your network to see when users enter and exit your database and flag instances where connections are dropped suspiciously.
On August 14, 2012, Microsoft released a security update for Microsoft Office that includes an update to the Windows Common Control MSCOMCTL.OCX.
Unfortunately, this update causes problems with existing Microsoft Access, Visual Basic 6, and other applications that use the common control such as the Treeview and ListBox controls. For us, this impacts our Total Access Analyzer program which uses the Treeview control in its database explorer feature. It triggers this error:
Total Access Analyzer is the most popular Microsoft Access product of all-time! Documenting and analyzing your database objects, macros and VBA module code, Total Access Analyzer helps you understand what's going on. Take control of your MS Access applications and find errors and ways to improve their design and performance. Over 300 issues are pinpointed with 380 presentation quality reports for comprehensive documentation and cross-referencing of your application. Winner of every Best Microsoft Access Add-in award with great reviews, Total Access Analyzer remains the "Must Have" tool for serious Microsoft Access developers and people who inherit existing Access solutions.
Here’s a response to a question about the standards we implement for our Microsoft Access applications. These are some fundamentals for ensuring a solid foundation for professional Access solutions in priority with some resources related to them:
Split Database Design. Without this, it’s nearly impossible to enhance the database while others are changing the data.
Cleaning up VBA code. From code indentations to applying our variable naming conventions along with prefixes for global vs. module vs. procedure, and constants vs parameters vs. regular variable names. We use the Code Cleanup feature of Total Visual CodeTools to do this. Personally, without cleaning it up to our standards, I find it nearly impossible to get any work done while I’m struggling with someone else’s convention.
Setting up a development, testing and deployment process so changes can be implemented safely and efficiently. Lots of issues around this but having a fast, stable way to manage changes and how people launch the Access application is critical. We use our Total Access Startup program to help with this.
After these structures are in place, we’re ready to run Total Access Analyzer against the database to address the issues it finds wrong and could be improved in the application.