Friday, October 06, 2006

New Blog

Hi Guys and gals,

I've started a new blog, and will slowley but surely merge the two.
You can access the new blog via
www.randomitbits.blogspot.com
Please pay it a visit, that will be where the IT bits and pieces will go from now, reserving this site as more of a personal diary (although I said that I would be duplicating content on the other site, I've just changed my mind).

All the best and have fun,

Ben

Tuesday, September 05, 2006

Getting back into the swing of things

Hi guys,

Again, sorry for being away for so long. I'm going to make a stand now that I will use my blog as and when i have something important to write about, rather than just because I haven't used it in a long time.
Today I'm going to tell you about how to get your outlook calendar items into Microsoft Access, including the fields you want rather than the ones provided when you link. Linking can be annoying because you dont get the basics such as start date / time.
The way I did it uses simple VBA to open an outlook object, open the calendar and put it into a recordset. It then goes through the recordset adding the records you want to look at.
This is great because you can also choose appointments which have certain attributes.

Anyway, enough rambling, here's the code:

Sub getcalendarappts()
DoCmd.SetWarnings (no)

Dim myApp As New Outlook.Application
Dim myNS As NameSpace
Dim myFolder As MAPIFolder
Dim myItems As Items
Dim MyItem As AppointmentItem
Dim intLoopCounter As Integer, intBusy As Integer

Set myNS = myApp.GetNamespace("MAPI")
Set myFolder = myNS.GetDefaultFolder(olFolderCalendar)
Set myItems = myFolder.Items

For intLoopCounter = 2 To myItems.Count
mysubject = myItems(intLoopCounter).Subject
mysubject = Replace(mysubject, "'", "")
mystart = myItems(intLoopCounter).Start
myend = myItems(intLoopCounter).End
myrecur = myItems(intLoopCounter).GetRecurrencePattern
mycreate = myItems(intLoopCounter).CreationTime

With CurrentDb
SQLcode = "Insert Into Outlook_Calendar values('" & mysubject & "',#" & mystart & "#,#" & myend & "#," & myrecur & ",#" & mycreate & "#)"
DoCmd.RunSQL (SQLcode)
End With
Next intLoopCounter

Set myNS = Nothing
Set myFolder = Nothing
Set myItems = Nothing
Set MyItem = Nothing

DoCmd.SetWarnings (yes)

End Sub

OK, so you're probably going Huh?! right now, so I'll walk you through it.
Firstly, it stops any warnings such as "You are about to add 1 row".
Then it declares and creates our main variables and objects. This is the stage where we actually drill down into the calendar. The first variable is an outlook object - this allows you to access the contents of outlook. It then creates a namespace (folder) reference, declares the folders we want to look at as MAPI folders, and creates variables for the actual appointment items, and a couple of integers so that we can count through.

It then sets the namespace to the MAPI folders of outlook, sets that to the actual calendar folder, and sets the items to refer to the items within the calendar.

Now we're onto the actual data harvesting.
For every record from #2 through to the end of the table (#1 is the titles which you dont need unless you're doing a CSV - which I'll talk about later with regards to using the stream writers), assign each of the values you want to see to a variable.
You can view the available values by looking at AppointmentItem in the object browser.
You'll notice I did some replacing with the subject - this simply removes the ' s from the subject line as they can mess around with the SQL code. Which brings me nicely onto the SQL code! This bit will vary depending on what your table looks like, but the basic syntax is "Insert Into ('') Values('');" I haven't included the fieldnames simply because I'm going to use all of them, in this case access simply works through them in order. You refer to the variables by using " & variable & " as you will probably have done whilst working with strings.

It then runs the SQLcode (hopefully adding the calendar item to the table).
and moves onto the next item in the outlook calendar.
The last bit is simply clearing up - setting the variables to nothing, and turning the warnings back on (otherwise they wont work until you next open access).


I hope that helped, as I had to trawl the net for hours when I first needed to do it.

All the best,

Ben

Tuesday, August 08, 2006

Getting started again

Well, having gotten over the initial excitement of getting back into my blog, its time to start on with what's happening with my head, and my developments.

It's come to that point again, when God is shouting ideas into my head on what I can do with my brain. Network this, Automate that, Develop this, Design that.
Wow it gets hot in that there head of mine.

I'm currently thinking about the possibilities of wireless networks / VPN connections between churches.
I'm also designing a bills system which will keep records for me, and allow Laura to work in her usual way - hitting problems with opening and closing access databases from excel.
On the horizon is a new server to store our films and photos, and a network to interconnect everything.
Also just added to my list is a challenge - a dead iPod to be repaired - hard drive failure? how difficult is it working again?

I'm Back!

WOW! Sorry I've been away for so long! I lost my password, and cant find for the life of me why my email account was blocking blogger.com related emails!
I added it to my safe list, checked the firewall, checked the anti-virus, nothing.
In the end, with the kind help of a guy called Karl from Blogger.com's technical team, I changed my registered email address to my work address, I then couldnt get on from work, but I could generate the forgotten password emails!!
I created a webmail account, and the forwarded the email to it, where I then logged on at the cybercafe at work, checked my webmail, and sure enough - I was back in :D

So this goes out as a big thank you to Karl for getting me blogging again. - and if his manager happens to read this, Karl should get a big pay rise just for being nice.
Cheers Karl from the Blogger Team.

Have fun

Tuesday, June 13, 2006

New Son!!

Guys!! I have a new son!!

Evan David Moorhouse was born at 3:05 am on 13th June 2006, weighing a very healthy 8lbs and 8oz.

He has my build, and a lovely little birthmark on his left calf.
He hardly cries, and sleeps so much... I'm sure that will change very soon!

Now all I need is sleep!

Thursday, June 08, 2006

New Job

Sorry I've been away for so long! I've been working hard on my new job, which seems to be flying by.
I'm now a data development analyst, building databases for 9 RCCs throughout the UK.
I have a slightly frustrating time at the moment, in that although I can speak "Techie" and "Lamans" I have to be able to understand how everything actually works. Not how to do it, or how its been set up, or what it stands for; how it works and what it is.
For example, "Calculate AHT by grabbing a sum of ActTalkTime for each StaffNo from dbo_tblAgCalls, and dividing it by CallsTaken, adding a date criteria of >Date()-2 where 2 is the number of days you want to go back". What?!
Ok, AHT is Average Handling Time, so we know that we're aiming for the average duration of each call a staff member handles (either makes or receives).
To work that out, we need the total amount of time they've spent on calls, and the number of calls they've taken.

The total amount of time is nice and easy, sum([dbo_tblAgCalls]![ActTalkTime]) adds up (sum) all of the talk times (ActTalkTime) from the agent calls table (dbo_tblAgCalls).
eg: call 1=2 mins, call 2=6 mins, call 3=22 mins. 2+6+22=30

The number of calls is also easy - Sum([dbo_tblAgCalls]![CallsTaken]), adds up all of the calls taken (CallsTaken) from the agent calls table (dbo_tblAgCalls).
eg day 1=1 call, day 2=2 calls 1+2=3 calls

We then divide the total talk time by the number of calls to get the average talk time per call - 30 mins / 3 calls = 10 mins per call.

You can also add a criteria (rule) into access, which limits the records it looks at. Under the date information you can ask it to look at dates greater than todays date, minus however many days. (> = greater than, date() = todays date, -2 = take away 2 days).

That's just one figure!! I'll learn it eventually!


Oh, and just in case you were wondering, yes Evan is still in Laura's tummy.

Monday, May 29, 2006

Still in there!

Aww maan - we thought Evan was going to be here! Still firmly settled in Laura's womb.
I need him to come out soon, otherwise I'm going to go insane!

On the development front, I'm awaiting content from Ang Marchant, and am building the E-Commerce auditor. All good fun, although I left the impression of MSAccess on my screen yesterday because it was on for so long!!! It's fading now though.

Sunday, May 28, 2006

Getting Going With Baby Due

I'm finding it so hard to concentrate at the moment! Laura (my wife) is extremely close to giving birth to our son, and that's all I can think about! I keep finding myself staring into space when I should have completed a report or written an article about 3 days ago!

I need to crack on with an auditor database too! aah the list goes on!
Hope you guys are all ok, drop me an email some time? - development@moorhouses.plus.com

Ben