Skip to content

Annual MS Access tasks – and my blood pressure

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

Hi James!
 I’m hoping you can help me rather quickly. I created the calendar for next year and something went wrong. The calendar generated fine. Then I went in to designate holiday weeks. So I chose the following:
12/21 – 12/25 and 12/28 – 1/1
2/15 – 2/19
3/28 – 4/1
 However when I went to book kits the weeks were messed up.  So the week of 12/21 is not there which is correct, but I think the system shifts the week numbers or dates somehow because all the other dates I chose 12/28, 2/15, and 3/28 are still there and the week after each of those is gone (designated as a holiday).  So I went in to fix the 12/28 but I cannot retrieve the week of 1/4. I did not try to fix the others. 
 So in summary, I need the above weeks designated as holiday weeks so the kits do not book on those dates and I need the following weeks back in the system: 1/4-8,  2/22-26, and 4/4-8.
 If you have questions let me know.
My first course of action is to figure out what she is talking about.  Then determine why this is happening.  Then set out to resolve the issue.  To my customer, its just one step “give james problem – james solve problem”.  I often need/require her to be more specific and provide details, lots of them.  She is better now about details, as you can see by her message.
I think the problem she is describing is due to how she completes the task – the steps she takes.  I solved this a year ago by going into the table and actually removing records generated – rather than using the application to do it.  Probably not the best solution, BUT, it works.  I had her regenerate the weeks of the year and then I went into the schoolyearWeeks table and manually removed the 4 weeks.

Once that was done, I got another message from her ”

thanks James. The dates look good BUT… when I book any kit the “return” date isn’t working correctly.  So if I book a kit ex. es115 for 1/4/16 it should automatically have a return date for the given number of weeks we designated it for. Instead the message comes up that “your return date is beyond the given time……”  So it seems the calendar is not catching the number of weeks each kit is allowed. hope that makes sense. Just try to book any kit and it should come up. Try Sodus, primary, bassage and book a kit.
 Thanks as always for all your help!

I went into the application and found where there is a hard coded value in a query.
” vWeeksPastEnd = DateDiff(“ww”, #8/31/2016#, vEndDate)”
Really, a hard coded value?  Well – its a simple fix, change the date.
I found the code by opening the form that generated the error (above), putting the DB into design mode, clicking the select control that generated the error, viewing the properties and going into the on-click event – which brought up the VB code window.  Then I searched for the error and found the code.
  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
End If
Once I had that, I was able to find the variable vWeeksPastEnd , and ……. find the hard coded date.
I should really figure out how to automatically change that date, but that is too much work (i am lazy) – plus I do not have a rule to determine when to change the date to the next year.  When my customer decides to do the bookings.  Rather, I blog it and make a note somewhere about annual maintenance tasks.  So the next time I see this error, I know just what to do and have to do very little thinking – but rather just executing the steps already thought out.  I do not mind doing that, what I do mind is having to solve the problem again.
Moral of the story?
Annual Access maintenance points
May 2016 (the next time I have to do this)
– Remove the *holiday weeks (4 of them) manually from the tblschoolWeeks (12/21,12/28, 2/15, 3/28)
– change the hard coded date in the Form_frmBooking form from 8/31/2016 to 8/31/2017

Practice – practice – practice

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.

Web servers…..remoting in – taking control

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.

Troubleshooting a problem in the web security camera software

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

  1. verify the problem – see it
  2. Determine where you think the problem is coming from (in this case Server or Network)
  3. try some low level troubleshooting – low handing fruit first – least invasive (impacts fewest users)
  4. If problem cannot be corrected in heavy client with a refresh – check the system properties that monitor the server health
  5. If problem persist – remote into the web server and check the Windows service (Genetec Server) – restart it
  6. Restarting the service is less invasive then rebooting the server

Making linked MS SQL tables local in MS Access

My customer has a MS Access DB with about 15 linked tables.  In Access – that means the tables and their data do not really live in the Access DB at all, there is only a reference to the tables that live somewhere else.  The somewhere else in this case is a MS SQL 2008 Server.  The SQL server, that houses these tables is being retired.  It has been in read-only mode for about 6 months – now it is time to take the server off line, forever.

That leaves me in the position of how to handle the data in those DBs on the server.  My customer wants to still be able to access the data for perpetuity.  For up to 10 years, my customer is supposed to be able to provide data from this DB.

The question then is WHERE DO WE PUT THE DATA?  So she can actually still query it?

My initial attempts were to export the data from the SQL DB into spreadsheets.  Not too fancy – but still provides a copy of the data that is searchable.

My second attempt, a better one, was to export the tables from the SQL DBs into a new Access DB.  Upon completion, all the relevant data from the SQL DB was copied into a new Access DB – I had to recreate the relationships – they did not export – just the data.  This was better than the spreadsheets.

Then – my customer said “James why can’t we make the linked tables local in my existing Access DB?  Hmmmm.  As much as I did NOT want to work any more on this project, I had to admit, it seemed like a fine idea.  I had not considered it.  My customer said she had starting doing this a couple years ago but never finished, I thought it was something I could look at for her.

ODBC defined

Linked tables (access to SQL server) explained

I quickly realized in order for me to do anything with the linked tables in the SQL DB, from my computer, I needed to connect to them.  I created an ODBC connection to the SQL server to the first DB and then was able to refresh the link and right click the table and choose Convert to Local Table.  I followed this process for the remaining tables in DB 1 – but was not able to convert the tables in the other DB.  Then it occurred to me that I needed to create a second ODBC connection to the other DB.  I thought the connection was at the server level and would allow me to refresh – convert to local (both DBs).  In reality – the ODBC connection was at the DB level, not the server level.  Once I created the second connection – I was able to convert the other linked tables to local tables.

linked to local

When I was done – the size of the DB changed from about 50 MB to about 400.  A good indicator that the data had in fact become local – or now resided in the Access DB.

In the end, since I had *moved the data from the SQL DB to the Access DB, my customers were very happy because they could still use all the Queries and Reports she has created over the years to look up data in her old system.

Moral of the story?  Be patient and listen.  Good ideas can come from lots of places.  ODBC connections are to a DB, not to a server.

Making sure you get your table relationships set up correctly….then build the data entry form

estec naviation

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

estec naviation

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

estec item details

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!

estec kits out

please keep your head in the clounds…..

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.

schoolworld - form creator

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.

schoolworld - form downloads

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.

schoolworld - form editors

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

schoolworld - main content

See how the Site Content has 6 main options and so has 6 main navigation buttons in the site front end.

schoolworld - main nvigation

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.


Get every new post delivered to your Inbox.