Skip to content

Stupid old access DB….. that only works in verson 2003

Well – that is what it seems anyway.  One of my access customers, who runs a fairly simply billing DB contacted me this week with this message.

Help, I have been out for 6 weeks and now my billing db is not working“.

I love the detail provided.

I looked at the DB and saw that on the main form of the application, the buttons were not working.  Its like a switchboard form – or auto-start form – we know the ones….

When I looked under the hood, there was no VBA and no defined macros ??  I wondered what the heck was supposed to be firing on the click events for each button?  The DB objects were there, tables, queries, reports, forms.  But no macros anywhere to be found….and no VBA.

What gives?

When I pushed a little deeper, my customer said “oh, well it only runs in access 2003.”  And she has a never version, 2010, on her workstation, along with the older version still.  don’t ask why, because IT installed it as part of the suite – and just did not bother removing it.

Quickly, I deduced that she probably opened the DB in access 2010 – it did some type of conversion of old VBA code – or tried to, and corrupted the DB.  I kinda proved this by opening an older copy of the DB using 2003 and seeing it work.  After sleeping on this, a very clear thought bubbled up today

my clear thought “This DB has been limping along in 2003 for many years and no-one has bothered to figure out what is necessary or required to update, so it works in the newest version of access.  Rather, it has been easier to simply keep 2003 laying around.”  How much work could it really be?  Her DB is not that complex.

Fortunately for her, she keep the db in her private network space and so I was able to request a copy of it from about 6 weeks ago.  I expect when I get the copy, I will be able to open it in 2003 without issue.  And that will solve the mystery of what happened to it.

Which leaves me with 2 outcomes

1 – Fix the code in the DB so it works in newer access version

2 – remove access 2010 from her computer.

The first is the better option, treating the problem, not the symptom.  BUT, this may prove to me more work that its worth.  When I have time, I will look at it with an eye toward 1 and update this post.

New cameras, IPs networks and explaining to other people

My boss, actually my bosses boss, walked into my office the other day, along with the project manager for our security camera project and asked why he could not see the new cameras working.  Well, since it is the bosses boss, I do not say what comes to my mind, which was this “how the hell should I know – I have not been involved in any of the communication about new cameras”.  Thankfully, I have a filter that keeps things from escaping my lips….

After a bit of investigation and discussion with the project manager, I have a better idea of the work that was recently completed.  I keep my cool because I always try to respect the working relationships I have with people (they are very valuable).  Armed with the new information, I called the vendor, Frontrunner systems and talk with the technician responsible for the work.  Ill call him Jake.  I spend about 30 minutes on the phone with Jake, after I created a print out of all the cameras and their names on a couple pieces of largess paper.

IMG_1662[1]

Jake informs me of the new cameras – where they are, which area, we have four.  WTCC, FLTCC, Newark main campus, and Newark ware house.  I identity the new cameras and their names and forward this information to my bosses boss.  I also reminded him that he or whoever he wants to show off the new cameras too can use a web client version of the software.  They would need to have an account with permission to the area where they want to view.

When I was done working with my boss and her boss and the project manager and Jake the technician, I was left feeling like I need a more thorough understanding of things.  I typically feel like this, since I am a person who always wants the conceptual understanding of whatever it is I am working on.  I need that birds eye view from which to descend and add meat to the bones.  To that end, I logged into the security camera software (Genetec) and looked at a couple options in more details.  One of which allowed me to see the IP addresses of each area – and upon clicking the ip address registered to the area, I could see the name of the camera.  Really the opposite view of how I had been looking at things – which was camera and then details, like ip.   Using the Configuration arm of the software (Genetec Security Center Configuration Tool), I choose Network and then was able to see

When I was talking with jake about the new cameras he said something like this, which is birds eye view and I latched onto it.

Process of configuring/adding new items (cameras) to the system

  1.  – Jake installed the cameras – physically
  2.  – Waited for Dave to configure the network (this is the part I would like to know more about)
  3.  – Then Jake could add IPs to the cameras (finish configuring them)

That process is interesting to me.  Makes sense that the cameras could be physically installed, but if the network is not ready, you cannot complete the installation, since each camera needs an ip address on the network.  Perhaps we needed to add more switches or actual ports on the network.  I am such a networking novice – only enough knowledge to be a little dangerous.

Another valuable view in the Config Tool is the Logical view.  It shows each area and all the cameras installed in that area.  Each area, like FLTCC has members or cameras.  So, FLTCC is an area and its installed cameras are members.  Conceptual, I like it!

Also – something useful in the software that i stumbled across was the video retention period, mainly set for 30 days – meaning the camera video storage would be around 30 days by default – after which the space is overwritten by the next 30 days.  If we have a problem and need to view camera footage, we have one month to do it.  I wonder if that is an industry standard?  Storage is not that expensive BUT, video storage is very demanding.  I did work with my customer a while back on a couple of the cameras and changed that setting to 40 days.

One final thought of the day…. partitions also have members, like areas.  The FLTCC partition has members like, cameras, and users.  Think conceptually – areas have members – patitions have members – areas have partitions (I think).  User groups have members – users.

Networks, switchs, power amplifier, cameras etc

I support a camera system that has many cameras over a network.  Sometimes cameras, individual ones, go off line.  Meaning they still show up in the system, but do not actually show anything.  While investigating this scenario yesterday, I walked around with one of our network tecs and the guy from the company who sold the hardware.  When a camera is down, follow these steps to try to bring back on line.

First – ensure the camera is getting power.  the camera is plugged into our network.  Look at the network drop, where the camera plugs into, and see if there is a number or something designating that drop location, like A15.  Next, find where that drop (the ethernet cable) enters the network – where does it plug in?  Which network switch box does it plug into?  We have a bunch of these *closets in our buildings.  Each closet contains lots of switch boxes which allow devices, like computers, copiers or cameras to be plugged into the network.  Once you find the closet – find the matching drop number, like A15.

In our case, the cameras are getting their data and power over the same line (cable) this is called a POE .  Power over Ethernet.  I think what actually happens is the cable coming from the camera plugs into a switch device – that has two connections for that drop, like A15.  So cable A15 plugs into the data in port on the power injector (PowerDsine 3501G) and then back out from the data and power out port  – back into the switch box.

IMG_1642[1]

Makes sense on a highly conceptual level.  This way, each device on the network does not need a separate power connection.

Yesterday – the power injector failed.  The power cable coming from the switch to the injector failed – at least it seemed to.  It indicated, via a yellow light, rather than a healthy green one, that something had failed physically in the device.  Once the device was replaced – the camera came back on line.

Moral of the story?  Networks are a little complicated and offer multiple fail points.  This problem was not software related, at all.

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.

group_sort

That is what was controlling the ordering.  Interesting also.

Takeaways?

  • 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 wflptech.org 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….

http://www.blackboard.com/news-and-events/press-releases.aspx

Follow

Get every new post delivered to your Inbox.