1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Time to switch from Excel to Access?

Discussion in 'HTML, Graphics & Programming' started by Woden, 8 Sep 2017.

  1. Woden

    Soldato

    Joined: 7 Mar 2011

    Posts: 6,860

    Location: Oldham, Lancashire

    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.
     
  2. rexehuk

    Mobster

    Joined: 13 Jun 2009

    Posts: 4,008

    Location: My own head

    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.
     
  3. dazzerd

    Wise Guy

    Joined: 14 Mar 2007

    Posts: 1,619

    Location: Winchester

    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.
     
  4. Woden

    Soldato

    Joined: 7 Mar 2011

    Posts: 6,860

    Location: Oldham, Lancashire

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

    Capodecina

    Joined: 17 Aug 2003

    Posts: 19,460

    Location: Woburn Sand Dunes

    ^^ what he said. Do this.
     
  6. Woden

    Soldato

    Joined: 7 Mar 2011

    Posts: 6,860

    Location: Oldham, Lancashire

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

    Capodecina

    Joined: 6 Mar 2008

    Posts: 10,043

    Location: Stoke area

    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.
     
  8. rexehuk

    Mobster

    Joined: 13 Jun 2009

    Posts: 4,008

    Location: My own head

    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.
     
  9. Tealc

    Soldato

    Joined: 13 Jul 2009

    Posts: 7,034

    Location: Llanelli

    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.
     
    Last edited: 10 Sep 2017
  10. dazzerd

    Wise Guy

    Joined: 14 Mar 2007

    Posts: 1,619

    Location: Winchester

    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.
     
  11. Tealc

    Soldato

    Joined: 13 Jul 2009

    Posts: 7,034

    Location: Llanelli

    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.
     
  12. OspreyO

    Mobster

    Joined: 12 Dec 2006

    Posts: 3,773

    More like the lack of a proper database.
     
  13. Th0nt

    Capodecina

    Joined: 21 Jul 2005

    Posts: 13,520

    Location: N.Ireland

    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.
     
  14. djkav

    Mobster

    Joined: 28 Aug 2006

    Posts: 2,979

    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 :)
     
  15. Woden

    Soldato

    Joined: 7 Mar 2011

    Posts: 6,860

    Location: Oldham, Lancashire

    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.
     
  16. izzop

    Wise Guy

    Joined: 7 Jul 2009

    Posts: 2,275

    Location: Wiltshire

    I had a boss back in the 90's who used to use Lotus 123 as a word processor :D
     
  17. OspreyO

    Mobster

    Joined: 12 Dec 2006

    Posts: 3,773

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

    Mobster

    Joined: 12 Dec 2006

    Posts: 3,773

    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.
     
  19. Bluecube

    Wise Guy

    Joined: 20 Feb 2011

    Posts: 2,484

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

    Mobster

    Joined: 18 Oct 2002

    Posts: 2,808

    Location: 2 doors down from Subo

    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.