Summary and Links for Microsoft MCSE Data Platform Solutions Expert


PLEASE NOTE THIS IS AN OLD POST Microsoft has replaced this qualification

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

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%)

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%)

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%)

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%)

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%)

Internal Development Good or Bad

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.

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 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

Scramble the values of individual cells in an Excel Spreadsheet


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


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


    Scramble = "Error " & Err.Number

End Function

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


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 cNum As String = "0123456789"
    Dim strNewChar As String
    Dim strThisChar As String
    For lngLoop = 1 To Len(parmString)
        strThisChar = Mid$(parmString, lngLoop, 1)
            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
End Function

Keyser Söze, Code and the World Cup


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
    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
    '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
                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.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
        Next iCounter
    startdate = startdate + 7
    If startdate > EndDate Then Week = NumberofFixtures + 1
    Next Week

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

End Function

Complex Event Processing (How Cool)


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

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
Set db = Nothing
End Sub

Attaching Databases to SQL Server 08R2 Express

It should be noted that the following although the easiest way to get a new database into an instance it should not be used in a production environment. In fact doing so may get you sacked. If experimenting though this method should be fine. If in doubt seek help as in a production environment you would want to look through all the code before attaching anything into an instance.

Go to SQL server management studio and on the Databases tree
Right click and select

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 and you should always move anything mdf file that you are wanting to put into a database into the data subdirectory.
This ordinarily is done by going to database in question scrolling down to the database and right clicking on the database

Tasks > – Detach…

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

The default location for database in SQL 2012 is
C:\Program Files\Microsoft SQL Server\MSSQL11.[InstanceName\MSSQL\DATA

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")

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


Scope of variables – And Getting Confused

Note to self

If you are wanting to pass parameter values between forms ensure that you place public variables in a module outside of the form.

IMPORTANT – additionally ensure that the same variable names are NOT also listed in the function on the form. If you don’t remove the local variables of the same name. Parameters will appear to be set to the public variables but when you try and call them subsequent to the local scope closure they will be blank.

This must mean that variables are set consecutively and transferred into a memory address. If two variables of the identical name are set the first gets one memory address and the second another. Thus they may appear the same but reference different locations. For clarity be careful with your variable definitions!!!!


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
      !LetterSent = 1
      !LetterSentDate = Date
    End If

    End With

Stopping and Starting SQL Server Instance 08R2 Express


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

Your server should now be running

Allowing sa login and altering password SSMS SQL 08 R2 Express


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

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 WITH PASSWORD = 'DifficultPass9£' ;

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

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


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

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

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
'//     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
    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"
        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 = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

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


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.


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 …

Details of their setup.