What Next ? (1 Viewer)

[ECEK]

I am an intermediate user and designer in access 2013. I am able to use VBA to most things and (with the help of this site) am able to implement more complex VBA to utilise in my designs.

So what next?

Where should I be heading ?

I want to spend the next six months studying something new and wondered what language it should be if any.

What is the natural progression for an access developer?

You hear so many people dissing Access so i figured I could do with a back up.

Any thoughts?

 

[plog]

The reason people crap on Access is because they they think its a remedial database which let's advanced Excel users make powerful spreadsheets without understanding how databases work. And often I agree with them.

If you want to work with Access (or any database), I would first learn about normalization (https://en.wikipedia.org/wiki/Database_normalization) and then SQL (http://www.w3schools.com/sql/).

If you are looking to be a programmer that's still not a bad place to start, but I would pick an environment I want to develop for and start there. Java--> android apps, JavaScript --> web browsers, PHP or ASP -->web servers.

 

[stopher]

So what next?

Where should I be heading ?

I want to spend the next six months studying something new and wondered what language it should be if any.

What is the natural progression for an access developer?

Reminds me of the part in Alice in Wonderland where Alice comes to a fork in the road and has a dialogue with the Cheshire Cat...

“Would you tell me, please, which way I ought to go from here?”
“That depends a good deal on where you want to get to,” said the Cat.
“I don’t much care where–” said Alice.
“Then it doesn’t matter which way you go,” said the Cat.

Like Plog said, you need to understand where you want to go before your question can be answered. Do you want to be a database designer (by this I mean all the back end stuff - normalisation, table structures modelling etc), a front end designer (building interfaces and not having to deal with the back end design) or a coder (coding pretty much anything and not necessarily to do with databases or front ends). Access conveniently packages all three, but this is not necessarily the norm and you need to be ready for that.

Are you doing this for personal or career reasons. The answer to this might change your goals. You might decide that creating mobile apps is fun and fulfills a personal goal. Whereas for career you may set your sights on say web development - perhaps less of an interest to you personally but perhaps a better path for your own career - just an example, not a point of view.

In the very short term at least you could consider setting up a server back end on your local PC and connecting Access to it. This will give you an environment where you can really get your hands dirty with SQL and setting up database structures from ground up without the aid of the very friendly Access relationship view. From here you can create a web interface to connect to your server database. There are loads on tutorials on this and it will maybe give you a feel of what it is you want to do.

hth
Chris

 

[ECEK]

Thanks guys:

I'm looking for guidance in a career environment.

My skills are in creating efficiency within a small business environment 100-400 users over different departments.

Automating reporting etc. Linking access to Excel utilising VBA and applying the solutions in Runtime.

I really enjoy the solutions/design side so I think being able to do what I do but do it via a web based system.

So my data sits on a server and is accessed much the same way that i design now but via the internet.

I'm not concerned overly with the back end (although a basic understanding I will be required) but how do I get my solutions from the desktop to the world !!!

 

[kevlray]

Not to knock Access. It definitely has a place and a purpose. But I think that if you want internet based solutions, then you have to look at web design. That can take you a lot of places. I have just some some studying on CCS, javascript and jquery (to be used with a visualization tool). You can do a lot to enhance regular HTML.

 

[NauticalGent]

I realize this is not quite on point, but I have seen the lightbulb come on for at least 2 Excel "experts" at work. Most folks simply fear them and cannot get over that fear. The ones that have joined sites like this one!

 

[Simon_MT]

Before I start this is what I have encountered. SQL Server great backend but there seems a real hung-up about allowing the data to tell its story. You can take the data out of a relational database environment and then report on the information in Excel. Really! That sounds really smart.

The SQL statement processing in Access is better than SQL Server and simply can't calculate complex KPIs in SQL Server alone. SQL Server may be faster but not as good.

Visualisation like Power BI is worth looking at, it supports both 32bit and 64Bit applications and you can use both for SQl Server and Access amonst other databases.

A Terminal Server has been used, in the past, view it as a local Cloud, all you have to do is ensure plenty of up-speed with a synchronous connection.

Html in itself is a dumb as a box of rocks. so you probably need to look at PHP or ASP.Net and understand of MVC Model View Controller.

More and more visits to site are on mobile phones and tablets so responsive web design is now becoming a common feature.

The most important issues to remember is knowing what and how to present the information. I think that Access Reporting is really excellent and the Form ware is not found in SQL Server.

We should remember that business don't really care about the techniques involved they just want to see intelligent business information. Whatever new technology we use it should add value to the business. Sometimes you get a surprise - Power BI others like T-SQL are underwhelming.

Simon

 

[Minty]

The SQL statement processing in Access is better than SQL Server and simply can't calculate complex KPIs in SQL Server alone. SQL Server may be faster but not as good.

Can you elaborate on this, I frequently use stored procedures to handle elaborate datasets that are just a downright pain to manoeuvre in access, or access simply won't handle (eg hangs) due to complexity. I can't think of a SQL statement that SQL Server can't handle other than a pivot/cross tab query. (Which you can do in SQL just not natively)

 

[Simon_MT]

The problem with Functions or Stored Procedures is the looping to and from the proc on each record. It is a hanging offence to store a total and yet we are prepared to create such an overhead. For example, once a Scheme has closed, the values will always remain the same no matter what. so each time we want to compare Schemes we perform calcuations on fixed values it seems really efficient to re-calculate values that will never change..

I have never managed to get Access to hang with complexity and I have done some very complex calculations. I re-use expressions with a SQL statement but importantly I create Query Definitions at the bottom standard expressions most frequently used and then higher queries to do the more speicalised calculations. Even this is not good enough for weighted averages and these I need named totals on the reports.

There is still one calculation I have to sort out:

=(Round((Sum([FeedValue])/Sum([FeedQtyTonnes])),2)/1000)*Round((Sum([FeedQtyTonnes])*1000)/[GTotalKilled]/([GTotalDW]/([GTotalKilled])-([GTotalWeanersWeight]/[GTotalWeaners])*0.7),2)

And that is just one calculation and at the Grand Total Level. This has to be replicated at each level using the grouping values.

Simon

 

[Minty]

I don't loop in and out of a stored procedures to produce results, I use the stored procedure to return the entire result set. Maybe this is why you don't see them as efficient?

 

[Simon_MT]

Probably, but then I use Queries rather than scripts.

 

[kevlray]

Can you elaborate on this, I frequently use stored procedures to handle elaborate datasets that are just a downright pain to manoeuvre in access, or access simply won't handle (eg hangs) due to complexity. I can't think of a SQL statement that SQL Server can't handle other than a pivot/cross tab query. (Which you can do in SQL just not natively)

Odd I have used the PIVOT in MS-SQL and it returned the results pretty much the same as Access?!?

 

[stopher]

It is a hanging offence to store a total and yet we are prepared to create such an overhead.

While this is true of a relational database, it certainly isn't true of all types of database.

For example, once a Scheme has closed, the values will always remain the same no matter what. so each time we want to compare Schemes we perform calcuations on fixed values it seems really efficient to re-calculate values that will never change..

Typically you will build a data warehouse that DOES store aggregates at various levels. Server based engines are well suited to updating and making available such databases. Once the databases are built, the organisation can then point what toys it chooses to take that data further - web based dashboards, dynamic reports on demand, business analysis tools, data extraction etc. Data warehouses are typically separate from the transactional databases that run the day to day business.

Imho, the argument of MS SQL (et. al.) versus Access isn't about ability to process SQL - I'm pretty sure MS SQL will handle any SQL one dares to throw at Access. The issue is about scale.

For single or small number of users on a local network, Access does a great job of delivering solutions quickly. If we're talking reporting then sure, get Access to pull data from a range of sources, crunch some numbers and spit out reports or update spreadsheets and dashboards through various mediums and locations. It does this very well and there's nothing I like better than waving Access' sorcery over some data problem and producing some results while the Excel users are getting bogged down with the slowness of their vlookups and pivots (excepting to some extent power pivot/power bi).

But then consider an organisation collecting say millions of records per year e.g. a call centre. And then wanting to report figures in a variety of schemas and formats to teams based nationally or maybe even internationally. There's issues about pushing large volumes of data to a multi-user wan. There's also issues of security, consistency and maintenance. And how do we keep the users up to date with what we are delivering? The OP is talking about 100-400 users. This is not where Access sits imho.

I love Access and for many years I would deliver the Access solution. But there are just so many other products and principles outside of Access that should also be considered either along side or instead of Access for many situations.

My advice to the OP is to get a feel for a range of tools and methods of managing business data and delivering information solutions. And get acquainted with a server based engine. I taught myself the basics of MySql and MS Sql at home. It was enough to seal me a job in a previous company.