Skip to content

Old Access reports – and how to tweak them

This was the email from my customer.  ”

Hi James!

I’m hoping you can help me with the system.  I just printed out the District final request and there is an odd problem.


  1. Go into the system, go to reports, requests, district final requests, and look at …. Gananda for example.  You will see it starts with AV100 and then CC32……etc…. now scroll to the 7th  page and you should see towards the bottom CC31, 34, 42, and 43. I must have put them in wrong or something, can you make them go in order with the other CC kits on page 1?
  2. Also if you look at the 7th page again, you’ll see a section break where it looks like the school is supposed to add the amount of the SM kits, EM kits, AS kits, CC kits, etc.. However earlier in the document there is nothing for the ES kits or the SC kits. Can you put a break in there or if anything there could be a break at the bottom of each page with a “sub-total”. 
  3. And… if you look at the section break is says 518.000  SM s  I don’t know what the “s” is I think  it used to say subtotal or something.  Can you check that too?
  4. One more, can you put page numbers on this on the bottom right??”

Oye.  Half the battle is almost always understanding the request…

This is reasonable, but for me, difficult to solve.  I usually want to be a technician and not a demolition expert.  Translated meaning I want to solve the issue – help my customer, with as little change to the application as possible.  This is true because the application is a 17 year old MS Access application that does the job.  If there is new stuff asked for, that is one thing, but when I am asked to fix something, that usually means be a technician.

The report in question actually uses a table as the source, not a query.  That is the first thing that seems odd to me.  I am used to seeing a query as the source to a report, not a table.  There is functionality in the app. that when executed ends up deleting the table in question and then rebuilding and populating it.  Fine, its logical, but still odd.  My customer then, one a year, creates new order by entering it and then executing the routine responsible for building and populating the new table.  The table is populated with about 10,000 records because it rights every district and whether or not it is using a kit for the new year.  In reality, this report should have a source of a query, which would only pull about 100 records per district, instead of 1000.  But, I digress.

So, this is the interesting thing in the report.  The grouping and sorting area at the bottom.  Again, sorting and grouping and filtering are things that typically happen in a query.  The query is the laizon between table data and reports, but not this time.

This is the group – sort – total area of the report, where these rules that are usually defined in a query are in the report.


Getting the right order, especially in the Sort and Grou on Kit_number was the real tricky part for me.  After looking at this issue a couple times, I was able to understand what was being done here and be a technician.  Grouping first by districtID, then sorting by kit_number and then group again on kit_number – using only the first 2 characters of each kit number.  Seems trivial now, but it frustrated me for longer than I want to admit.

A snapshot of the report, shows the output and why it does make sense this way.


and then the total section when the kit number changes from ES

kit break

So records are grouped by district and then sorted by kit number first (so they are all together) and then grouping on kit number, the first two letters of, and then totaling for each kit type, like “ES”.

Once it worked, it made sense, but not a minute before.


A brief thought on DNS registration

The other day, three of our websites went bye bye.  Their DNS names were not renewed.  After contacting our customer, the DNS were renewed and the sites were back on line – content in tact.  Took a few hrs to propagate, but overall a nice quick response by our vendor, who is the owner of the DNS names.  We are the owners of the other 7, soon to be 8 DNS.  Meaning we, Jeff G. manages that information for us.

Jeff did mention that when he registers a DNS name, he has to provide a lot of info.  specifically a technical, billing and administrative contact(s).

DNS can be registered for up to 10 years, but that seems too long for most people I would think, since they do not know how viable the site could be in 5 years.  I read the typical DNS period is 1-3 years.

these are DNS names who’s server I either support or manage.

moodle (*5), sw/bb (*8).  3 additional sw/bb servers that I support/manage whose DNSs are owned by sw/bb

We had another site go down for maintenance the other day, which spawned a reaction “same as last time?” from my boss – it was not, a coincidence they say.  This site was down for maintenance and we do own the DNS rights to it., according to Jeff G.  Regarding that domain name, Jeff said “originally created in 2001, was renewed in 2015 for 5 years.

Maybe they, being us, since we own it, got a discount, an offer we could not refuse, if we renewed for 5 years.

Another interesting message from April of this year (2015).  Only interesting since it involves the registration of domain names.  It once was abstract but not its found!


The domain: is available for purchase.  We recommend that you purchase the domain name moving forward.  You can use any domain registrar such as GoDaddy, Directnic, or Network Solutions, just to name a few.  Once the site has been purchased and setup we will provide you with DNS information to use to point the domain name to your SchoolWorld website.  Let me know if you have any questions.


Well.  We live, we learn. Jeff G has a list of all the domains that we manage. For each registration that we own, we have to supply to the registrar the technical, billing and administrative contacts.  I am not sure who Jeff uses for the registration (GoDaddy? Directnic?)

My questions “Jeff, how many domain names do we own?  are they all registered with the same registrar?, how long do we typically renew a DNS? 1,3,5 years?” 

I do not have to ask when they expire, I heard Jeff saying the other day they got notifications about things expiring all the time.  everyday.  Software, DNSs, certificates, etc.

A final interesting two thoughts…

1 – registrars, who collect lots of data when registering the domain, do typically offer a slightly more expensive option if you want additional privacy.

2 -I copied this text from this site.

No, you do not own the domain name. You are leasing the right to use the domain name for a specific period of time. At the end of that time you can choose to renew the domain or let it expire. An expired domain can be registered by another party and used for any purpose they see fit.

Due to the fact that domains are not owned property they can be disputed in certain situations. The ICANN agency regulates all domain registration and usage on the internet.



The day our schoolworld – blackboard sites were gone…

this is a little stressful.  Walking into work and having your boss call you into her office and say “James, get in touch with so and so and find out why this web site is no longer registered…” .  In the meantime, users of that website have started calling and emailing the helpdesk with inquires about their web pages.  A couple teachers who have *teacher pages on the website contacted us first. A couple examples of rattled customers

Good morning – I got Brian’s email over xmas and attempted to access FLTCC’s site this morning. A prompt does appear stating that the domain has expired for the entire site, not just Brian’s teacher page. Any help would appreciated,


Hi Andy,
I was just checking my webpage, as my juniors are working on a project and I had provided them with resources on the page. I am a little alarmed because I receive a message that the domain has expired. I also attempted to log into the administrator section and receive the same message. This is the link to my class website
Currently my stomach is turning because I have 4 years worth of development put into this webpage and it is utilized daily. I don’t know if someone forgot to pay the electric bill or what but if this can not be recovered it will be a major issue.  any advisement or guidance would be most highly appreciated. Hope you guys are enjoying a merry Christmas. Thanks! B

We quickly realized that the entire site was gone, not just specific teacher pages.  After a little more digging, we discovered that two additional websites are gone.   Count them, 3, 3 websites there one day, gone the next.  Fortunately, it is vacation week for out school districts, so not too many people wanting the websites.  But, enough to contact us and create stress.  I have the following people emailing, calling or visiting me in my office yesterday.

teacher 1, 2 and 3 about their teacher pages

principle and director of the school whose site was gone

director of our group (my boss)

Not much fun.  What happened was the vendor, SchoolWorld had ownership of the 3 domain names and let them expire.  If we had ownership of the DNS, we would have been notified, many times, that the domain name was going to expire.  We did not own it, we did not get notified.

I contacted SchoolWorld shortly after finding out what happened, to their credit, they acted quickly and had the sites re-DNSed in a couple hrs.  My contact Javon, forwarded the info. to the engineering team, who sprung to action and got those DNSs re-registered.


That is correct.  The content will still be in place on our servers  We have renewed all three.  We are waiting for the DNS to propagate.  DNS propagation can take anywhere from a few hours up to 72 hours.  So far one of the domains is working again:  The other two shouldn’t be much longer.

these are the three sites


There was really nothing more for me to do but wait.  Once the sites were back up, I notified all the people who had notified me about the problem.  Good customer service.

My final takeaway is that two of these sites have teachers that use the teacher sites feature of the schoolworld/blackboard site.  That is a good thing.  I conceptualized a comparison to our moodle sites, where teachers also have courses, not sites, but their similarities are many.

Opps – I blew it up

And did not know it.  That is not good.  I was messing around with setting permissions on a new user group in our web security software – since i am admin, and I somehow removed the people and privileges of the group.

In the software, Genetec, there are users and user groups and permissions within.  I arrived at work today with priority 1 email and a visit from my colleague, whom the helpdesk was contacting about a resolution.  Yesterday, I was in the software creating a new group and adding a new account to it.  I did not want to give the account admin. privilege, but needed the account to see all the cameras in the 3 areas, buildings.  I used the copy configuration tool, or I tried to, I copied from an existing user group its settings to the, a, new group that I created.  Like this, create the new group, copy the privileges from that group to this new one.  That is what I was doing with the copy configuration tool option.  Only SOMEHOW, I reset the privilege and membership of the RSC Entrances group.  The group had nothing but a name, no member accounts, no areas defined or privileges granted.

It took us about 15 minutes to *restore accounts and permissions to the user group, which corrected the problem being reported by all 3 of our employees who monitor the building entrance ways.

The software reported to them, when they authenticated, that they did not have permission to see anything.  The reason was because their account had been removed from the user group.  We added that back first.


There they are, the 3 people and their sub accounts, added back into as Children of the RSC Entrances group.  that gave them some privileges back.

Next we added the group as an accepted user of…


And finally, we added permissions back for the members of the group, to operate the doors – allowing them to interact with the software and open the doors.


At the end of the day, good lessons learned for me?

  1. be more careful when creating new groups and trying to limit privileges
  2. Security model is group focused.  Members (accounts) are added to groups and permissions are added to the group.  The group has the permissions to see this an that, not the individual account.

Then end

Security cameras – IPs – configuration

We have a new client called rapid response, who are building an interface that will link directly into our security cameras at our 3 locations.  We have had the security cameras in place about 2 years.  Most of it has been updated or is new in the past 2 years.  We have 3 centers, Main campus here in Newark, a tech center in Williamson, NY and a tech center in Flint, NY.  Between the 3 centers there are about 75 cameras and 4 *doors.  They consist of at least two servers, both of which I am lobbing for remoting credentials.  I already have one, but discovered a second this past week when we had a problem that I diagnosed for our vendor, which ended up on the server in Williamson.

Our new vendor, Rapid response has been asking for IP addresses to the cameras on the system.  Makes sense.  I have provided that too them by printing off an *area view in the software that lists the names of the cameras in each location.  Then using the Configuration software provided by Genetec, was able to gleen the IP address for each camera and write that on the diagram.  Then I scanner the diagram and emailed it to myself and then to my contact at Rapid Response.

I want to help both the Rapid Reponse folks, in creating the new interface that will link to the cameras but I also want to access and understand the server environment better.  The later so when a problem occurs in the future with the security cameras (usually a problem means a camera or a door entity in the system is down.  By having remote permissions to the server, I can do two very valuable things if problems occur.

1 – restart services on the server

2 – restart the server itself.

I will naturally still let my vendor, Frontrunner know what I am planning to do, but it could help in two ways

1 – quicker resolution to a problem

2 – negate the need for the vendor to come to one of our sites.

I find myself most often troubleshooting, and communicating the findings, when a problem occurs.  I would still do this, but be able to reset services and the server when something is down.


That is the image(s) I used to communicate camera names and IP addresses to Rapid Response folks.

The day our door(s) went down

The doors are entities on a network running on Genetec software.   They have a special provision in the software – they are able to unlock the doors, 4 of them between 3 buildings.  People monitor and let people into the building from.  You could call this, building entrance way security software (Genetec).  The doors along with about 25 cameras on the main campus are all part of the network via IP based architecture (I really should learn more about network architecture, like switches, ports, servers, ips domains in a Windows environment.  Anyway, I digress.

One of the door monitors reported the problem on a Monday morning (when else, right?).  The issue reported to our Helpdesk, who then have a inquiry / action diagram that i put together to help them know how to direct/filter the issue.


This is a priority 1 level issue, according to our Helpdesk staffers…even though the doors can still be monitored and opened via the old technology.  But, I digress again.

I logged into the thick Genetec client program to look at the doors entities to see what the issue was.  I was able to quickly find some useful information to share with the vendor, Frontrunner.  The Access Manager was down.  This is a service, I think, running on a server at another location.  Not sure why the service for the doors at main campus are on a server at a remote location, but, I digress yet again.

I was able to coordinate with someone at the remote location as the technician from the vendor had not been to this location.   They meet up there and reset a service probably is all.  Derrick said to me that it was likely a Windows update that restarted the server, which caused on of the services to not come back on line.   Stupid Windows.  We discussed ensuring that Windows updates are not automatically pushed onto the server.  We would prefer a more managed pull solution, where we could get the update and monitor it to ensure that all our necessary services on the server restart.

I am requesting remote access to this server too.  I have access to a server here at the main location, but not to the one at remote location.  I want access so I can restart services or the server itself, if necessary.  If a problem occurs in the system (usually something, a camera or door, has gone offline) – these are good steps that I could perform in the interest of not having to have the vendor come on site to do something, like restart a service.  I call this low hanging fruit.  Let me try it, see if we can resolve it, then contact the vendor.

Genetec software information.

Finally, another co-worker vested on the network side of things said to me “Can we have the server at williamson moved into a better closet?”.  When the server was initially set up, our network engineer was not there and so the server ended up in the wrong closet – wrong in terms of no climate control.  It was moved and everything is up and running.

It is two things I am asking the vendor for

1 – remote access to both servers

2 – with permission to restart services and the server

Legacy MS ACCESS application

I support an old MS access application.  It has lots of old code in it, that I did not write.  I understand this application because I understand DBs, SQL and VBA.  Each to a decent level.  I have been supporting this app. about 3 years.  Its probably about now that I start seeing things in the application differently.  For example, for the past couple years, my customer asks for things to change or update in areas of the application, and i change them.  I deal with the single request to do something in a single place.  The other day came another request to fix the application so the current year data shows up in the form.

this is a common request.  “James, in place so and so, the wrong data is showing”.  I go in and change a hard coded value in a SQL query.  Each year, a new year is generated in the application and then its id has to be updated in numerous places.  Each year I do this.  Now I see this as a single task.  When the next new year is created by my customer in the application, I should go through the app.  and update the code, at once.  I should not make my customer report to me that this form or that report are not showing the correct data.  I should solve this globally, at least try.  I could actually do a couple things to solve this

1 – I could do a global search for the variable name and update in each place.

2 – I could refactor the SQL so that after a certain date, it use the next available number (since this is how the values increment each year.

3 – I could create a function that changes the value in one place and call it from all over the application.

#3 is the best solution – put the code in a single place, update it, call it, but also the most work.  2 is decent, I could figure out how to simply use a variable in the SQL and define it in a more global space in the form, for example.  #1 is still a big improvement and the least intrusive, for the old girl.

“James., can you ….”

Honestly, this is my lens “4 years left of supporting this application, at maximun”.  It almost got the book last year.  software has a limited lifetime, a season, a day when it is no longer viable.  something else comes along and does it better or as part of a larger system.  In this case, another BOCES who handles a similar function absorbed it.”

More to follow


Get every new post delivered to your Inbox.