Time to switch from Excel to Access?

Soldato
Joined
7 Mar 2011
Posts
6,859
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.
 
Soldato
Joined
13 Jun 2009
Posts
4,230
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.
 
Associate
Joined
14 Mar 2007
Posts
1,659
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.
 
Soldato
OP
Joined
7 Mar 2011
Posts
6,859
Location
Oldham, Lancashire
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.
 
Soldato
Joined
6 Mar 2008
Posts
10,078
Location
Stoke area
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.
 
Soldato
Joined
13 Jun 2009
Posts
4,230
Location
My own head
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.
 
Soldato
Joined
13 Jul 2009
Posts
7,167
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:
Associate
Joined
14 Mar 2007
Posts
1,659
Location
Winchester
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.
 
Soldato
Joined
13 Jul 2009
Posts
7,167
Location
Llanelli
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.
 
Soldato
Joined
12 Dec 2006
Posts
5,129
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.
 
Soldato
Joined
21 Jul 2005
Posts
19,981
Location
Officially least sunny location -Ronskistats
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.
 
Soldato
Joined
28 Aug 2006
Posts
3,003
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 :)
 
Soldato
OP
Joined
7 Mar 2011
Posts
6,859
Location
Oldham, Lancashire
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.
 
Associate
Joined
7 Jul 2009
Posts
2,389
Location
Wiltshire
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 :D
 
Soldato
Joined
12 Dec 2006
Posts
5,129
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.
 
Soldato
Joined
12 Dec 2006
Posts
5,129
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.
 
Soldato
Joined
20 Feb 2011
Posts
3,619
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.
 
Soldato
Joined
18 Oct 2002
Posts
2,869
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.
 
Back
Top Bottom