Microsoft Access Reports and MS Word

Recently I had a request from a client with a legacy Access database. This database produced some nicely formatted reports, complete with graphics, which they needed to export to MS Word for further editing.
I was amazed to find that this feature is not supported within Access. You would thin that two products such as these being part of the same suite, and such mature products at that, would support such a seemingly basic feature. It is common for report add-ins for Visual Studio to have Word as an export option. But, sadly, not Access.

The best I could do for the client was to export to RFT, which lost the grapics and most of the formatting. The full answer would be to rewrite the system to create the reports in Word via automation, but this is never an easy solution, and with 23 complex reports to convert, not a quick one either.

Visual Studio 2010 Missing Configuration Manager

One strange problem I had with VS 2010 was locating the configuration manager, in order to switch between debug and release modes.

To get this visible off the Debug menu, I had to go to Tools\Options
Choose Projects and Solutions, and then the General Tab
Check the "Show Advanced Build Configurations"

VB.Net Convert Automate Excel to convert csv file to xlsx

I needed to export 9000 rows from SQL to a xlsx file. I had no choice over the output format, because this was required by another system.

At first I tried automating a xlsx file directly, and outputing the data one cell at a time. This was very slow.
In the end, moved all the data into a CSV string, wrote this to the file system as a CSV file, then used Automation to open this file in Excel 2010, and save it as xlsx.

This was actually a lot harder than one might have expected, especially getting Excel to save in the right format, and to cleanup after itself.

Below is the code that finally worked. A simplified version of this would do the job, but I found all of this is required to cleanup Excel afterwards. Every part of Excel that is used must have its own variable assigned, so that .Net can do its garbage collection successfully, and to leave no orphaned processes running.

        Dim Excel As New Microsoft.Office.Interop.Excel.Application
        Dim WorkBooks1 As Microsoft.Office.Interop.Excel.Workbooks
        Dim WorkBook1 As Microsoft.Office.Interop.Excel.Workbook
        WorkBooks1 = Excel.Workbooks

        WorkBook1 = WorkBooks1.Open(TempPath)

' XlFileFormat.xlWorkbookDefault = 51 - this is correct, although a lot of forums recommend
        WorkBook1.SaveAs(Filename:=TempXLSXPath, FileFormat:=XlFileFormat.xlWorkbookDefault)

        'CLEANUP

        WorkBook1.Save() 'trying to find a way to get this to close without a prompt
        WorkBook1.Close(False)
        WorkBooks1.Close()
        Excel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook1)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBooks1)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
        WorkBook1 = Nothing
        WorkBooks1 = Nothing
        Excel = Nothing

Word Normal.dot template corruption

When automating MS Word, one of the problems that can be encountered is corruption of the normal.dot template. When this happens, the template can be deleted, because it will automatically be recreated with default settings. It would have been nice after all these years, and thousands of features that have been added into Word, if they had a built in feature to do this.

The manually process is not entirely straight-forward, because the normal.dot template can exist in many places.


Here are the basic steps to delete it.

1. Close all word documents. Run task manager, and check applications and processes to make sure there are no instances of winword.exe running. 
2. Close Outlook too.
3. Click on the Start option, click Search, then Files or Folders.
4. In the Name box, type the name of the global template for your version of Word. For Office 2007/2010 this will be normal.dotx or normal.dotm. For older versions of Office, this will be Normal.dot
5. In the Look In box, select your local hard drive
6. Click Search to locate the file.
7. For each occurrence of global template that appears in the Find dialog box, click the right mouse button on the file and then click Rename. Add a new file name, such as OldNormal.dot or Normal-1.dot.
8. Retest the application that is creating the Word document.

Completed Course on Lynda.com

Today I completed the Introduction to SharePoint 2010 course on Lynda.com.
I found the course to be very helpful, very well presented.
It suffered from none of the hesitant mumbling that in my experience often mark on-line software course, but was fluent, enthusiastic and informative.

I learnt a lot in a a few hours, and I can see that SharePoint is a powerful product with a lot of potential, and should make conventional web designers very nervous.

Am very interested in what a developer can add to SharePoint. It works with Visual Studio and SQL Server, so their are many development options with it. 

Introduction to Sharepoint 2010

Sharepoint can be hard to grasp as a concept, because it does not do one thing. Exchange handles emails, calendars, SQL Server is a database server, IIS is an internet server, but we cannot apply such a simple description to Sharepoint.

Sharepoint is not an application, but it is a platform, which is capable of hosting a variety of integrated solutions.

Sharepoint started life as Microsoft SharePoint Portal Server in 2001

These days several products may carry the Sharepoint label.

The server products are

/H

1. SharePoint 2010 Foundation
2. Sharepoint Server 2010 - Standard
3. Sharepoint Server 2010 - Enterprise

Each of these server products includes all the functionality of the version below it.

SharePoint Sites

SharePoint enables the creation of websites.
These are all based on a standard pattern, and all share certain features in common. ALL SharePoint sites are built on collections of lists and libraries.

So even a brandnew  blank websites have certain features as standard, such as "Like" buttons.
One big difference in Sharepoint 2010 from previsous versions, and from WebSites generally is the ribbon. Similar to the Ribbon in office, this allows you to edit SharePoint sites.

The Ribbon is access by clicking on Edit on a SharePoint page.

If you have permissions to edit a site, the Site Actions menu is very important. This will allow you to make modifications to the Sharepoint site.

The elements of Sharepoint are available individually available elsewhere, such as shared calendars and todist list, colaboration on documents - such as Google Documents.

It is the ability to combine them all together, without needing developer experience, which makes it very powerful. Add together SharePoint in the cloud, and it looks a very useful business tool.

Visual Studio 2010 Datasets with SQL Server 2000

It is possible to have a Visual Studio 2010 running aginst SQL Server 2000

I have a client running SQL Server 2000 and I have a UI developed in Visual Studio 2010. When trying to create reports for this I got the message "This server version is not supported.  You must have Microsoft SQL Server 2005 or later".

The client is not going to move to a new version of SQL Server just yet, so I had a problem.

The work around I came up with is to copy the database to SQL 2005 in my development environment, create the dataset and reports against this version at design time, then at run time, switch back to SQL2000. This worked fine.

Another problem I had to overcome was using temp tables when working with VS datasets.
The answer is to put this statement right at the beginning of the stored procedure:


 IF 1=0 BEGIN
     SET FMTONLY OFF
 END 

Text Type Deprecated in SQL Server 2008 and above

How to handle MS SQL Text type data now that it is deprecated

The text type, which was always a special case when creating triggers, stored procedures and the like is depracated from SQL Server 2008.

To update systems, replace the TEXT type with VARCHAR(MAX) or NVARCHAR(MAX)

In vb.net, change the parameter declarations from TEXT, to this
        cmd.Parameters.Add("@Notes", SqlDbType.VarChar, -1).Direction = ParameterDirection.Output

New Features In Visual Studio 2010

A partial list of some of new features in Visual Studio 2010 that I think I will find useful

1) Auto-Implemented Properties. 
For my database derived code I automatically generate all the properties anyone, but other than that declaring properties with their corresponding Get/Set methods is one of the least exciting tasks. Microsoft have made it easier with auto-implemented properties.
Simply declare the property like this:


   Property ClientID as Integer 

Visual Studio will automatically generate a hidden private member, and Get/Set methods

To make them visible if you need to customise them, for example, to add some validation, then type G or S on the line after the property declaration and VS will show the hidden methods.


2) Reference Highlighting
This will be a feature that I will miss when I work in previous editions of Visual Studio. When you click on a keyword, all instances of it are highlighted.
But much more usefully, when clicking on part of a construction such as a If..Then, all parts of that construction are highlighted. I think that this will be most helpful when working with nested statements.


Some features I am not so sure about:

• Lambda Expressions - these could make code harder to read, and the time savings over regular functions are minimal.

Google Cloud Connect

Today Carl Hughes of IT Hound recommended Google Cloud Connect.
This wonderful piece of technology allows me to use Microsoft Word and Excel, but have them synchronised to Google Documents. This gives me the benefit of having access to my documents from any internet enabled application and being able to collaborate on them with others, but still having the powerful editing capabilities of Microsoft's Office Suite.

Its easy to set this up
1)  simply download and install the Cloud Connect tool from here:
http://tools.google.com/dlpage/cloudconnect
2) Open Word or Excel and login to your Google Apps/Docs account
3) You can then choose to synchronize any document you create, and it will appear in your Google Documents account.

However, in testing this, I could only get it to work in one direction, from Word up-to Google Docs. Subsequent edits in Google were lost. 

Janus Winforms Suite and Visual Studio 2010

I have used Janus Winforms 2.0 and 3.5 with Visual Studio 2003, 2005 and 2008.


However, with Visual Studio 2010, when I tried to add Janus controls to a form, I found they were all disabled.


Investigating this I found that only Janus 4.0 will work with Visual Studio 2010.

Data Cleaning

What is data cleaning?

Data cleaning can mean removing duplicate records, corrupt records, and correcting and completing information already held in a database.

It can help avoid waste in mailings, and improve customer service.

I have now created a new domain, which will be used to promote the data cleaning side of my business.
dataclean.uk.com

I am unusual because I am offering a complete service, we can take a list of contacts and companies from a database, process them, then merge the results back into the original database system. My many years of database experience helps greatly with this process, and it is a lot more efficient than manually checking the database.

Among the services I am offering am:

• Finding duplicates, and dealing with them appropriately so that no data is lost.
• Updating individuals and companies that have moved, so their new address is on your database.
• Noting people who have opted out of marketing sales calls. In the UK there can be a fine for calling private numbers that are registered on the TPS list, or for calling businesses that are registered on the CTPS list.
• If someone is registered on http://www.the-bereavement-register.org.uk/ our customer's database can be updated to indicate the person has died. Receiving mail for someone who has died can be very upsetting for the relatives, and can make a business seem heartless. With this information the business can respond appropriately.
• I can update records so that the addresses match the Post Office's official address format.
• I can enhance data, by looking up telephone numbers of individuals and companies, obtaining turnover and staffing levels, and by retrieving key contact details for businesses.

Because the process is done in bulk, and I take responsibility for merging the results back into the master database the whole process can be very fast and efficient.

Google and Outlook Calendar Synch

Its easy to set-up two-way synchronization between Google's on-line calendar and Outlook.

Simply download the small add-in from here:

http://www.google.com/support/calendar/bin/answer.py?hl=en&answer=89955

During installation, supply login details for your Google account.

And that's it, changes to one calendar are reflected on the other.

Tested with Outlook 2010 running on Windows 7 64-bit

Could not load file or assembly 'Microsoft.SqlServer.BatchParser"

I had created an SQL Server tool kit, that allows me to document and generate code against SQL Server databases. This uses SQL Server SMO (Server Management Objects)

After moving my developement environment to Windows 7 64bit this application no longer worked and I got the error messages: Could not load file or assembly 'Microsoft.SqlServer.BatchParser

I found that the solution was to download and install the SQL Server Management Objects Collection for 64bit.
This is available here:
SQLServer2005_XMO_x64.msi

Google Chrome Loading Slowly

Google is my preferred browser, I found it much faster than the alternatives. Recently however it has become very slow, with most pages refusing to load the first time around and having to refresh.

IE and Firefox where OK.

I found the culprit was the latest install of Skype. It installed an add-in in Chrome which put a Skype dial button next to all phone numbers on the page. By going to Chrome\Tools\Extensions I was found the add-in and disabled it. 

Normal service was resumed and Chrome was back to it usual nippy self.

Print What You Like

Printable Versions of Web Pages

Have you ever needed to print out a web page, but over half the page is filled with adverts, banners, buttons and other stuff? Many websites have a print version, but, if they don't this 
is a very useful web site: http://www.printwhatyoulike.com/home/index2


There is also a bookmark link you can add to you button bar, which will allow you to modify any page you visit to make it more printer friendly. You can remove sections, change font sizes and more.


There is also a section of code you can add to your site to make it more printer friendly.
http://www.printwhatyoulike.com/print_button


http://www.kandysolutions.co.uk/Solutions.html

Import GMail Contacts into MS Outlook

To import Gmail contacts into Outlook.

The combination of Outlook 2010 and Gmail seems to be a good one.
Outlook 2010 does a great job of synchronising with my Gmail account. It automatically configures the settings correctly when given login credentials, mirrors the folder structure of gmail. It also gets past the restrictions on outbound mail when I am on someone else's ISP connection which had previously rendered Outlook useless.

Step by step instructions on how to import Google contacts into Outlook

1. Go to Contacts on your Gmail accout
2. Look for the more actions button (its at the footer of the contacts page for me as I write this)
3. Choose Export
4. You can choose to export all contacts or a specific group
5. Also, choose Outlook CSV as the specific type
6. Choose the location on your local system for the csv file and click Export
7. In MS Outlook, go to File\Open on the menu
8. Select import
9. Select Import from another program or file
10. Choose Comma Separated Values (Windows)
11. Browse to the CSV file you created in step 6
12. Select a destination folder - set this to Contacts
13. Click Next and then Finish to complete

Among other things I have done through my company Kandy Solutions is to create custom Outlook add-ins, and to implement other solutions using MS Offices, such as the automation of Word and Excel, creating automatic form letters based on database entries.

Setup a HP LaserJet 1100 on Windows 7 Ultimate 64bit

By default, my elderly but reliable Laserjet 1100 is not listed on the list of printers in Windows 7 Ultimate.
Doing a windows update on this list does add it, but installing using this driver did not get the printer working.

It can be downloaded from this site
http://catalog.update.microsoft.com/v7/site/Search.aspx?q=%22windows%207%20laserjet%201100

Once download - the files need to be extracted.

Then go to printer properties, driver, and when prompted to selected on, click on Have Disk, and browse to the files you have just extracted

The driver pack contains drivers for many different printers, so it is necessary to select the 1100 from the list.

After this my printer appeared to have installed ok, but still did not work. So I rebooted the PC, in line with the ancient moto, "If at first you don't succeed, reboot"

Data Cleansing

Along with my database development work, and creating software, there have been many times when I have been asked by clients to help correct their data. This can include things like identifying bad addresses, missing postcodes, sorting out the way names are stored in the system that sort of thing. It can also involve identifying and resolving duplicates. For residential addresses, finding duplicates is fairly straightforward, but for organisational data it can be much more complex.

When correcting business contact data there can be several layers and a lot of related information to deal with. For example, Organisations change name through re-branding exercises, (e.g. Barclays Bank, became Barclays) flotations etc..  Also, companies may have different forms of their names such as Kandy Solutions Ltd, and Kandy Solutions Limited. They may change addresses, and of course staff can move branch change company and change position within organisations. This is where having a database specialist can be very useful first of all in identifying all these duplications and variations, then in merging the records together.

Why does this matter? Well, how much it matters will depend on your business, but if you are mailing brochures or other mass mailings you will be wasting a lot of money mailing to the same person multiple times, posting to addresses that don't exist, so the mail never arrives.

My company, Kandy Solutions have now started to offer a full data cleaning service for customers in the UK. We can take care of everything, from checking that the database does not need additional unique indexes to prevent unnecessary repetition of data through to identifying new addresses for organisations, checking addresses are correctly formatted and match the official Royal Mail Postal Address file, identifying people who have signed up to the various opt-out schemes operating in the UK, looking up business and residential phone numbers for people on the list and many move.

Here is a full list:

Deduplication

Royal Mail's Postal Address File (PAF) Verification

Business Gone Away - find their new address

New Contact Addresses - where people has paid for their mail to be redirected, it can be possible to find their new address too, if they have chosen to let the Royal Mail make that information available

Business Telephone Lookup - we can add business telephone numbers to your data if they are missing

Residential Telephone Lookup - As long as people are not ex-directory, we can add their home phone numbers to your database

Mailing Preference Service (MPS)

Telephone Preference Service (TPS) - Vital if you telemarketing to individuals, you need to know if they are registered with the TPS, because if you call them and they complain you could face a fine.

Corporate Telephone Preference Service (CTPS) - Similarly if a business has registered a telephone number with CTPS and you cold call it, you are committing an offense. We can update you database and identify any numbers you hold that are on this list and help you avoid inadvertently calling people you shouldn't

Analysing Data - Which are your best clients, which leads are old and have never come to anything, and so can be archived rather than wasting money continuing to target them. I can answer these and many more questions by analysing your client database.

Telephone Number Validation

Data Enhancement

Name reformatting

There is much more information about the data cleansing service we offer available on our web site at http://www.kandysolutions.co.uk/Data%20Cleansing.html

Available for Ad-Hoc Development Work

If you need help with any database issues, or are considering having a custom system built, why not get in touch.
You can find my contact details here: Kandy Solutions Ltd

Over the years I have build many database systems, usually using MS SQL Server but also with MS Access.

Sometimes I get called in to troubleshoot an existing system, perhaps deal with a corrupt database. File based systems such as Access, Firefox and Paradox are particularly prone to corruption but often most if not all data can be recovered. SQL Server is much more stable, but can occasionally have serious problems. It may also be a design issue. I have come across clients who no longer have access to the original database developer and have a problem with the system. In one case they could no longer even log on to their SQL Server based system. Using my experience, and some of the tools that accompany SQL Server, such as the Profiler, I was able to diagnose the problem, fix it and get everyone working again within a couple of hours.

Other times I have build major systems for companies. I am able to deal with the whole process, from the initial consultation, through to the design and build, user training, and post installation support.

I try to select the most appropriate solution for a client. For example, for one major international manufacturer, I worked closely with their global IT director, and built a series of integrated Intranet database solutions that linked together their factories around the world. This mean there were no installation requirement on the user's desktops and being web based, was easy to use. It also mean they were operating system independent and have been able to run for a decade now without problems.

I think that more and more companies are looking to make their systems available to users wherever they are. Internet or Intranet solutions that are optimised to run on mobile devises when those are used could well be the way to go. This means the organisation is not tied to one particular devise.

However, for a rich and powerful application, you can't beat a windows desktop system. I have created many of these. In the past I have used Visual Basic and Delphi, these days I use VB.Net. I like to integrate my systems with Microsoft Office, so that users can generate Word and Excel documents automatically for example. It used to be that the problem of deploying a windows application to many users was a problem, and keeping it up to date, since a bespoke system goes through many changes. These days I like to use Microsoft's Click-Once technology when deploying solutions. It means that users are able to receive updates automatically, and that the application does not interfere with any other software on the PC.

JAVAC not recognised as an internal or external command

After installing the Java SE Development Kit 6, I created a java application, and when to the command prompt to compile it, I got this error message: 
"JAVAC not recognised as an internal or external command"


I eventually found JAVAC.exe by browsing to this folder:
C:\Program Files\Java\jdk1.6.0_23\bin


There are two ways to avoid the error message:
1) Supply the full path to JAVA.exe when compiling
2) Add an environment variable to this path.


In windows vista this can be found in Control Panel\System\Advanced System Settings
There is a button here for Environment Variables, as shown in the screen shot:

In the system variables, look for one called path and edit it
In the variable value box go to the end, add a semi-colon and the path to the JAVAC folder, eg ; C:\Program Files\Java\jdk1.6.0_23\bin\


Once compiled and executing the class from the command prompt you may still get errors such as this:

Exception in thread "main" java.lang.NoClassDefFoundError: YourAppName(wrong
name: YourAppName)

1. Two things to bear in mind, when using JAVA to execute the class, do not add the .Class suffix to the class name when executing.
2. Everything is case sensitive. Make sure the name of the class you enter matches the actual name in case as well as spelling

Offline GMail

Gmail is a great email tool, but what happens when you have no internet connection but still need access to your email and attachment history.

Gmail has an offline mode, it is not enabled by default, but it is easy to setup, and means you can keep working, even when you have no internet connection.

To set this up, log in to your Gmail account, go to Settings, and choose Offline from the menu.
Once you have applied it starts to copy your emails and you will see a progress report like this one:

By default "Enable Offline Mode" is not checked. Simple check this and apply. If you don't change any settings all you email and attachments for the last year will be copied to an offline cache. There will be shortcuts created that allow you to access the offline mode even when your internet connection is down. Obviously you cannot send and receive emails in this mode, but you can do most of the other things you would normally do, such a read, search and sort emails.

Janus GridEx - Validating New Record Before Saving or Validating Edited Record

With Janus GridEx there are many events which trap edits. Often I want to validate the record before allowing it to be added, or checking that an edited record is valid.

The UpdatingCell even can be good for this, but what if a users adds a new record, and does not edit a cell, leaving it blank, which violates your system rules. In this case it is best to use the AddingRecord event and get the row and check that


Private Sub GridEX1_AddingRecord(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles GridEX1.AddingRecord


        Dim newRow As Janus.Windows.GridEX1.GridEXRow = GridEX1.GetRow()
        If newRow.Cells("my column name").Value.ToString = "" Then
            GridEX1.CancelCurrentEdit() 'If rule is violated you can reject the row using this method
            Exit Sub
        End If
  
End Sub



    Private Sub GridEx1_UpdatingCell(ByVal sender As Object, ByVal e As Janus.Windows.GridEX.UpdatingCellEventArgs) Handles GridEx1.UpdatingCell


        Try
            If e.Column.Key = "My Column Name" Then
                If e.Value.ToString.Trim = "" Then
                    MessageBox.Show("Holiday name should not be blank", "Holidays", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    e.Cancel = True
                End If
            End If


        Catch ex As Exception
           'Error Handling Here
        End Try


    End Sub

Configuring FTP Server on Windows Server 2008

Windows Server 2008 comes with a new FTP server that can be quite simple to set-up, if you use the Wizard. If you try to do it without the wizard, it can be very frustrating.

To start the FTP new site Wizard go to Start\All Programs\Administrative Tools\Internet Information Services (IIS) Manager

Then right-click on the main server node in the tree on the left, select Add FTP Site and the wizard starts. From there it should be very straight forward.