Code for altering tables on the fly

Most of the time when you are wanting to enter information automatically in fields as a result of a user interaction it is easiest to use some kind of event trigger from the form. Regularly you want to close down a form and alter a field value in a table on a form which is not presently open.

While the events associated with individual fields and the code therein on forms is generally very good at executing code consecutively making it very predictable, it doesn’t always like you calling another field on another form from a different form often producing an error.

In such cases it is better to alter data entry completely programmatically rather than relying on forms to be loaded before altering fields. The code can still be triggered by an event on a form however.
This code looks to a table of Attendees (T008Attendees) on Courses and selects an individual booking based on its reference booking (I have set this to ParameterID).
The letter sent and letter sent date fields are then updated. As many fields as you want could however be updated. Makes for a very nice user experience.

Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    Set db = CurrentDb()

    strSQL = "SELECT T008Attendees.PKID, T008Attendees.LetterSentDate, T008Attendees.LetterSent FROM T008Attendees WHERE (((T008Attendees.PKID)=" & ParameterID & "));"

    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

    With rst

    If .RecordCount > 0 Then
      .MoveFirst
      .Edit
      !LetterSent = 1
      !LetterSentDate = Date
      .Update
    End If

    End With

Stopping and Starting SQL Server Instance 08R2 Express

startstop

Simply within SSMS right click on the instance and hit STOP and
To start an instance if you are still in SSMS you can simply right click and hit start

Note if you stop a SQL Server instance from within SSMS AND then exit SSMS and try and go back in you will be denied as SSMS is unable to connect to a service that is not up and running.

In such a case to restart the instance you need to go to SQL server configuration manager and click on the instance and restart from there.
In Windows 8.1 you can get to config manager by using the search facility.

Windows 10 Alternative
Hit the search ring
type in services.msc
A new dialog should appea that will have Services(Local) with several columns – Name / Description / Status / StartupType
You want the Status to be Running
Use the mouse to highlight SQL Server (MSSQLServer) and then right click
Start

Your server should now be running

Allowing sa login and altering password SSMS SQL 08 R2 Express

password-security

Recently I installed SQL Server 08 R2 Express.

As part of the standard installation the sa login is disabled as default.

Clearly although this is a useful security feature it is a bit awkward if you are wanting to undertake certain tasks and as part of the installation this security feature is in no way obvious.

So what do you do to re-enable it?

Firstly log into SSMS under the machine windows login.

Go to the Security section of the server (not any of the databases) and expand the Logins branch. SA the system admin should be listed and if it is disabled it will have a small red down arrow next to it.

To re-enable highlight sa and right click

Select properties

Select Status

now alter the following
Permissions to connect to database engine
Set this to GRANT

Login
Set this to Enabled

Now highlight instance itself and right click and go to properties.
Within server properties highlight Security.
And change set server authentication to
SQL Server and Windows Authentication mode.

You should have sa login now enabled – don’t worry if sa still has a small red arrow next to it the SSMS client needs to be refreshed to see changes to set up.

If you want to change the password for the SSMS server I would recommend running the following.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'DifficultPass9£' ;
GO

Note you can enforce the requirement for a strong password within the same security section of the sa login if you require.

Building Timing Software for Athletic Races

A while back I got the opportunity to work with a friend who times athletic races. After a bit of questioning he showed me some of his equipment and how it worked. I realised I could probably put together some home made timing software and so I set about doing it. The following is an overview of the stages that I went through to create the software.

Firstly its important to understand how the hardware works. To my mind this is very much a pattern and irrespective of the software you are using it is going to have to perform the same tasks in the same order.

Pre race test and hook up
IMGP0085

Each competitor is given an RFID chip that has a unique number registered to it. (Numbers can be changed but requires specialist equipment)
A timing box is used to both power the matts (aka antennae) and act as a clock. When someone goes over the matt the chip emits the number and sends to the box. The box then places a time stamp on the chip and places in a file. A file or stream of all these strings is then pushed out of the timing box to awaiting computer.
Computer is listening and takes this information and software does the rest

In terms of the information that is produced by the box this is remakably simple – a raw hex string for each read.

My solution has three main tables
T1 – Raw times
T2 – Competitors
T3 – Chip Tag numbers.

The hard coded chip numbers are hex and each competitor is allocated a chip – they are actually being allocated this hex key but the number equates so on the chip itself will be a readable number eg 14001, 14002 the actual raw code from the box is often something like 45ab32c

What the software then does.

Step 1
Ensure exact duplicates reads are not entered generally because of multiple imports of same file.This is done by setting the full hex key as a primary key disallowing the same value to go into the Raw Times table multiple times.

Step 2
Hex to Decimal translation

Step 3
Drop times before race start time.
Simple select query.

Step 4
Match Reading to competitors – A simple join after hex to decimal translation of information from 3

Step 5
Sort times within competitor in ascending order
Data sort on dual columns

Step 6
Gate times – Competitors get multiple reads over the matt. I have set up something called a gate ( no idea whether this is a standard pattern or not but its what I call it ) Then a gate period is subjectively decided, lets say 30 seconds, (this can be altered for lapped races where you know a racer can’t complete a time in a certain time). A Query is then set up to look to the first time of a competitor and the gate period is added. The software continues looking down the times and deletes any times greater than the first gate time but less than the first gate time plus the gate period.

Step 7
Count timing points
In some races there are laps some are point to point – a lapped race can be thought of as recursive non lap race in that it is a series of non lap races where the end of one non lap race is the start of another. All times for each competitor are sorted in increasing order and an additional field is added with incremented numbers 1,2,3,4 etc. Typically there will be 2 a start and a finish. There can be one where a gun has gone off and competitors are not running across a start matt. In that instance everyone is considered as having the same start time the time of the Starting pistol and start one is given the 2 point and 1 is assumed to be a pre-set time.

Step 8
Pivot the times
Place point number as column header – name of competitor as row heading and time value as the value of the pivot.

Step 9
Calculate the lap times
Simple n+1 time minus n time

Step 10
Add up individual times to get total race time sort by any additional category eg gender age etc… sort time order and allocate prizes appropriately.

Step 11
Pick up whatever pretty report writer you can get your hands on – excel will do at a push and print out and hand to race organisers.

Step 12
Make pretty forms so you can easily change competitors add in extra times for missed times allow for edge cases like DNFs DQs penalties etc.

Sunday 11th of May I had the opportunity to test the software out by acting as lead timer on the Castle of Mey 10k the most northerly 10k in the country. Glad to say worked perfectly.

Below is a video of the 2015 race where we were in the McNicol Van

Nice when a plan comes together.

Attach DSN Less Connection to Link MS Access to SQL Server

link2

This code is generally available all over the tinternet nonetheless I list it here for my own personal use. I use an autoexec macro to trigger the code on open

With a Run Code action to trigger the AttachDSNLessTable…
So the code in the macro might look something like this
AttachDSNLessTable(“Table01Invoices”,”Table01Invoices”,”Server01″,”AccountingDatabase”,””,””)

Alternatively you could run it from the immediate window of the VBA module section.
Ctrl + G to get the immediate window up then create the function with the required parameters placing a question mark in front of the function eg
?AttachDSNLessTable(“Table01Invoices”,”Table01Invoices”,”Server01″,”AccountingDatabase”,””,””)

Pressing return will result in True result and when you go to the tables section Table01Invoices or your table should appear. Note if you have the tables section open of the database window then you will need to refresh.

Honestly works a treat and you can totally revolutionise processes if you are allowed to use the ease of front end design of something like MS Access with the scalability and power of SQL Server.

Needless to say vendors tend to be universally unwilling to give me details of their(/our!) backends.

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName:Name of the table
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Why you and your Organisation should make Web Bespoke part of your operations.

themessage565171

Don’t get me wrong as a platform for communication I consider things like facebook and Linkedin as necessary evils for what is essentially cloud service market places but my preferences are always…

No adverts
No demands to have login prior to viewing site
As much annonymity for visitors as possible
Long form writing!!!!

I guess the thing that irks me the most is the light manipulation that occurs on these sites. They gently nudge you towards their wishes not your own. I must say as an exercise in communication I am much happier with this format where I have greater editorial control. (albeit a bit less traffic)

Its why I really love bespoke ( or as bespoke as possible ) if I could I would be designing my own operating systems.
Maybe one day.

Why?

2 words

Greater Understanding.

Leading to improvements in

1) Flexibility
2) Control
3) Ability to anticipate
4) Adaptibilty
5) Automate tasks ( Given time in certain cases I have been able to completely design out tasks )
6) Improved long term planning
7) Increased reliability
8) Faster response times
9) Reduce requirement for others time ( always something which is a complete premium )
10) Better timing
11) Massive coordination improvements ( yes why ask someone to tell you about something why not give them the ability to edit the information themselves – I can stay at home, kind of)
12) Improved motivation

It allows me to experiment which is when I learn the most and I think leads to the holy grail of increased productivty and reduced cost.

Want to increase the productivity of you and the people around you? Give them as much flexibility as possible and open up your design environment. I can’t see anything but advantages resulting from this both personally and for the organisation.

A good example of an organisation going bespoke with their web design …

http://www.theregister.co.uk

Details of their setup.

http://www.theregister.co.uk/about/company/website/

A Story about User Interface Design

At work we recently moved to a new building which has been adapted several times, some spaces have had multiple uses and multiple security level changes. The present use of rooms is different from the original design. As a result walking down the corridor requires a combination of pressing buttons, waiting for automatically opening doors or simple manual operation.

I can see new people coming down the corridor and people regularly get caught out by at least one door waiting for a manual door to open automatically or alternatively trying to manually open the automatic doors one set of which have heavy hydraulics.

Light relief when I am having my lunch.

UI design

An example application for use learning Русский

IMGP0184

I quite often put together various databases / applications to help me with my side projects. This is a good example. In an effort to help me remember my Russian verbs I put together a small database to assist in learning and remembering verbs.

One table consisting of

PKID
Memory Number
Russian Imperfecive Form
Russian Perfective Form
English Translation
Conjugation Type
Conjugation Class
Reflextion
Memory Aid
Page in Book

Works nicely in conjunction with the code that alters the keyboard mapping when going between cells (follow LINK for details). I am entering the list of verbs based on the popularity of use as listed here.

http://masterrussian.com/vocabulary/common_verbs.htm

And I have field (Page in Book) listed so that I can relate back to my personal reference manual.

“The big silver book of Russian verbs 555 fully conjugated verbs in all Tenses” by Jack Franke – McGraw Hill – 2 nd edition

Here’s an image of the simple form that I put together only took half an hour. I use a left click AZ sorting right click ZA sorting on columns much like applications such as Outlook and ITunes the difference is my sorting tends to be a bit more intelligent in that I will generally sort on multiple columns. EG if I have a sort on a name field first name for example I will make the surname the secondary sort. I personally really dislike the double click as an event I think it is highly imprecise. The memory aid is some tricks I took from a Tony Buzan book. Future development will be ability to print out list and a further child table that will allow recording of related conjugations and yes the example screen shot shows that I still need to input some more information.

RussianVerbs

BANISH DOUBLE CLICKING – My UI Design Patterns

doubleclick

I eluded to it in my last post but when I have tabulated forms I like to have the labels activated and set to sort alphabetically ascending on left click and descending on a right click.

I use the mouse down event as trigger for this.

If Button = acLeftButton Then
    
    Me.OrderBy = "Q001Contacts.CompanyName, Q001Contacts.Surname, Q001Contacts.Firstname"
    Me.OrderByOn = True
    
    Else
    
    Me.OrderBy = "Q001Contacts.CompanyName DESC, Q001Contacts.Surname DESC, Q001Contacts.Firstname DESC"
    Me.OrderByOn = True
    
    End If

I use this pretty much on every single tabulated form that I have – This is not so different from Outlook or Itunes. What is different is that I tend to use a slightly more intelligent sort. In the above code you can probably see that for this particular column (its the company column) I first sort on company then I sort on the surname of the individual and then on the first name. I find it frustrating on things like Itunes or Outlook that you can be left searching around within a sort category sometimes as it is unclear what order within the sort that things are arranged by.

I use the left and right click rather than double because I used to find that double clicking would first sort the list one way and then immediately sort the other. A double click I believe sends mixed messages to the system and encourages lag. I still hate the idea of double clicking on things.

If I could change one thing about standard UI design it would be to get rid of the double click!!!

Needless to say I have banished double clicking from all my UIs.