I have a customer who is using a 17 year old MS Access application. I have been supporting it for a couple years now. supporting meaning when something needs to be updated or when somethings does not work and Access gives error messages – I need to figure out why, fix it and move on.
Let me say that again…..
Support = Resolving a problem and moving on.
I am smart when I blog about the things I do, solve, figure out, work through etc. Especially when that thing is a annual thing.
Yesterday, I got a message from my customer that said this
Once that was done, I got another message from her ”
If vWeeksPastEnd <= 0 Then
[vEndID] = DLookup(“[SchoolWeekID]”, “tblSchoolWeeks”, “[Week_Number] = ” & vEndWeek & ” and [SchoolYearID] = ” & [Forms]![frmBooking – District]![vSchoolYearID])
MsgBox “Your Return Date is beyond the last week for kit returns!” & Chr(13) & Chr(10) & “Your Kit Return Date has been shortened.”
[vEndID] = DLookup(“[SchoolWeekID]”, “tblSchoolWeeks”, “[Week_Number] = ” & vEndWeek – vWeeksPastEnd & ” and [SchoolYearID] = ” & [Forms]![frmBooking – District]![vSchoolYearID])
vWeeks = vWeeks – vWeeksPastEnd
Not exciting but sooooo necessary. Practice those schoolworld web site support tasks. Know them, be intimate with them. Help your customers who see it as something much less clear than you. Most of them anyway.
The past couple days, I have uploaded new files to the site – to a page. Doing so allows me to think critically about where the file is? Filecabinet or on a page. I advocate using the file cabinet most of the time. Why? Because you only maintain a single copy of the file and reference it from places on the site. An old concept associated with content management systems…..but, most people using the system do not have that clear understanding.
When you make an update to something, look at it critically and change the way the file uploads are stored and referenced, if applicable. for example. We have a password protected page on one of our sites that has a bunch of files associated with the page. Actually, there are 2 pages associated with the main page – the associated pages have the files attached to them.
the past couple days, I have updated two files on one of the pages. I did not change the name of the file, but did upload it and replace it in the page, not the file cabinet. I considered uploading the updated files to the file cabinet and then copying the reference to them into the pages. BUT, since these are stored to a password protected page – and nowhere else on the site….I left them on the page.
The important thing is the thinking. The rolling around in it, thinking about the best way to support and update content – people. To be ready to be helpful to the people who are championing the site – meaning adding new content.
I try not to speak too technically about the sites and their content – instead, I try to think how the person I am working with is seeing the site. When do they want from it. This helps me to be clear in my thinking, understanding etc.
I also reminded myself that I installed Dreamweaver a couple weeks ago so I could re-build a big ole nasty html page and NOT IN THE SCHOOLWORLD provided editor. Ugh. I found the file I had changed using dreamweaver, updated it and then copied and pasted that code into the HTML version of the page in schoolworld. SO much easier for this task.
Dreamweaver is what I used to call a front end editing program. AKA an HTML – CSS page builder. Very good at helping you build pages with straight html and css.
A dumb title. This is a reflective post for my brain. I have a sense of some things that happened yesterday that are important in my overall support and nurturing of the web security camera system.
the players: a web server running Windows 7, not Windows server. Genetec software. Frontrunners systems.
There is a web server running in our network (which is why Dave D says “I cant even access the server on my network”… the server runs Genetec software. Frontrunner is the network – physical side of things BUT also is trained to work with the Genetec software along with their hardware. I am merely a dude whose organization is using the Genetec – Frontrunner model.
Frontrunner are the people who come in a physically wire the hardware to the network. They work with our networking people and set up new switches and configure / assign IP addresses for each item on the network (these items are mainly cameras and a few hand held Intercoms.
Yesterday, I worked with a frontrunner engineer who knew how to support the Genetec software. He showed me how to assess the health of the web server that is supporting the software. Derrick – frontrunner engineer also explained and created an account for me to access the web server. Now I can remote into it and ensure that things are healthy. etc. I should share this information with our own engineer Dave D – who did make the comment a while back about not being able to access the server on the network. The web server has a Genetec software service that needs to be running, like a SQL server or ColdFusion server has a service that runs on a Windows server. I helped him think though a problem that was occurring in the Genetec software – finding the common denominator – helped us realize the problem was not in our network, but on the server.
In other words – if a camera or a device on the system is non-responsive – then the problem is either
a – in the device (not likely)
b – in the network (ip address Vlan conf) – likely
c – on the server (service did not restart properly after a Windows update) – possible.
the problem is one of those. my job here is to help determine which it is.
Derrick the frontrunner engineer said “its either the network or the server, im guessing network”. I helped him see the common denominator in the problem was the server, so he looked there and saw the issue.
I watched the items (entire partition – and then the individual doors – come back on line) after he reset the Genetec Software service, on the server.
There is even a watchdog service on the server that *watches the critical Genetec Server service and if it is not running, starts it. Wonder if that is working? The service was running yesterday – only the software relying on the service got a little discombobulated….
There is also a Genetec Security center mobile service that provides web access to the system. If I ever see a problem with the web client(s) not connecting to the server – then I could remote in and check this service. Its something, what I call low hanging fruit. I probably end up contacting the vendor (frontrunner) and they resolve the issue – BUT, its nice and smart to vet out as much low hanging fruit as possible.
While on the server browsing around a bit – I can see two vary large drives (1.81 TB) tarabytes – thats trillons of bytes… This is a video server – in a way, its a web server who has the responsibility to keep 30 days worth of 24 hr recording for about 80 cameras. That would be a lot of video storage. One of the two large drives reports only 42 GB available (of 1.8 TB) – this must be where all this video is kept. There is a recovery drive (10 GB) that is virtually empty.
I am guessing that this is genetec’s server – not ours. We provided the network to plug the server into, but the server is part of the entire Genetec – frontrunner service. They put the software on it, set it up etc. They are the main admins. Derrick from Frontrunner said something like “Its ok for customers to have accounts on the server”. this is also why Dave D complains and days “I don’t know $#$# about that server”.
“Frontrunner Network Systems delivers world class IP Physical Security, Access Control, Data and Voice Systems and Services” – from their website. Access control systems
Genetec is a partner of Frontrunner systems. Frontrunner is the big player, will all the infrastructure. Genetec is one of many software’s that partner with Frontrunner.
“Genetec’s engineers had the intuition that the typical analog or digital point-to-point telecom architecture would be short lived. More so, Genetec believed that the direction of the future was the multipoint to multipoint networked architecture. The sophisticated intelligence of software created to manage a complex network offered a major opportunity and would usher in new possibilities to customers worldwide. Thus, Genetec pioneered the first fully IP-based security system. Despite industry scepticism of this new market technology, Genetec’s team developed and perfected the solution.“
Today, I was greeted with a big red email (priority). It said “I cannot unlock the doors using the software”. Not a collosal issue, since the person had a backup plan and could still open the doors from her seat. Actually, this problem existed for each of the entry ways in our configuration. We have 3 doorways, which are represented in the software as literal doors.
First thing I did was report the problem to our helpdesk (for records) then visited the person who reported the problem – verify the problem first. I logged into the software and saw the same issue. Each of the Doors was off line, the software would not communicate with them. I contacted my frontrunner guy – who configures the software to report the issue.
We discussed the issue a little – turned it over – found the common denominator in the problem, which was the server not the network. Is the problem in the network or in the server? A couple times previously, the problem was in the network. A guy on our side did something that caused that part of the network supporting the cameras (ips), to go down.
The problem could also be in an account – permissions, but if cameras or doors or entire partitions are off line – that suggests either a problem in the Network or on the server. Network or Server?
In this case, it was server. Working with my frontrunner contact – we went into the genetec software – as an admin, to the Config tool | System | roles
Turns out the problem was caused by the server (Windows 7) updating automatically and not restarting the Genetec
Once that service was restarted, my genetec session logged out, eventually logged back in and the Doors each came back on line one after the other.
The postmordem analysis steps – takeways
- verify the problem – see it
- Determine where you think the problem is coming from (in this case Server or Network)
- try some low level troubleshooting – low handing fruit first – least invasive (impacts fewest users)
- If problem cannot be corrected in heavy client with a refresh – check the system properties that monitor the server health
- If problem persist – remote into the web server and check the Windows service (Genetec Server) – restart it
- Restarting the service is less invasive then rebooting the server
Staring MS access, a lady who knows enough to be dangerous and a dude, that’s me, who has been here before.
My customer request sounded like this “Hi James, we have some stuff we want to enter for our kits. We have lots of things that go into the kits and want to be able to report on them from the DB.”
After a little more prodding from me, I gleaned this from my customer.
Some of the items that go in the kit could go into multiple kits and some of the items in a kit come from more than one vendor.
My data normalization skills kicked in and I spend many hrs looking at this to ensure I got it right. The kits were already in the DB, but not the detail of the kit items or any information about vendors who supply the kit items.
I ended up creating three new tables in the DB.
New tables and their relationships
The assoc table would be the table to keep track of the details of the kit_item.
After a bit of dabling around with VBA and trying to handle the requirement exclusively in VBA – I punted and allowed the application to do the work that it was designed to do. In dabbling, I did get to learn more about VBA – breakpoints – watches and how to drill down into a data structure to view a value. BUT – it was way to much *extra work to do it this way. WHEN access has lots of native features to handle things like editing and saving data to multiple tables on an edit.
The primary tool I used to *build an entry form for a table and its related data was a form – subform solution. The kit_itemid would be the linking field between my main form (The item) and its child or linked sub-form, the assoc_kit_kititem. This classic entry form allows you to enter a parent record, like a kit, while detailing lots of records about that kit, like how many items did it contain, how many vendors etc.
Setting up the form was not trivial – as the subform can be a little tricky. But, patience and attention to detail and Stackoverflow and even the MS help, specifically on the subform edits and properties, was enough to help me build this form.
Main item data entry form with sub-form
The outer navigation bar is for the primary record, while the inner sub form shows details about the selected item. You would use the outer record nav while entering new items and the inner nav while adding details about the item. I ended up adding drop down controls to show existing kits that the item would belong to and for the vendors.
I also added a very simple data entry form for the item vendor. Once I had tables understood and created and the entry forms working – I added a little navigation from the home form.
Navigation menu for the kit items
I added a new button to the main form, which opens the sub form that you see on top of the main menu. I also added a view of the kit items being entered, not a report, but a form technically, with a listbox on it that displays a query result set.
Display Form – using listbox to show query result
and finally, I added a form that shows the total number of times the kits and their items were booked each year.
Display Form – using listbox to show query result – again!
What I mean is make sure you understand at a high conceptual level how things are done in this software. Think of yourself, in the support role, of the person who has to explain things before they are solved. Not always, but usually, I cant solve something unless I have groked it – at a high conceptual level. The following is to that end.
I have a request today about a survey form on one of the site pages. In exploring the question, I confirmed – asserted these high level principles regarding schoolWorld based web site (pages) content etc.
If a form shows up on the web site soliciting input from you – then the form was created first using the form module – accessible to certain people – depending on their rights.
This form is created and can be editing at any time by the person who has permission to do so. The form can then be linked to from anywhere in the site. A link to it in the middle of a paragraph or from the bottom of a page. People click the link – fill out the form. The results of the form are also avail. to the person with permissions. they can view all the user submissions and even report the data out from the *form (underlying DB) – using options avail to them.
The administration of the form has options like – a password or a range where the form would be avail. as well as places where you can edit the content – text on the page around the form itself. As well as options to email – push email to someone who may be interested in a submission of the form. Of course you can also edit the questions on the form as well as remove and add new – all though that starts to mess with the integrity of the data.
The schoolworld site has a concept of a main navigation – this is true in all of the templates available via schoolworld developers. That concept of navigation is represented by
See how the Site Content has 6 main options and so has 6 main navigation buttons in the site front end.
Main navigation options have lots of sub-options – which are individual pages that are added to the site.
I can always find a page on the site by its parent most main option.
Site paragraphs have a concept of “Paragraphs on Pages”. The pages that are avail. seem to be lacking a cogent structure, but this is how you find and edit the content “paragraphs on the pages”.
Additionally, we can create a link to a video – which is not, in this case, uploaded to the schoolworld server. Most often, we should simply link to a video that is uploaded to a video server. We have our own video server that we use organizationally. Lots of different software and systems use video or have video that lives on our dedicated video server and is consumed from there.