Skip to content

MS access support – glad i made a copy of the DB…aka The Always Rule

One of those things you learn along the way…. Especially if you support dbs or code of any kind.  The “always rule“.

What is it? Some people would know.  Always back up the file, code, graphic or db or whatever it is you are changing.  My access customer says things to me periodically like “james, help” or “I was wondering” – depending on if she wants something fixed or added.

I was working on a simplish form, report and query update – when boom, seemingly right in the middle of my “experimentation” – that is how we solve problems, Access starting reporting file not found.  I tried about 15 minutes to figure out what was going on, what did I do to cause this?  At that point I realized I had made a copy of the DB before I started playing with it.

In 3 minutes, I was back in business – solving the problem I started out to do.

I simply renamed the crashing version of the DB name_crashing and renamed my copy to original name

The problem required me to make a change to a report – and the query that feed the report.  It took me a little while to figure why the records in the report were being ordered the way they were.  I naturally looked at the query (SQL) first.  After a little experimentation – there is that word again – I asserted the query was not in charge of the ordering of the recs in the report.  After looking a little harder at the report, I toggled the report design tool group – sort in the toolbar and saw the selections in the report.


That is what was controlling the ordering.  Interesting also.


  • Back up your work always.
  • Do not work on the live version, ever.
  • If something breaks, try to fix it, but don’t be afraid to punt the issue to the curb in favor of using your backup.

Annual MS Access data updating…..

I have this old Access application that I support.  My customers have been using it for many years.  Yet, they seem to not use it the way it was designed.  Sound familiar?  She contacted me this week and said “some of the bookings that I did for this year are showing the old cost.”  What she did not say is “I must be doing something out of sequence or I think I did not select this option when I created the bookings“.  Its always the system that is the fail point.  Sound familiar?

The reality is the problem almost always is not in the application, but rather in the person executing the application.  To say another way, the problem is usually between the chair and the keyboard.

This is not a judgement on my customer.  Trust me, I have done the same thing in other scenarios.  I just wish she would take a little ownership of the mistakes or process misunderstandings that she has.  Also, to be forthcoming with what I am working on.  To my point for next year.

What to do when the kit booking cost need updating in year 16-17.  Take these steps

  • Open the kits table – ensure the cost for the kits is up to date
  • open the bookings table to verify that bookings for the school year are in there – should be schoolyearid 19
  • create a new query and use an update statement to update the incorrect booking costs
  • Pull up the the kits table, hiding the columns not needed – showing just the id and the cost
  • Next to the kits table – position the query window – so its easy to see both and scroll down the kits table
  • This past year, I also printed out the appropriate kit booking records – so i could see which kits i actually needed to run the update on.

estec booking updates

A simple little SQL statement to update the records

UPDATE tablename

SET fieldname = xx

WHERE kitid = xx AND schoolyearid = xx

*please don’t forget the WHERE clause when doing updates like this…..;)

Supporting NYS test scoring….staring ASAP

This is a big deal for us.  I work for a RIC (regional information center) in western NY.  Its a BOCES, if you know what that is.  One of the big services that we offer is the processing of physical test scoring sheets.  We have a customer developed piece of software, developed using ASP, by my colleague Gary and supported by my other colleague Mike.

If you know NYS and the emphasis on test scoring – then you know this is a big deal.  These are a few high points from a conversation the other day with Mike.

ASAP is our custom developed software (Gary and now Mike support it) – written in ASP (microsoft)

The software processes test scoring scans and creates extracts of data.  Digital results, of course.

There are many school districts in the state that use our service.  Not just our own districts (WFLBOCES has 26 districts it supports).  Downstate districts also use our service.  They send us their test scan sheets and we scan then, process the data and send it back to them.  We do not keep it.  Its is their data.  The district then moves the data into what NYS called the level 0 warehouse.  this is where all the district test data goes.

Someone at the district then looks critically at the data extract and verifies its accuracy.  Often times, there are discrepencies that must be resolved.  Trust me, NYS is VERY finicky.  When this happens, the district sends us a copy of the extracted data (we NEVER touch the data in the level 0 warehouse.)  We look at, find the problems – invalid marks on test sheets, names on forms etc.  hanging chads (remember 2000?).  We make a correction, or diagnosis, sometimes creating new extracts for the district.  If a new extract is created, the district then loads that again, and has to wait til Wednesdays (I think that is the day new data is loaded into the level 0 warehouse.)

This is a big deal for us.  We process a lot of test data throughout the year.

A little more detail on this service and process provided on the Edutech web site.  Test scoring 101.

And a little more detail about test scoring and data extracts.

This is a helpful video that explains this process and its timeline in a little more detail.

The Blackboard behemoth

I support 10 web sites in my job that are traditional SchoolWorld web sites.  Some time not too long ago, BB purchased SchoolWorld – or they at least merged.  I heard a couple weeks ago that the SchoolWorld sites would no longer be technically supported at the end of this month – June 2015.  I also heard that the product itself would be experiencing and EOL date next June, 2016.

We recently set up a new SchoolWorld site.  My customer said something about how they used SchoolSites, not SchoolWorld for something.  I told him his new site would be a SchoolWorld site.

So – I am trying to get my head around the Blackboard juggernog a bit more.

Random things about BB that I am absorbing…

BB is Blackboard – they hold an annual moot in DC for a couple days in July.  If I was not going on vaca this year, I may have considered going.

BB is aka Blackboard, BB online, BB engage, formally know as Edlin, BB world, this is from their web page –

Lots of products – dept to their products Mission statement with anaylitics

This is probably the most relevant news header for what I am trying to understand

Ed-tech giant Blackboard acquires Schoolwires to help build school websites and apps for K-12 students

This is what I support – school websites for k-12 students.

This is a little disconcerting – reminds me of Microsoft back in the day….gobbling up anything that looks like it could make them $$ or at least threaten their monopoly.

Last year, Blackboard acquired MyEdu to help reduce the number of unemployed college graduates, and today’s news takes Blackboard’s total acquisition count up to 11 in the past 5 years alone, having taken on ParentLink (2014), CardSmith (2014), Perceptis (2014), NetSpot (2012), Moodlerooms (2012), Presidium Learning (2011), Elluminate (2010), Wimba (2010), and Saf-T-Net (2010). Between 2006 and 2012, it’s estimated that Blackboard spent around $500 million buying other companies.

Since 2011, Blackboard has provided website hosting and CMS for schools, following a merger with Edline. So with today’s acquisition news, Blackboard is cementing its position in this space, adding that “school websites and apps are the natural way” to improve teaching and learning.

Well – this may be a good place to keep up with things happening there….

New schoolWorld web site and stuff I am still learning

Hopefully, I learn every day, something. That is probably true. Blogging about things increases understanding of the subject. By writing about it, you think critically about it. Thinking critically about it makes you, well, consider it at a deeper level. A favorite term of mine from my development days is *grokking, pronounced: grawking. Understanding something at a very deep level.

Do it, do it again, explain it to someone else, teach it, do it again and again….then you start to approach grokking.

SchoolWorld is a company that makes web sites. For teachers, for schools etc. We use it for both. I support the websites used for schools. I work for WFLBOCES, as part of our mission, we supply and support websites for our schools or buildings. We have about 10 that each has their own website. Each site has people or content champions, I like to call them, who feed the beast. The beast being the website. If the website is not championed or feed, it is irrelevant. Most of the champions are self sufficient, I get them started, occasionally answer a question for them, maybe upload a file or fix a link or add a graphic or embed a video piece. But, for the most part, they do the work of feeding the site.

We are opening a new school this fall called ptech (pathways technology early college high school). A very innovative approach to high school and being of college. Even President Obama knows a little about it.

We put our new site up this week and I created webmaster accounts for my content champions for the site.

The site was only up a couple days when my content champion, Emory was asking for changes to the design of the site.  This is a true champion.  He wants to control the look and where he can place things on the site.  He has used other web sites like google sites.  He already has content on the google site, so it was quick and easy to copy it into the schoolWorld site.

Emory emailed the support team with a couple requests – he asked me first, and I told him I had no more power than him, both our accounts are webmaster.  The schoolworld support team emailed him back with good answers.


Hi Jevon, 
I am looking at some basic changes: 
Our site is: and it just went live this past week and I completed adding the content this past weekend…
Here are some of the changes/updates I’d like to make….
Add our logo to the left of the main title (upper row)
Change the yellow highlight to a color shade of green
Add an editor space above New Feature  (on the main homepage)
Also, when I looked at the example from SchoolWorld Templates, using the site, they have a Spotlights feature on the right-hand side of the main picture. How do you do this? And what editor space is available for this?
Thank you for any help you can provide. I have attached the current logo for our school. 



Thank you for responding so quickly.  James actually provided us with a logo to be added to your site.  Our Design team has the logo, but are unable to apply it at this time due to some hardware issues on our end.  We hope to have access to the server that houses the images soon and will apply the logo at that time.

Please refresh your browser and take a look at the new green to see if this will suffice.

We are unable to add an editor to the area of the site that you have referenced.  This particular template displays its Home Page Text directly to the left of the main image, just below the top navigation area.

Lastly, we have enabled the Spotlight module.  You may need to add it to your admin area after logging in.   This will allow you to create and edit your Spotlight stories that display on the right hand side of the page, similar to the example site you provided.  Thank you in advance for your response.


SchoolWorld Technical Support

Blackboard Inc.

Schoolworld added another module – spotlights – so my content champion could continue to be a champion.  They were willing to change the color scheme simple enough and added the new module without any additional changes or other bureaucracy.

Moral of the story?

find your site champion

support your site champion

Point them at the schoolWorld support and let them do what they can for him.

SchoolWorld has lots of little modules that it can move in and out to provide more or less functionality for the web site.  Similiar to moodle and plugins.  Content management sites like this are designed to be flexible and are build in small modules that can be added or removed.

The end.

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.


Get every new post delivered to your Inbox.