Time to switch from Excel to Access?

Woden · 8 Sep 2017 at 19:08

I think we are starting to push the limits of Excel now we just keep running into too many stumbling points.

Basically, we had a "Inbound Tracker", essentially a list of all the deliveries due (about 200-ish a day), that we update with arrival time, trailer number and fill% as the day goes on.

I updated it with other cells that update themselves, main one being "OK" if on time, or the delay in minutes if it is late. I put in conditional formatting so lates are in red, might be late (not in yet, but less than 30 mins late) in yellow. And cells for another department to update with why its late, ETA, stuff like that.

Problem is, I think, too many people edit or look at it. Excel likes to get data jumbled up if someone changes the sort order (say from delivery time to origin alphabetically) and just now, someone was looking at it, clicked save to update what I had input it and I lost over an hours work.

I'd like it to be a database, but I have limited knowledge of Access, and just enough SQL from a short module at uni on PHP. I don't mind learning as I go, I enjoy it, and if I don't nobody else will!

Would Access be viable? Ideally one database (one excel workbook a day at the moment, but one database would be far better for monitoring performance) with separate views for each department so they can only see/do what they need to.

I've gotten pretty good with VBA in excel, so not too worried about that.

rexehuk · 9 Sep 2017 at 19:58

Could keep the excel sheet, but change the access model.

If it's just "people looking at the sheet to see updates" then maybe something like:

- Power BI connector on your machine
- You maintain control of sheet
- All others look at Power BI that is refreshed every 15 minutes via website URL

You could have it in the same format with conditional formatting... but also do cool stuff like showing KPIs, deliveries on time today and so forth.

dazzerd · 9 Sep 2017 at 20:22

I've made a career telling people excel isn't a database so yes if it's something which needs to be shared in real-time use a db backend at least front end can be anything you want Inc excel though I wouldn't recommend it.

Woden · 9 Sep 2017 at 21:00

I'm gonna use this as an excuse to learn Access on company time.

james.miller · 9 Sep 2017 at 21:05

dazzerd said:
I've made a career telling people excel isn't a database so yes if it's something which needs to be shared in real-time use a db backend at least front end can be anything you want Inc excel though I wouldn't recommend it.

^^ what he said. Do this.

Woden · 9 Sep 2017 at 21:34

rexehuk said:
Could keep the excel sheet, but change the access model.

If it's just "people looking at the sheet to see updates" then maybe something like:

- Power BI connector on your machine
- You maintain control of sheet
- All others look at Power BI that is refreshed every 15 minutes via website URL

You could have it in the same format with conditional formatting... but also do cool stuff like showing KPIs, deliveries on time today and so forth.

IT here are pretty strict, I won't get any additional software authorised unfortunately.

AHarvey · 9 Sep 2017 at 23:48

Woden said:
IT here are pretty strict, I won't get any additional software authorised unfortunately.

You will if you put together a proper request, cost it, say why it is needed and how much time and money it can save the business. You don't know if you don't ask.

Other option would be to create a web front end and sql back end. No need to add any software then as it can all be done in browser, anyone can access it with permission and you can access it on any platform.

rexehuk · 10 Sep 2017 at 11:58

AHarvey said:
You will if you put together a proper request, cost it, say why it is needed and how much time and money it can save the business. You don't know if you don't ask.

Other option would be to create a web front end and sql back end. No need to add any software then as it can all be done in browser, anyone can access it with permission and you can access it on any platform.

Indeed.

But to create a SQL server... and web server... you're going to need software and servers. That is more unlikely to be granted to you if you're just sitting in the business. Not only that if you're physically coding (and they are truly "very strict") then you're going to need to comply with coding standards, including secure coding standards and penetration tests.

Tealc · 10 Sep 2017 at 16:40

The use of Access over Excel made a huge difference to the way my old department functions. We used to have duplicate records, sharing issues and spreadsheets left open in someone's 4 days off. All went away when we started using Access.

IT seem ok with it. They wouldn't help us build it but at least we got server space for it.

Many say we should do away with Access and use SQL server but that would need someone to actually program the front ends from scratch. Access makes it easier, although it is fairly reliant on VBA to do anything beyond simple updates.

dazzerd · 11 Sep 2017 at 20:51

Tealc said:
The use of Access over Excel made a huge difference to the way my old department functions. We used to have duplicate records, sharing issues and spreadsheets left open in someone's 4 days off. All went away when we started using Access.

IT seem ok with it. They wouldn't help us build it but at least we got server space for it.

Many say we should do away with Access and use SQL server but that would need someone to actually program the front ends from scratch. Access makes it easier, although it is fairly reliant on VBA to do anything beyond simple updates.

Nothing stopping you using access as a front end and sql as the backend. If you are building split unbound access databases which you should be doing if you are working over a wan or lan then porting the backend to sql is relatively trivial though you will need to think about how you deal with user access and queries a little differently. Once you have the backend done then you can think about being a bit more advanced with the front end as well. But to be fair for 90% of lob systems the ui you can produce in access is good enough.

Tealc · 11 Sep 2017 at 22:00

Nothing stopping you using access as a front end and sql as the backend.

Have considered this but as I'd need to rewrite a load of query code and that I've moved onto another department it's low on my priority list. Mind you due to our really slow internal network (200kB/s) it can take a minute to run some of the queries. So it might change eventually.

OspreyO · 14 Sep 2017 at 00:41

Tealc said:
Have considered this but as I'd need to rewrite a load of query code and that I've moved onto another department it's low on my priority list. Mind you due to our really slow internal network (200kB/s) it can take a minute to run some of the queries. So it might change eventually.

More like the lack of a proper database.

gpuerrilla · 1 Oct 2017 at 19:51

dazzerd said:
I've made a career telling people excel isn't a database so yes if it's something which needs to be shared in real-time use a db backend at least front end can be anything you want Inc excel though I wouldn't recommend it.

Agree ^.

I currently spend lots of time at work biting my lip over projects using Excel as their 'database'. One thing I did notice is using conditional formatting slows the processing down a ton! If you are able to minimise this you will find the performance jump up again.

djkav · 17 Oct 2017 at 18:20

I'm surprised some people haven't used excel for word processing.
Excel is for numbers/finance and charting. Access is for data storage, reporting and data tracking. Word is for writing simple text documents

Woden · 17 Oct 2017 at 18:26

djkav said:
I'm surprised some people haven't used excel for word processing.
Excel is for numbers/finance and charting. Access is for data storage, reporting and data tracking. Word is for writing simple text documents

Literlery the only number in the sheet is how late the trailer is, with some conditional formatting. The Powers That Be don't seem understand that if they give me time to rebuild it in access, it will be more stable, less stress on the file server and finding historical data and reports for ONE database is far, far easier than trying to do it with a workbook per day.

izzop · 18 Oct 2017 at 06:20

djkav said:
I'm surprised some people haven't used excel for word processing.
Excel is for numbers/finance and charting. Access is for data storage, reporting and data tracking. Word is for writing simple text documents

I had a boss back in the 90's who used to use Lotus 123 as a word processor

OspreyO · 22 Oct 2017 at 03:09

djkav said:
I'm surprised some people haven't used excel for word processing.
Excel is for numbers/finance and charting. Access is for data storage, reporting and data tracking. Word is for writing simple text documents

Word is a lot more powerful than people realise. I've written quite a few publishing and authoring systems using word/VBA.

OspreyO · 22 Oct 2017 at 03:10

Woden said:
Literlery the only number in the sheet is how late the trailer is, with some conditional formatting. The Powers That Be don't seem understand that if they give me time to rebuild it in access, it will be more stable, less stress on the file server and finding historical data and reports for ONE database is far, far easier than trying to do it with a workbook per day.

You might consider doing a prototype yourself in your own time. If it makes you own job easier, and you learn a valuable skillset it doing it.

Bluecube · 22 Oct 2017 at 07:59

djkav said:
I'm surprised some people haven't used excel for word processing.
Excel is for numbers/finance and charting. Access is for data storage, reporting and data tracking. Word is for writing simple text documents

I’ve seen Excel being used for word processing several times. One cell per line, blank cells between lines for paragraphs, absolute mess.

Vipernet · 4 Nov 2017 at 00:34

I work for a removal company, who used to use a paper diary for the bookings! This meant if one person had the diary making a booking and someone else phoned in to make a booking we'd need to phone them back once the diary was free!

I created a quick google sheets template that allowed jobs to be input, and also allocate men and vehicles to specific jobs, so you could also see what men and vehicles you had left to use for each day. I created google accounts for everyone so they have log in to use it, but it also shows a revision history for any changes and the user that edited them. My next step it to create a gui for it to make it simpler, but it works ok on the boss' tablet using the google sheets app at the moment.