Access 2016 and Excel 2016

Cromulent

Cromulent

Cromulent

Soldato
Joined
1 Nov 2007
Posts
7,016
Location
England
I currently have an Office 365 subscription and as part of that I get to use Access 2016 and Excel 2016. There is something coming out in the following year that I think I'll need to use both of them for but I'm not an expert on either.

I've built my own databases in PostgreSQL and Python / Django but I've never really used Access 2016. This will be a personal database that I'll use on my desktop. I basically want to store the data in Access 2016 and then analyse it in Excel 2016.

I was just wondering what you would suggest for learning these two pieces of software? I'd love to become an advanced user of Excel 2016 and would be willing to learn enough just to put together my own database in Access 2016.

Any help is appreciated :).
 
I would have a read into VBA for those applications as it gives you great control when simple wizards and regular items just dont get you far. Lots of videos out there and the odd online course can get you clued up in no time.
 
Access is going to be deprecated soon, I really wouldn't bother. Get to grips with XAMMP so you can design and database correctly using MySQL.
 
I would entertain the above if web based is the tool of choice but to say its not going to be considered soon is along the realms of the millenium bug scaremongering. :)
 
Django x2 said:
Access is going to be deprecated soon, I really wouldn't bother. Get to grips with XAMMP so you can design and database correctly using MySQL.
Click to expand...

I'm actually a web developer by trade. I wouldn't use MySQL if you paid me. The only reason I have it on my server is because I have some blogs that I write on that I don't really care about and Wordpress is a simple solution that gets you writing blog posts nice and quickly. For all serious database work I'd use PostgreSQL which is much better than MySQL.

The reason I wanted to use Microsoft Access was because I wanted to prototype something on the desktop but if it is going to be deprecated I might as well just move onto building a website from scratch without any prototyping and hope for the best. I didn't know they were going to deprecate it though. I guess I'll just concentrate on Excel then.

Thanks for the replies.
 
Hijacking this thread slightly - what's a good CRUD interface for MySQL/Postgres databases if I want to replace Access? I have an Access database which is fairly complicated, lots of tables with many-to-many relationships. Queries I'm fine with, but what I like about Access is being able to build subforms that read from related tables for data entry. So if I have a 'movies' form, I can look up a particular movie and see a subform with all of the actors listed, and ditto for the 'actors' form. Then I can add additional entries to the subform as needed. This is with a 'movies', 'actors' and 'movies_actors' relationship table.

I don't really have the skills or the inclination to program a write/update interface, I only used Access because I was familiar with it. So something that's relatively easy to replicate the functionality of Access' forms would be ideal.
 
El Pew said:
Hijacking this thread slightly - what's a good CRUD interface for MySQL/Postgres databases if I want to replace Access? I have an Access database which is fairly complicated, lots of tables with many-to-many relationships. Queries I'm fine with, but what I like about Access is being able to build subforms that read from related tables for data entry. So if I have a 'movies' form, I can look up a particular movie and see a subform with all of the actors listed, and ditto for the 'actors' form. Then I can add additional entries to the subform as needed. This is with a 'movies', 'actors' and 'movies_actors' relationship table.

I don't really have the skills or the inclination to program a write/update interface, I only used Access because I was familiar with it. So something that's relatively easy to replicate the functionality of Access' forms would be ideal.
Click to expand...

I use Django which is a full featured web framework written in Python. It is incredibly easy to use and supports multiple different databases including MySQL and PostgreSQL (although it supports some features that are only available in PostgreSQL so the Django developers recommend you use PostgreSQL). You can use class based views (which I personally dislike) that allow you to make a web page without having to program it all yourself. Plus it also has a really nice way of working with HTML forms that allows you to make forms and reports really easily. I highly recommend you check it out.

https://www.djangoproject.com/

If you want a nice IDE to program Django and Python projects that works on Windows / Mac and Linux then PyCharm is good. You can get it for £6.90 a month.

https://www.jetbrains.com/pycharm/
 
Cromulent said:
I use Django which is a full featured web framework
Click to expand...
But that requires me to learn Python, and learn how to use Django. I'm not a developer and I don't want to be. All I want is a simple application.

For instance you can connect Access itself to a MySQL table and get pretty much what I'm after, but it's awfully slow doing this over a network connection to the point that it's unusable.
 
El Pew said:
But that requires me to learn Python, and learn how to use Django. I'm not a developer and I don't want to be. All I want is a simple application.

For instance you can connect Access itself to a MySQL table and get pretty much what I'm after, but it's awfully slow doing this over a network connection to the point that it's unusable.
Click to expand...

In that case check out LibreOffice Base. Free and open source and is an active project so should be updated for years to come.
 
Cromulent said:
In that case check out LibreOffice Base. Free and open source and is an active project so should be updated for years to come.
Click to expand...
I've tried this, it's a contender for the worst software I've ever encountered:

- requires a JRE, but not the latest one
- native MySQL connections crash the application, requiring the installation of a MySQL driver (not a massive issue since Access requires this same driver, but annoying nonetheless)
- does not support defining relationships at the database level for either MySQL or Postgres
- when defining relationships at the form level, errors out retrieving the records in the subform, with a nice vague 'could not retrieve data' message
- randomly decides that the underlying database has 'gone away' and refuses to reconnect until I restart Base. Other apps have no problems connecting to the very same DB at the same time.

It's terrible.
 
El Pew said:
I've tried this, it's a contender for the worst software I've ever encountered:

- requires a JRE, but not the latest one
- native MySQL connections crash the application, requiring the installation of a MySQL driver (not a massive issue since Access requires this same driver, but annoying nonetheless)
- does not support defining relationships at the database level for either MySQL or Postgres
- when defining relationships at the form level, errors out retrieving the records in the subform, with a nice vague 'could not retrieve data' message
- randomly decides that the underlying database has 'gone away' and refuses to reconnect until I restart Base. Other apps have no problems connecting to the very same DB at the same time.

It's terrible.
Click to expand...

Working with proper databases like PostgreSQL is hard if you are not a programmer. Is there any reason you don't want to learn? You could build a much better solution yourself that has all the features you yourself require rather than hoping some third party has all the features that you want.
 
Cromulent said:
Working with proper databases like PostgreSQL is hard if you are not a programmer. Is there any reason you don't want to learn?
Click to expand...
I know that's probably the 'best' solution, it's just a matter of skill, time and effort - none of which I really have. The DB I'm working on is effectively a write-once, read-many thing. The 'read' side is pretty much working, I have a framework that I've written in PHP that runs the right queries and spits things out to my CMS in the way I want, and minor things like adding an extra column or two in a table can be catered for with minimum effort. But I'm by no means a competent PHP developer, and that's my best language.

It's the 'write' side that's a pain. The data I'm entering is a mess, it's effectively typewritten lists of varying quality. I've tried using character recognition software with mixed results, but the most reliable way of entering it is simply to type the records in manually. The relationships between the entities are mainly many-to-many, which seems to be the hardest to work with from a data entry point of view. To go back to my movie/actor example, in my case there may be anywhere from 20-100 actors per movie and records for these actors may already exist from previous movies that I've already entered. Complicating matters, there is no reliable unique identifier for the 'actors' in the original data.

So the pain point comes when I add a new 'movie' with 100 actors. Some of those actors may already exist, most probably don't, and the actors may or may not have a pre-defined unique ID. What i'd like is the ability to add a new movie, then for the actors each time I enter the unique ID do a 'join to existing actor record' if the ID exists, or create an entirely new actor record if it doesn't. There are a few thousand movies and probably close to 200,000 actors in total in the data, so anything I can do to simplify or streamline the data entry is going to have an impact. I'm not confident I have the ability to do this with or without the help of a CRUD framework.

My Access DB has got me closest to a solution but it's far from ideal - the subforms for the actor records are pretty easy to set up but I have to manually resolve issues for existing actors, and I have to periodically migrate the data from Access to my current MySQL database. I've looked at a few CRUD frameworks for PHP and other languages but none seems to offer much that would get me close to a solution. I could write my own, but it would take me an age, probably not work very well and would definitely be some horrible web 1.0 monstrosity that would be worse than working with my current Access abomination.
 
El Pew said:
I know that's probably the 'best' solution, it's just a matter of skill, time and effort - none of which I really have. The DB I'm working on is effectively a write-once, read-many thing. The 'read' side is pretty much working, I have a framework that I've written in PHP that runs the right queries and spits things out to my CMS in the way I want, and minor things like adding an extra column or two in a table can be catered for with minimum effort. But I'm by no means a competent PHP developer, and that's my best language.

It's the 'write' side that's a pain. The data I'm entering is a mess, it's effectively typewritten lists of varying quality. I've tried using character recognition software with mixed results, but the most reliable way of entering it is simply to type the records in manually. The relationships between the entities are mainly many-to-many, which seems to be the hardest to work with from a data entry point of view. To go back to my movie/actor example, in my case there may be anywhere from 20-100 actors per movie and records for these actors may already exist from previous movies that I've already entered. Complicating matters, there is no reliable unique identifier for the 'actors' in the original data.

So the pain point comes when I add a new 'movie' with 100 actors. Some of those actors may already exist, most probably don't, and the actors may or may not have a pre-defined unique ID. What i'd like is the ability to add a new movie, then for the actors each time I enter the unique ID do a 'join to existing actor record' if the ID exists, or create an entirely new actor record if it doesn't. There are a few thousand movies and probably close to 200,000 actors in total in the data, so anything I can do to simplify or streamline the data entry is going to have an impact. I'm not confident I have the ability to do this with or without the help of a CRUD framework.

My Access DB has got me closest to a solution but it's far from ideal - the subforms for the actor records are pretty easy to set up but I have to manually resolve issues for existing actors, and I have to periodically migrate the data from Access to my current MySQL database. I've looked at a few CRUD frameworks for PHP and other languages but none seems to offer much that would get me close to a solution. I could write my own, but it would take me an age, probably not work very well and would definitely be some horrible web 1.0 monstrosity that would be worse than working with my current Access abomination.
Click to expand...

Django could very easily sort out the problems you are having in a short period of time. The 6 part tutorial on their website is excellent and Python is probably the easiest programming to learn that I have ever used. Far easier than PHP. If you are going to use Python I highly recommend you use the latest version (3.6.1). The problems you are having with the database itself are also easy to sort out. You want to read about database normalisation which will explain everything to you about to build a decent database system. At least give the Django tutorial a try. It will be worth it just to see whether you like it. It is the best documented web framework I have come across. You can even use a CSS framework to build the user interface incredibly quickly.
 
Getting back OT, vba is handy for both access and excel. When you say analyse the data what examples can you give? Hard to post suggestions without being more specific.
 
El Pew said:
For instance you can connect Access itself to a MySQL table and get pretty much what I'm after, but it's awfully slow doing this over a network connection to the point that it's unusable.
Click to expand...

Something wrong with that then - our entire system at work is built with Access/VBA & VB6 linked to a MySQL backend (100+ GB) and no performance issues. Access queries can be slow, but that is normally because you are using access specific / vba functions such isnull(), iif(), dlookup() etc rather than generic SQL equivalents. For more speed then either use SQL pass through queries, and write them in MySQL specific syntax, or create views / stored procedures on MySQL and call them.
 
Armageus said:
Something wrong with that then - our entire system at work is built with Access/VBA & VB6 linked to a MySQL backend (100+ GB) and no performance issues. Access queries can be slow, but that is normally because you are using access specific / vba functions such isnull(), iif(), dlookup() etc rather than generic SQL equivalents. For more speed then either use SQL pass through queries, and write them in MySQL specific syntax, or create views / stored procedures on MySQL and call them.
Click to expand...
It's a remote MySQL server, not on the same network. The connection is quite fast but Access seemingly can't handle the slightest glitch in the traffic.

I need to learn Docker for other purposes, so I'll see if I can put together a Django instance in a container and use that for development. It appears that a simple front end for MySQL that's not a complete *********** simply doesn't exist.
 
Django x2 said:
Access is going to be deprecated soon, I really wouldn't bother. Get to grips with XAMMP so you can design and database correctly using MySQL.
Click to expand...

Where are you getting that from btw?

I'm not a huge fan of Access but its everywhere, usually with a few years of VBA attached, ditto with Excel. It will be hard to kill that massive user base.

I use MS SQL. But occasionally I use Access as a forms front end for another database, especially older systems using inked tables. I think people mainly underestimate how useful VBA and office applications are. Also how entrenched their use is in companies.

MS will try to move everyone to Azure, Sharepoint and Office 365 for Business. Be interesting to see how that all works out.
 
You must log in or register to reply here.
Back
Top Bottom