Monthly Archives: June 2014

Creating linked tables in MS Access using VBA

Recently I ran into a system that used Oracle as back-end database and an MS Access database/app containing the forms as front-end for the users. The Access database connected to Oracle tables and views using ODBC linked tables. We had about 5 different Oracle servers for development, test, acceptance and production. Due to a lot of legacy code the table names in Access and Oracle weren’t always the same.

Frequently we needed to change to which Oracle server a specific instance of the Access app would talk to. Instead of manually removing and relinking the tables, I created a simple local table to define which tables should exist in the Access database and what the tables name on the Oracle server should be. A simple VBA subroutine actually establishes the links.

Here’s a short version of it:

Option Compare Database
Option Explicit
Private Const gDSNTemplate = "ODBC;DRIVER={Oracle in OraHome817};SERVER=${server};UID=${user};PWD=${password};DBQ=${server};DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;L...(cant remember the rest)..."
 
Public Sub LinkTables(Server As String, username As String, password As String)
    Dim dsn As String
    Dim strAccessName As String
    Dim strOracleName As String
    Dim tDef As TableDef
    Dim rs As Recordset

    'Create the DSN for the requested environment
    dsn = gDSNTemplate
    dsn = Replace(dsn, "${server}", Server)
    dsn = Replace(dsn, "${user}", username)
    dsn = Replace(dsn, "${password}", password)

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLinkedTables")
    Do While Not rs.EOF
        strAccessName = rs!AccessName
        strOracleName = rs!OracleName
 
        'Remove the outdated linked table 
        '(ignore the error if doesn't exist)
        On Error Resume Next
        DoCmd.DeleteObject acTable, strAccessName
        On Error GoTo 0
 
        'Create the linked table
        Set tDef = CurrentDb.CreateTableDef(strAccessName, dbAttachSavePWD)
        tDef.Connect = dsn
        tDef.SourceTableName = strOracleName
        CurrentDb.TableDefs.Append tDef
        tDef.RefreshLink
 
        rs.MoveNext
    Loop
End Sub

Automatic conversion of Tables to your C# classes using Specflow.Assist.Dynamic

Using tables in .feature files is good practice for creating readable and maintainable testcases. When you create the step definitions, you’ll frequently find yourself creating code to convert the fields in the Table class to something more useful for your code. Using Specflow.Assist.Dynamic we can have automatic conversion between the Table class and a collection of our own classes. In this post we’ll see how to get this working for a basic example

Install the NuGet package

PM> Install-Package Specflow.Assist.Dynamic
Attempting to resolve dependency 'ImpromptuInterface (≥ 5.6.2)'.
Attempting to resolve dependency 'SpecFlow'.
Installing 'ImpromptuInterface 5.6.2'.
Successfully installed 'ImpromptuInterface 5.6.2'.
Installing 'SpecFlow.Assist.Dynamic 1.0.2'.
Successfully installed 'SpecFlow.Assist.Dynamic 1.0.2'.
Adding 'ImpromptuInterface 5.6.2' to TableConversion.
Successfully added 'ImpromptuInterface 5.6.2' to TableConversion.
Adding 'SpecFlow.Assist.Dynamic 1.0.2' to TableConversion.
Successfully added 'SpecFlow.Assist.Dynamic 1.0.2' to TableConversion.

This has updated our App.config with the following:

<specFlow>
    <!-- For additional details on SpecFlow configuration options see http://go.specflow.org/doc-config -->
  <stepAssemblies>
      <!-- This attribute is required in order to use StepArgument Transformation as described here; 
    https://github.com/marcusoftnet/SpecFlow.Assist.Dynamic/wiki/Step-argument-transformations  -->
      <stepAssembly assembly="SpecFlow.Assist.Dynamic" />
    </stepAssemblies>
  </specFlow>

Create a .feature file with a table in it

Create the following .feature file:

Feature: TableConversion
	In order to avoid lots of code, I want my Tables in the feature file 
	to be automatically converted to an object I can use in the code of my 
	step definitions.

Scenario: TableConversionExample
	Given the following table:
	| id | sometext        | somebool |
	| 1  | Hello World     | True     |
	| 2  | and again hello | False    |
	Then my binding should have the following objects:
	| id | sometext           | somebool |
	| 1  | Hello World        | True     |
	| 2  | and again hello    | False    |

Create the following binding

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Collections.Generic;
using TechTalk.SpecFlow;
using TechTalk.SpecFlow.Assist;

namespace TableConversion
{
    class MyPocoClass
    {
        public int    id       { get; set; }
        public string sometext { get; set; }
        public bool?  somebool { get; set; }
    }

    [Binding]
    public class TableConversionSteps
    {
        private Dictionary<int, MyPocoClass> MyObjects;

        public TableConversionSteps()
        {
            this.MyObjects = new Dictionary<int, MyPocoClass>();

        }

        [Given(@"the following table:")]
        public void GivenTheFollowingTable(Table table)
        {
            var rows = table.CreateSet<MyPocoClass>();
            //Store each row/object in a dictionary using its id as key
            foreach (MyPocoClass poco in rows)
            {
                this.MyObjects.Add(poco.id, poco);
            }
        }

        [Then(@"my binding should have the following objects:")]
        public void ThenMyBindingShouldHaveTheFollowingObjects(Table table)
        {
            var ExpectedObjects = table.CreateSet<MyPocoClass>();

            //Check that each object that should be present, really is present
            foreach (MyPocoClass ExpectedObject in ExpectedObjects)
            {
                MyPocoClass ActualObject = this.MyObjects[ExpectedObject.id];
                if(false == ActualObject.sometext.Equals(ExpectedObject.sometext))
                {
                    Assert.Fail(String.Format(
                        "Expected sometext '{0}', actual text was {1}",
                        ExpectedObject.sometext,
                        ActualObject.sometext));
                }
                
                if(ActualObject.somebool != ExpectedObject.somebool)
                {
                    Assert.Fail(String.Format(
                        "Expected somebool '{0}', actual somebool was {1}",
                        ExpectedObject.somebool,
                        ActualObject.somebool));
                }
            }
        }
    }
}

Run the unit test

When you run this test, you’ll see that it passes. Somethings to remember:

  • The framework maps the name of the fields in the table header to a property in your poco class. If it cant find that property, no errors will be raised

  • Conversion of text in the .feature file to a C# property strips/trims white-space before and after the text in the .feature file. White-space in the middle will remain and be included in your C# property

  • The framework uses the .Net methods to parse the fields. Be careful, the text “1” will not result in a True value in your boolean fields in the poco class

Balsamiq a great tool for sketching your user interface

Often I’ll find myself wanting to show someone an idea for a user interface. A few years ago a colleague introduced to me to Balsamiq and I’ve loved it ever since. Its simple to quickly visualize my ideas and its easy enough that I can sit next to someone and we start moving stuff around until we’re happy with the look. It exports to .pdf, .png and the windows clipboard so its trivial to send the idea through mail or *gasp* print-it-out.

An image showing a basic design for a blog page in Balsamiq

Its easy to customize the contents of the various UI controls in the mock-up:
A image showing how a control in Balsamiq can be customized for each specific need

It has plenty of UI elements included by default and you’ll be well set for anything related to web, application, tablet or phone. There’s also lots of plugins available. Some eye-catchers are integration with Confluence and JIRA. The pricing for stand-alone and for the OnCloud versions can be found on Balsamiq’s pricing page

Here’s a video showing how to integrate Balsamiq with JIRA: