MS Access and Excel, am I asking too much?

[Woden]

So, at the minute someone in my office sends a few performance reports every hour, 24/7/364. The same data is entered into a few places a few times, and the main reports sent have copies saved. Going back years, it's madness. Then they complain about running low on space on the main fileserver

So I am looking at moving to a database. MS Access ... cos that's all I have. We have Oracle and a webserver but fat chance of me getting authorisation to use that.

So I built tables to cover all the data we need. Different tables for different related stuff. So a table for each sections production per hour (date-time, area1, area2, area3 etc), table for equipment issues (date-time went down, date-time went up, description of issue, engineer ticket reference number).

Every hour all this is emailed out by an excel macro. It's slow, buggy, cant be edited (creator left, took password to the macros and formatting with him) and we have millions of files everywhere. Basically not fit for purpose.

How hard would my idea be to implement? Basically whack it all into a database with a user friendly form (I'm the only nerd on the team), and mail out a report. I'd have to copy the current format, so that has to be flexible. And I will need to do some calculations on it (total downtime, volume etc). I'm pretty good with VBA, at least in Excel so no worries there.

Been learning Python (see earlier post many moons ago, changed my mind from Java, I like python ). But again, fat chance of being allowed to use it.

 

[AHarvey]

The question I would be asking is where is the data originally from? Where is the data coming from to get into the spreadsheet?

I'd be wanting to pull data straight from the source into the database, bypassing the spreadsheet, it's another step in the chain you don't need.

I'd also be looking at pushing the online solution. Don't think you won't get authorisation, as I've learnt it's how you put the idea across, not what you are actually asking for.

This is the idea, this is what we need, this is how much it will cost in man hours or money and these are all the benefits it will provide us. Also point out that custom reporting options will exist online as will the ability to easily backup, available anywhere and flexible.

 

[Woden]

We get most it from an 18 year old SCADA system with no access to the network. It's embarrassing when we have to explain it to potential clients on visits. We actually write the figures down then go type them in.

 

[AHarvey]

not ideal then really. I've no experience of SCADA systems so can't comment there but I take it that can't be upgraded to a newer system?

As for your idea, I suppose if it's done manually then either would be of use but I'd still lean towards asking for server space + dbase solution. All they would need is a cheap tablet then with wifi access and they can sit and enter the data directly.

 

[namnoc]

If it is being copied manually off a monitor then it may seem mad, but a webcam and OCR software is the best input method. There is more than one very large organisation in London that I know of doing exactly that

 

[AHarvey]

If it is being copied manually off a monitor then it may seem mad, but a webcam and OCR software is the best input method. There is more than one very large organisation in London that I know of doing exactly that

That's actually a very good idea. I've used a webcam + Raspi to create a very basic Braille reader but never finished it due to leaving the company.

it would depend on the monitor it's reading it off though.

 

[Quartz]

We get most it from an 18 year old SCADA system with no access to the network.

An airgap is an impenetrable security shield against hacking.

With regard to using a proper database, consider SQL Server Express, as it's free.

 

[BlackMaster]

I've done the Excel & MS Access solutions in the past. If you can, go straight to SQL Server Express as Quartz has already mentioned. I believe from memory you will be limited to 10 GB's of storage space, which is loads if you design your tables correctly etc. The other option which is free to use would be MySQL database.

You will need a 'server' to run it on. By server I mean a normal PC with 2-4GB's of RAM, placed some where it won’t be switched off. This will most likely fulfil your needs.

When I asked for my first server with very small requirements (dual core, 4GB of RAM etc), I was quoted £8000 for a virtual machine. That was the IT Department’s way of saying 'no'. Suppose what I'm trying to say is, you may also have to be aware of company / inter-departmental politics.

Out of interest, what version of Excel was used to create the spreadsheet with the macros? If it was before Excel 2007 (.xls rather than .xlsm), then the password may be recoverable with a hex editor.

 

[Cromulent]

PostgreSQL is the best free database out there with no limitations and works on Windows, Mac OS X, Linux, *BSD etc etc and is actually quite easy to use. I have it running on my Windows 10 machine so I can develop against it. The documentation is fantastic and it has drivers available for just about every programming language out there. I'd highly recommend you use that. You can even use PostgreSQL as the backend database for Microsoft Access if you install the correct drivers which makes it even easier to use. Check it out.

 

[AHarvey]

PostgreSQL is the best free database out there with no limitations and works on Windows, Mac OS X, Linux, *BSD etc etc and is actually quite easy to use. I have it running on my Windows 10 machine so I can develop against it. The documentation is fantastic and it has drivers available for just about every programming language out there. I'd highly recommend you use that. You can even use PostgreSQL as the backend database for Microsoft Access if you install the correct drivers which makes it even easier to use. Check it out.

Thanks for the info!

Not something I've looked at but will be now

 

[Sone]

If it's scada surely some sort of opc and "sneakernet" solution you could implement?