40 Useful Facts to Know about a System

40

A list of points I should remember to ask when doing ongoing system development, total re-design or external purchase.

For alternative systems rank both existing and new and compare.
Collection of facts is as objective as I could make it – interpretations and consideration as ever is subjective.

I have my own self built project software that records most of these points. The users and user roles is particularly useful my home baked pm software is integrated into my contacts which means if individuals titles and contact details change then the details below are automatically updated.

1. Name of system
2. Version number
3. General Description of the system (written)
4. Date of Creation
5. Name of Creator along with contact details
6. Name of Administrator along with contact details (If different from Creator)
7. Name of the person responsible for backing up the system
8. Backup description
9.Names of existing users – should include administrators and creator
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
10.Names of potential future users
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
11. Operating system runs on (Windows / Linux / Mac / Wine) If Linux please indicate distro
12. Backend Database type (SQL Server, Oracle, MS Access, Filemaker Pro, Omnis, Fox Pro, DB2, MySQL, PostGres, PostGIS, VoltDB, MongoDB, NoSQL)
13. – unc name of database server(s)
14. – unc name of web server(s)
15. – Important directories on particular servers
16. – name of backend database
17. Has the application been designed specifically to allow for geo referencing ( This is not a question as to whether the back end is spatially enabled ) – (Yes / No)
18. Front End technologies – list
19. Main programming languages
20. Is this a client facing system (Yes / No)
21. Could it be a client facing system (Yes / No)
22. Do individuals presently access this from mobile phones (Yes / No)
23. Do individuals presently access this from tablet devices (Yes / No)
24. Is the information available to the general public if they make a freedom of information request. ( Yes / No )
25. Please indicate the information in the system that would not be distributed even if someone requested it. [Description]
26. Is there a statutory requirement to keep this information ( Yes / No)
27. Was the system originally internally or externally created (Internal / External)
28. Capital cost of original construction (if relevant)
29. Estimated annual revenue cost of system ( not interested in wages of individuals only interested in actual costs paid to outside bodies )
30. Can we get a table structure of the system complete with all field names and field types. (Yes / No) – Please attach if yes
31. Is the process unique to the organisation( Yes / No )
32. How often is the design of the system altered ( As and when required, Changes are batched and pushed through periodically, Only in exceptional circumstances, Never)
33. Who can request changes to the system ( Anyone , only management , there is a working group of disparate users who request changes to an outside company , pretty much no one)
34. If that is an outside company makes the changes how often is that done (monthly, yearly, very rarely, never)
35. Are users presently happy with the system ( Yes / No )
36. Are management presently happy with the system ( Yes / No )
37. Any obvious other systems it could be combined with (No – Yes please list)
38. How is performance (Very good, Good, Adequate, Poor, Very Poor)
39. Can you list other organisations that use the same software (No, list of organisations)
40. Lastly where do you consider the application to be strong and where weak and do you have any specific proposals that could significantly improve your interaction with the system. [description]

Posted in Configuration, Database Design, General Opinion | Leave a comment

VBA access code – Passing a selection of e-mail addresses to Outlook

This is similar to the previous post except uses the inbuilt SendObject object to pass the string sBcc directly to Outlook. Outlook picks up the variables and so there is no need to set up public variables.

CODE
Private Sub CommandGroupEmail_Click()
On Error GoTo Err_CommandGroupEmail_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sBcc As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDB = OpenDatabase("\\SERVERNAME\DIRECTORYPATH\" & "TARGET.MDB")
Set rsEmail = MyDB.OpenRecordset("SELECT ... STATEMENT", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(![E-mail]) = False Then
sBcc = sBcc & rsEmail![E-mail] & ";"
sSubject = ""
sMessageBody = ""
End If
.MoveNext
Loop
End With

DoCmd.SendObject , , , , , sBcc , sSubject, sMessageBody, True

Set MyDB = Nothing
Set rsEmail = Nothing

Exit_CommandGroupEmail_Click:
Exit Sub

Err_CommandGroupEmail_Click:
If (Err = 2467) Or (Err = 91) Or (Err = 2483) Then
Resume Next
End If
Resume Exit_CommandGroupEmail_Click

End Sub
/CODE

Posted in Database Design, MS Access, VBA Code MS Access | Leave a comment

VBA access code for pulling together a list of e-mails and passing to a form

Below some standard code that I use to loop through a selection of records and create a string from the individual [Email] s in the below case the e-mails are passed to a form (FORMTOOPEN) – if you are using this then all text in capitals will need to be replaced by application specific information. You want to set up a global variable that is made public which you can pass the combined rsemail (in this case SendBCC) string to between opening up forms.

I tend to have a module called modGlobalVariables and I would put this in

Public SendBcc as string

Remember to be careful with your variable definitions – If you have the same variable dimensioned locally within a command and publically over the whole project values may not appear as expected when you get them.

CODE
Private Sub CommandGroupEmail_Click()
On Error GoTo Err_CommandGroupEmail_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Please note all available e-mails are placed in BCC section of a new form in alphabetical person name order. If a person doesn't have a listed e-mail address he/she will be omitted", , "APPLICATIONNAME"

Dim MyDB As DAO.Database
Dim rsEmail as DAO.Database

Set MyDB = OpenDatabase("\\SERVERNAME\DIRECTORYPATH\" & "TARGET.MDB")
Set rsEmail = MyDB.OpenRecordset("SELECT STATEMENT HERE"

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(![Email]) = False Then
SendBcc = SendBcc & rsEmail![Email] & ";"
MessageSubject = ""
End If
.MoveNext
Loop
End With

stDocName = "FORMTOOPEN"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Set MyDB = Nothing
Set rsEmail = Nothing
SendBcc = ""

Exit_CommandGroupEmail_Click:
Exit Sub

Err_CommandGroupEmail_Click:

If Err.Number = 2501 Then
MsgBox "The e-mail was cancelled without sending", , "APPLICATIONNAME"
Exit Sub

If Err.Number = 3734 Then
MsgBox "There are no Records Cancelling", , "APPLICATIONNAME"
Exit Sub

Else

MsgBox Err.Number

End If

End If

Resume Exit_CommandGroupEmail_Click

End Sub
/CODE

Note on loading of the new form you will need to pass SendBCC into whatever text box you wish to see it in where Me.Bcc is the name of the field receiving the SendBCC string.

CODE
Private Sub Form_Load()

Me.Bcc = SendBcc

End Sub
/CODE

Posted in Database Design, MS Access, VBA Code MS Access | Leave a comment

Connecting to SQL Server – authentication and QGIS

Within QGIS when you set up a connection to a MS SQL Server instance you are presented with two options

* Trusted connection – this is the same thing as using Windows Authentication and authentication is managed by the domain and authorization is handled by SQL Server – This could be handled by an Active Directory Security Group.

* Login – SQL Server can also use its own logins such as a user. These are both authenticated and authorized by SQL Server. They are only viable if SQL Server is configured to run in Mixed Authentication mode.

image001

Posted in Configuration, QGIS General, SQL Server | Leave a comment

The Gunslinger and the Indian

There’s a fight on your computer over a girl called Planned Obsolecence

the-good-the-bad-and-the-ugly-600x254
I get that you need to update software periodically and I definitely want to see software developers paid an appropriate amount but what happens when no one can think of anymore functions to add? With my older applications I definitely change them less and less as time goes on. What then? Can we keep our ten year old software packages which still have the distilled mathematical knowledge from 2 millenia of scientific knowledge 95% of which we have never used?

How will software houses make their money? Temptation is to either by accident or design discourage or deny companies the ability to buy and maintain software outright for local installations rather requiring them to rent from proprietary servers to steady their reducing income streams? (cough splutter splutter Adobe)

I’m sure it will act as a tempting vacancy for the Open Source community, especially on the desktop.

What does the user do?

Go with the native Indian who is less familiar and sometimes rough around the edges but has a far more sustainable and economical way of life or the mercenary gun slinger who you always kind of half suspect has his own agenda and may leave town quickly.

To the horses boys there’s a fight coming.

Posted in General Opinion, Open Source, The future of software | Leave a comment

The Importance of a Booking Confirmation and Careful Communication

rsvp

With booking systems there are a lot of important things that will make a system successful – good operational speed – a well designed table structure – great UI and good stability but without really really great communication and confirmation with the customer you’re missing a golden opportunity.

I would argue that wedding invites represent the absolute gold standard in organising communication between parties in what is essentially a booking contract.

* Firstly you give the responsibility of organisation to a responsible and motivated party who is familiar with the whole process and why they are doing what they are doing.
* Then great care is taken in the timing of when items should be sent out (not too long before not too short)
* Addresses of all parties are carefully and thoroughly checked
* Extreme care is taken in the wording and importantly formating of communications (and often flexible and personal messages included for specific parties)
* Extreme clarity is taken to ensure party’s know who to contact if their circumstances change and RSVPs come back to the persons that sent them out.
* Direct contact is encouraged in situations lacking clarity
The RSVP process is formalised to reduce confusion ensuring that the original party can identify who communications come back from and simplified to require the minimum amount of effort on the part of the guest.
* All the while the process is constantly overseen by senior management who have complete visibilty and problems are tackled when they arise.

A good booking confirmation will act not only as a great piece of promotion for the organisation (you have the attention of the individual concerned they are listening to every word you write) but superb quality and clarity reduces confusion, reduces the requirement for clarifying further work resulting in less errors, increases the likelihood that the individuals will return to do business, probably will encourage them to book more courses and give confidence that the contract will be actually carried out.

To my mind this kind of detail should be born in mind for all system communications.

An important consideration when designing new systems.

Posted in Database Design, General Opinion, User Interface Design | Leave a comment

MS Access SQL for randomising the results from a query

bullish_fractal

I was wanting to randomize the source of a form so that I could test myself on some Russian within a table. Turns out its very simple. Basing a Query on the following SQL will achieve it.

SELECT TableName.PKID, TableName.Field1, TableName.Field2
FROM TableName
ORDER BY rnd(INT(NOW*PKID)-NOW*PKID);

The nice thing about basing a form on this is that every time you open the query it will run a query and give you a complete random list but importantly if you want to navigate through the records in the form it will remember the random order and you can go back and forth in the list and it will be in the order as originally opened.

The interesting thing about this code is that it takes its seed from the time (the function NOW) the next thing I am thinking about doing is making that a definable variable that can be set automatically by the user. As I said I have used the above code to test myself on Russian Vocabulary. I have a dictionary of all the words that I have come across at present. I am given a phrase or word in English and I must type it out in Russian. This was good except when I opened the form it would test me on the table in the same order everytime. Great except I new the first part of the table well and steadily got worse as I went through the table. The table is now so big that I would never sit down and work through the whole table. Randomising the table prior to opening the form solved this but introduced a new problem. Having the same list was useful for building up knowledge of the words it effectively broke the table into a small subset. By having a variable that the user could define they or myself will be able to only move to a random list once I am confident of those words. My thinking is that this will break down what is now quite a large list into smaller parts to learn but I can still use repetition to improve my competency and rate of learning.

Posted in Database Design, MS Access, Programming, SQL MS Access | Leave a comment

Summary and Links for Microsoft MCSE Data Platform Solutions Expert

mcsepicture

Microsoft MCSE Data Platform
Some notes on the syllabus for this – thinking of taking it.

https://www.microsoft.com/learning/en-gb/mcse-sql-data-platform.aspx

Earning an MCSE : Data Platform certification will qualify you for
jobs such as database analyst and database designer

1 : Querying Microsoft SQL Server 2012
Exam 70-461 – MS SQL 2012 credit towards certification MCSA and MCSE

* Create Database Objects (24%)
* Work with data (27%)
* Modify data (24%)
* Troubleshoot and optimise (25%)

See : https://www.microsoft.com/learning/en-gb/exam-70-461.aspx

2 : Administering Microsoft SQL Server 2012 Databases
Exam 70-462 – MS SQL 2012 credit towards certification MCSA and MCSE

* Install and configure (19%)
* Maintain instances and databases (17%)
* Optimise and troubleshoot (14%)
* Manage data (19%)
* Implement security (18%)
* Implement high availability (12%)

See : https://www.microsoft.com/learning/en-gb/exam-70-462.aspx

3 : Implementing a Data Warehouse with Microsoft SQL Server 2012
Exam 70-463 – MS SQL 2012 credit towards certification MCSA and MCSE

* Design and implementation of data warehouse (11%)
* Extract and transform data (23%)
* Load data (27%)
* Configure and deploy SSIS solutions (24%)
* Build data quality solutions (15%)

See : https://www.microsoft.com/learning/en-gb/exam-70-463.aspx

After completing steps 1-2 you’ll earn a Microsoft certified Solutions
Associate (MCSA) SQL Server 2012 certification

4: Developing Microsoft SQL Server 2012 Databases
Exam 70-464 – MS SQL 2012 credit towards certification MCSE

* Implement database objects (31%)
* Implement programming objects (21%)
* Design database objects (24%)
* Optimise and troubleshoot queries (24%)

See : https://www.microsoft.com/learning/en-gb/exam-70-464.aspx

5 : Designing Database Solutions for SQL Server 2012
Exam 70-465 – MS SQL 2012 credit towards certification MCSE

* Design database structure (29%)
* Design databases and database objects (32%)
* Design database security (15%)
* Design a troubleshooting and optimisation solution (24%)

See : https://www.microsoft.com/learning/en-gb/exam-70-465.aspx

Posted in Learning, Personal Development, SQL Server | Leave a comment

Introduction to Basic Printing QGIS 2.2

Creating maps that you can pass on to others is often a central and regular requirement if not in paper format then in a digital format that can be e-mailed or printed out. Here’s a quick reference for myself as much as anything else.

To get into the print composer you can create a completely new print composition or alternatively load an existing print composition – Generally the 5th icon in the main menu bar will take you there (should be a white landscape rectangle with a star will give you access to the print composer , demonstrated below;

The 6th icon can be used to get to an existing declared print composition.

PrintComposerDemonstrationEnvironment

Now in the first instance you are going to want to navigate around the map and ensure that the map you wish to produce has the correct extents. In the below image on a 2 screen image I show QGIS v2.2 open on the left screen and the print composer open in the right. To move the composition area around go to the map window within the main program and navigate accordingly. Then within the print composer window hit the command button titled

Set to map canvas extent

This will re-draw your composition with an interpreted boundary defined directly from you map window. You can enforce scale in the item properties. Similarly after changing layers you will need to ensure that you hit the above button again when you want the composition to reflect the layers within the map window.

PrintComposerMovingtheMap

Posted in Digital Mapping, Geographical Information Systems, GIS, QGIS 2.2, QGIS General | Leave a comment

Introduction to Basic Snapping in QGIS 2.2

QGIS22Digitizing
First set up your project – open the project you are interested in and then hit the settings / snapping options.

Within this window dialog box select the layer you are interested in snapping to and set the snapping distance. For our example it is good to set the distance between 5 and 10 units.

It is also possible within that screen to set the mode of the snapping to something like vertex and segment mutually exclusive or otherwise.

Then hit the icon which is marked as a single crayon and that is you starting to be able to digitise.

Placing the cursor over a vertex or a segment will result in the computer searching the nodes on the segments or vertexes and highlight the nearest one with a pink cross. Making a single left click will attach a new digitised node of the polygon to that node.
Continue as appropriate and when happy with the graphic right click and hit save. Hitting cancel will remove the polygon.

Posted in Digital Mapping, Geographical Information Systems, GIS, QGIS 2.2, QGIS General | Leave a comment

Internal Development Good or Bad

20_Type9Target
Elite Dangerous from Frontier Developments an example of a game built on internally developed game engine – and they are seriously kicking ass as a result.

It is tempting having been burnt with IT projects to say right that’s it I don’t trust consultants anymore I’m going to try and do everything by myself we cannot trust outside companies with our valuable processes – we are after all primarily purely a process company and our processes are golden to us. I must admit I have had periods of my working life where I have been sympathetic to this view. I have found Internally developed systems great because – they motivate internal teams, they increase knowledge of systems design, they can be completely market leading, they can be incredibly flexible and reactive, they really engender responsibility and accountability, they can be very incremental and adaptable and certain individuals can develop systems often using existing IT infrastructure for solely labour costs. (Why employ capable people if you don’t want to use them?)

Against this there are some fairly big black marks which for some are insurmountable.
They tend to be very person dependent with a lot of power resting with certain individuals
Often those individuals are not necessarily chosen by management and often management really don’t like this.
People move on
Most systems will take a year to at least get up and running and sometimes solutions are needed quicker than this.

As a result I would always suggest a mixed strategy of allowing talented individuals to develop those areas for which there are no good products on the market while encouraging buy in of good tools and good products where tools and products do exist. It really should not be an either or and going down solely down either path could lead to problems. It is of course rare to go solely down the all internal route but I am aware of companies only going down the externally produced route.

But be aware even if you are going down the open source and internal development path – be prepared to invest. Buy good IDEs – don’t skimp on database support. Hire consultants (but please give them focused tasks non delivery of results from consultants is often because they’ve been hired without any real idea of what is required of them) Buy products because they look interesting. Financially support open source projects that are actively contributing – not because your liberal with your money but because value is value chances are you can still choose a cheaper path that benefits you and the providers by not leaving yourself open to the kind of consultancy that costs but does not provide. Most of all its your chance to buy in and vote on the future of your software. Open source providers will sit up and notice pay attention and give real weight to your requests.

Be warned though this kind of imagination and vision requires allowing good visibility and control across large parts of the network something that seems to contradict the general trend towards tighter formal security (at least where I work). I would argue however that tighter security often leads to loss of accountability and responsility (a lack of people who can track through all the programs of an issue) resulting in people and especially management being completely blind sided by problems and counter intuitively greater risk of negilgence and greater opportunity for fraud. [Financial Crisis and the Accounting profession anyone?]

I would add that if you really want to be world class you are going to have to take control of your software.

Posted in General Opinion, Productivity, Programming, The future of software | Leave a comment

QGIS – Free GREAT Digital Mapping Software

windglobe A map showing winds over the Atlantic

Looking for a desktop digital mapping package? You really need to check out QGIS it is an absolutely excellent open source geographical information system. At the time of writing the latest version was QGIS 2.4 – the below tips were taken from research into windows version of QGIS 2.2

Full program available here.
Link to www.qgis.org site (English)

Tip : Navigation – Magnification – Plus or Minus mangifier Icons or wheel scroll
Tip : Navigation – Scroll – cursor keys or alternatively the hand icon or hold down the space bar and movement of the mouse when pointer is in the map window.
Tip : Projection – CRS stands for Coordinate Referencing System – lots of different ways of showing what is essentially the surface of a sphere on a flat surface – and more generally referred to as map projection – you will remember from geography. For most UK maps the coordinates are often in Ordnance Survey UK Grid therefore you want the properties of Coordinate Referencing System of the project to be OSGB and you want the coordinate referencing system of the individual layers to be OSGB as well. Once this is done the scaling will be correct and so will the measurement tools.
Tip : View / Panel – allows you to switch on and off menus – very good and very powerful
Tip : Graphical Record selection – Icon in the middle of the toolbar that has a number of differing options – it’s a drop down that allows different things for selection.
Tip : Attribute Record selection – Icon in the middle of the toolbar that allows for table attribute selection. Shows the table and this can be sorted properly.
Tip : Deselect Records – can individually de-select using the keyboard alternatively you can also use the de-select icon in the middle of the top of the screen.
Tip : Browser – brilliant for navigating through the directory and seems a lot quicker than going through the pop up individual menus on the left – for me anyway – additionally you can add an additional browser layer and transfer things between directories. It is an excellent alternative to the file dialogue manager.
Tip : View / Decorations – You can add things like scale bar and copyright to the map window here – very intuitive and nice finishing touch to your projects.
Tip : Labelling – Make scale dependent – highlight the layer you are interested in and right click. Now select the Labels option and within the Size section change the drop down from points to map units.
Tip : Labelling – Threshold the labelling – right click on layer and then go to the Rendering section and select scale based visibilty and adjust accordingly.

Above interpreted from the QGIS manual see:
Link to PDF version of QGIS v2.2 manual

Posted in Digital Mapping, General Opinion, Geographical Information Systems, Open Source, QGIS General | Leave a comment

Scramble the values of individual cells in an Excel Spreadsheet

dice3

Just out of curiosity I went off and had a look for a function that does the same for excel spreadsheets as my previous post did for databases.

Here’s a function that will work.


Public Function Scramble(Optional ByRef UserText As Variant, Optional ByRef Everytime As Variant) As String

On Error GoTo ScrambleError

Dim i As Long
Dim Num As Long
Dim NewPosition As Long
Dim Temp As String

If IsMissing(UserText) Then
Scramble = "No data"

Exit Function
' No quotes automatically generates an error from the worksheet.

ElseIf IsError(UserText) Then
Scramble = "Error - try adding quote marks around your entry."
Exit Function
End If

Application.Volatile (Not IsMissing(Everytime))
If TypeName(UserText) = "Range" Then UserText = UserText(1).Value
Num = Len(UserText)
If Num > 0 Then
For i = 1 To Num
Temp = Mid$(UserText, i, 1)
NewPosition = Int(Num * Rnd + 1)
Mid$(UserText, i, 1) = Mid$(UserText, NewPosition, 1)
Mid$(UserText, NewPosition, 1) = Temp
Next ' i
Scramble = UserText

Else

Scramble = "No data" 'Can result from entering ""
End If
Exit Function

ScrambleError:

Scramble = "Error " & Err.Number

End Function

Posted in Excel, VBA Code MS Excel | Leave a comment

Wanting to demonstrate a database and need to scramble the data?

dice

Here’s a nice function I found that will completely randomize information within fields of a database. Data will not be recoverable from this process which of course is its strength.

Good if you are wanting to demonstrate a database to people that normally contains sensitive information but don’t have time to make up your own records.
Works on text fields and will randomize numbers as further numbers and letters as further letters.


Public Function ScrambleID(parmString) As String
Dim lngLoop As Long
Const cAlpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const cNum As String = "0123456789"
Dim strNewChar As String
Dim strThisChar As String
For lngLoop = 1 To Len(parmString)
strThisChar = Mid$(parmString, lngLoop, 1)
Do
Select Case strThisChar
Case "A" To "Z"
strNewChar = Mid$(cAlpha, Int(Rnd * Len(cAlpha)) + 1, 1)
Case "0" To "9"
strNewChar = Mid$(cNum, Int(Rnd * Len(cNum)) + 1, 1)
End Select
Loop While strNewChar = strThisChar
ScrambleID = ScrambleID & strNewChar
Next
End Function

Posted in Database Design, MS Access, VBA Code MS Access | Leave a comment

Keyser Söze, Code and the World Cup

world-cup-fifa_1401359010

It seems appropriate given the name of this site and 2014 being a world cup year that I might post something on code that I found a while back now related to establishing fixtures in a league coded by, for me, a mythical character. Back then I was taking part in a squash league with some friends and we needed to figure out some way of organising the matches for everyone. Seemed simple enough everyone plays everyone else on consecutive weekends. Turns out its not quite so easy and there’s a whole branch of mathematics called combinatronics that specifically looks at the way of optimising this kind of problem. In that really humble syntax of mathematicians I heard this phrase being banded about – a non trivial problem. As ever I turned to the internet and found some code by someone called Dev Ashish. Now I don’t know about you but prior to the internet I never had access to this kinds of expert and the power of the code really blew me away. The code very neatly creates the required number of matches in a table and allows me to organise matches for individuals to play each other and from there I was able to keep scores on everyone. It was in a word a bit of genius coding.

That was approximately 2005 and come 2014 and my blog the world cup reminded me of this amazing piece of coding.
I have my suspicions of where Dev Ashish is now but I can’t tell for sure.

Keyser Söze / Woland / Dev Ashish – they’re out there…

Joking aside many thanks to Dev Ashish for posting an amazing piece of code…


Option Compare Database
Option Explicit

Public intLeagueno As Integer
Public strLeaguenme As String

Function CalculateFixtures(ByVal Age As Integer, ByVal startdate As Date, ByVal EndDate As Date) As Integer

'**************************************************
' Set Database connections and Recordsets Variables
' Coded by Dev Ashish
'**************************************************
Dim cnn As ADODB.Connection
Dim rstTeams As ADODB.Recordset
Dim rstFixtures As ADODB.Recordset

'****************************************
' Create Integer Variables
'****************************************
Dim NumberofFixtures As Integer ' Number of Fixtures between teams
Dim NumberofMatches As Integer ' Number of Matches to be played
Dim NumberofTeams As Integer ' Number of Teams
Dim Week As Integer ' Week Number for Fixtures

Dim FirstTeam As Integer
Dim LastTeam As Integer

Dim StartPosition As Integer

Dim strtdate As String
Dim intMsgbox As Integer
strtdate = InputBox("Enter the date you want the league to start", "Question?")
If (strtdate = "") Then
intMsgbox = MsgBox("Thanks anyway")
startdate = 3500

Else
startdate = strtdate
intMsgbox = MsgBox("Calculating the fixtures starting" & " " & startdate, vbOKOnly, "Result")

End If

Dim iCounter As Integer

'****************************************
' Create Player String Variables
'****************************************

Dim Player1 As String
Dim Player2 As String

'****************************************
' Create Team/GameSequence Variables based on Number of Teams
'****************************************
Dim Team(50) As String
Dim GameSequence(50) As String
Dim TeamNames(1 To 50) As String

Set cnn = CurrentProject.Connection
Set rstTeams = New ADODB.Recordset
Set rstFixtures = New ADODB.Recordset

'*********************************************************
'Open the Tables Teams and Fixtures
'*********************************************************
rstTeams.Open "SELECT * FROM tblTeams Where leagueno = " & intLeagueno & "", cnn, adOpenKeyset, adLockOptimistic
'Where AgeGroup = 'u" & Age & "'"

rstFixtures.Open "tblFixtures", cnn, adOpenKeyset, adLockOptimistic

'****************************************************
' Read the Team Names into an Array
'****************************************************
iCounter = 1

Do While Not rstTeams.EOF

TeamNames(iCounter) = rstTeams.Fields("Team")
iCounter = iCounter + 1
rstTeams.MoveNext

Loop

'*****************************
'Set Main constants
'*****************************
NumberofTeams = iCounter - 1
NumberofFixtures = NumberofTeams - 1
NumberofMatches = NumberofTeams / 2

'*****************************************************
' Clear the Game Sequence Array
'*****************************************************
For iCounter = 1 To NumberofFixtures
GameSequence(iCounter) = ""
Next iCounter

'*****************************************************
' Clear the Teams Array
'*****************************************************
For iCounter = 1 To NumberofTeams
Team(iCounter) = iCounter
Next iCounter

FirstTeam = 0

'*****************************************************
' Create the Game Sequence ready for the fixtures
'*****************************************************
For Week = 1 To NumberofFixtures
FirstTeam = FirstTeam + 1

For iCounter = FirstTeam To FirstTeam + NumberofFixtures - 1
If iCounter > (NumberofFixtures) Then
LastTeam = iCounter - NumberofFixtures
Else
LastTeam = iCounter
End If
GameSequence(Week) = GameSequence(Week) & " " & Format(Team(LastTeam), "00")
Next iCounter
GameSequence(Week) = Trim(GameSequence(Week)) + " " & Format(Team(NumberofTeams), "00")
Next Week

'***************************************************
'Insert the new fixtures into the Table
'***************************************************
For Week = 1 To NumberofFixtures
StartPosition = 1
'Debug.Print "Week " & Week
For iCounter = 1 To NumberofMatches
Player1 = Mid(GameSequence(Week), StartPosition, 2)
Player2 = Left(Right(GameSequence(Week), (StartPosition) + 1), 2)
StartPosition = StartPosition + 3

rstFixtures.AddNew
rstFixtures.Fields("WeekNo") = Week
'rstFixtures.Fields("HomeTeam") = TeamNames(HomeTeam)
rstFixtures.Fields("Player1") = TeamNames(Player1)
'rstFixtures.Fields("AwayTeam") = TeamNames(AwayTeam)
rstFixtures.Fields("Player2") = TeamNames(Player2)
'rstFixtures.Fields("Age") = Age
rstFixtures.Fields("FixDate") = startdate
rstFixtures.Fields("Leagueno") = intLeagueno
rstFixtures.Update

Next iCounter
startdate = startdate + 7
If startdate > EndDate Then Week = NumberofFixtures + 1
Next Week

'****************************************
'Close the tables
'****************************************
rstTeams.Close
Set rstTeams = Nothing
rstFixtures.Close
Set rstFixtures = Nothing

End Function

Posted in Applied Mathematics, MS Access, VBA Code MS Access | Leave a comment

Complex Event Processing (How Cool)

complexEventProcessing2

As part of my timing software solution I had been interested in seeing if there was a better solution to the constant pulling of information from the timing boxes. It would for instance be far better to have some sort of push mechanism where calculations were only made when new information was received.

Turns out that with the advent of big data (which to my mind is being driven by sensors) it seems to be a bit of a hot topic. Doubtless brought on by the myriad number of devices and sensors which are in the market at the moment. Microsoft have a framework called StreamInSight which is free to those that already have SQL Server 2012 licence (not sure which level)

I still have a lot of questions.
Leading on from the post I did on designing my own timing software I consider that it would be useful as a single time could come in and it could be linked up with its partner times and a lap number could be calculated that would allow the relatively easy display via pivot. A process that could occur as the information came in rather than being pulled and batched which is the way my system does it at the moment.

Not sure how I can get my hands on the framework though… Would love to try it out.

Resources here.

StreamInsight: More than Just an API

General StreamInsight article list

Posted in Problem Solving, Programming, SQL Server, The future of software | Leave a comment

Step through forms and alter properties.

A nice patch of code that will allow you to cycle through a series of forms and make them read only. Useful if you don’t have immediate access to make changes to the backend, SQL Server or active directory. If you have any programmatic save record commands you will have to deprecate those lines.


Public Sub turnOffFormProps()
Dim strForm As String, db As DAO.Database
Dim doc As DAO.Document
Set db = CurrentDb

For Each doc In db.Containers("Forms").Documents
strForm = doc.Name
DoCmd.OpenForm strForm, acDesign
Debug.Print Forms(strForm).Properties("AllowAdditions")
Forms(strForm).Properties("AllowAdditions") = False
Debug.Print Forms(strForm).Properties("AllowDeletions")
Forms(strForm).Properties("AllowDeletions") = False
DoCmd.Close acForm, strForm, acSaveYes
Next doc

Set doc = Nothing
db.Close
Set db = Nothing
End Sub

Posted in MS Access, Programming, VBA Code MS Access | Leave a comment

SQL Saturday is coming to Edinburgh

Pretty lucky really some of the worlds most talented DBAs hosting an event about 1km up from my flat.

More details can be obtained here.

http://www.sqlsaturday.com/281/eventhome.aspx

There is a waiting list but might be something for you to think about for next year.

Posted in Database Design, SQL 08 R2, SQL Server, SSMS, The future of software | Leave a comment

Using VBA to open URL in chrome

Haven’t tried this out but could be useful. I have a digital mapping web application that I link to from a database and it has issues with IE but works perfectly in Chrome…

shell("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url http:google.ca")

Update : 24 June 2014 – just tested this out on a windows 8.1 device and seems to work

%localappdata%\google

Posted in Database Design, MS Access, VBA Code MS Access | Leave a comment

Attaching Databases to SQL Server 08R2 Express

jigsaw2
Easiest way is probably to sign in as SA and then select the Database menu on the left.

Right click and select
Attach…..

Attach database window should appear which will allow you to use the Add… button to navigate to the
\Data\ subdirectory where all the sql server databases are held.

IMPORTANT – prior to loading a file in the database will have needed to have been DETACHED.
This ordinarily is done by going to database in question scrolling down to the database and right clicking on the database

Tasks > – Detach…

WARNING
If a database has not been detached properly it may NOT be possible to re-attach the database this is of course a security feature. So experimenting with simple moving files about will not work…

The listed code below does it at command line but the above works in SSMS

The default location for databases in SQL 08R2 Express is
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Data

Posted in Configuration, Database Design, SQL 08 R2, SQL Server, SSMS | Leave a comment