Education after Access... (1 Viewer)

Villarreal68

Registered User.
Local time
Yesterday, 18:35
Joined
Feb 15, 2007
Messages
133
Hello everyone,

I have been working with Access for some years now and I have created a few interesting and beneficial dbs for the company. I started with single user db, then multi user, then front-end and back-end dbs. I initially got started thanks to this forum and I don't think I would have gotten as far as I have come without your help. So thank you!

I finally get a chance to take some database training, compliments of my employer....so I want to make the most of it. So my question is what would be the recommended training I should take to improve my database knowledge?

Should I take some SQL Server classes?
Should I take some clasess on how to move my back-end Access databases to SQL Server (are there any classes like that)?
Should I look at something else that may help me better?

Any and all recommendations are appreciated.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 01:35
Joined
Jan 14, 2017
Messages
18,186
It depends on your own priorities.
If you are ever likely to use SQL Server, then that's a good idea.
However, it may be that you have other more pressing issues of things to learn.
You should know what you don't know ... if you see what I mean! :)
 

Villarreal68

Registered User.
Local time
Yesterday, 18:35
Joined
Feb 15, 2007
Messages
133
Hello Colin thank you for responding.
I would love to go into Java or PHP or some other programming language, but I was given the requirement that the training needed to be "Access Related". So I'm trying my best to learn something new and still make it relevant. So I figured I could learn SQL so I could learn how to move my back-end databases to a more stable platform, but I don't know if that is the best way to go or if those type of training classes are available. I don't just want to take some Access classes to find out that I didn't learn anything new. So I figure I would throw it out there and see how others experiences could help me make the best decision.
 

isladogs

MVP / VIP
Local time
Today, 01:35
Joined
Jan 14, 2017
Messages
18,186
Hello Colin thank you for responding.
I would love to go into Java or PHP or some other programming language, but I was given the requirement that the training needed to be "Access Related". So I'm trying my best to learn something new and still make it relevant. So I figured I could learn SQL so I could learn how to move my back-end databases to a more stable platform, but I don't know if that is the best way to go or if those type of training classes are available. I don't just want to take some Access classes to find out that I didn't learn anything new. So I figure I would throw it out there and see how others experiences could help me make the best decision.

I've been using SQL Server for over 10 years on an intermittent basis.
After all this time, I know how to use a TINY proportion of SQL server feature set.
I never took any classes in it but wish I had done so when I had the chance. I'm certain you will learn a lot ...

BUT before you commit yourself ... have a look at Steve Bishop's Access video series on You Tube - there are over 100 in all from beginner to advanced.
No need to watch them all. Read the titles & see if there are gaps in your Access knowledge that would also be useful to plug
 

Villarreal68

Registered User.
Local time
Yesterday, 18:35
Joined
Feb 15, 2007
Messages
133
Colin thank you for your response. I'll take a look at the Youtube videos.

Thank you for making reference to them.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:35
Joined
Feb 28, 2001
Messages
27,001
Villareal68, ask YOUR BOSS what he believes will be his department's direction or the company's direction before you sign up for ANY courses.

Eventually, Access runs into size/speed limits. Admittedly large limits. But the limits exist. In that case, you can protect the Access investment (particularly since you understand FE/BE splits) by upsizing databases to use SQL Server - and therein lies your educational direction. You can then focus on courses of SQL Server database management and interfacing.

Or your boss could foresee other directions of growth where you would share data with Access and WEB-based pages, using an active database such as SQL Server as the common BE for both elements. In which case some web-based work added to some elementary SQL Server syntax might be more in line with corporate needs.

Since the company is willing to send you to school, work WITH them by finding out what would be good for the company. HINT: This is a way to make you more valuable as an employee and it also gives you good relationships with your employer. I.e. they think more of you if YOU openly think more about their needs.

No, it won't stop you from one day getting let go if you screw up or business goes belly up. But remember this fact: You never get paid what you think you are worth. You get paid based on what your employer thinks your job is worth. So if you can improve that perception of worth, you are in a good position.
 

Mark_

Longboard on the internet
Local time
Yesterday, 18:35
Joined
Sep 12, 2017
Messages
2,111
One other area you may want to look into is networking courses. Knowing how the infrastructure is set up, configured, and maintained can be very useful when you are programming. It allows you to not only take into account the underlying media you will be dealing with, it will also allow you to express what needs to be upgraded or reconfigured to allow your system to work better.

There are times when being able to persuade the powers that be that segmenting your network for optimal bandwidth for certain departments has its advantages. This is especially true when you have data-intensive analysis and reporting that you are trying to do without having it bring normal operations to a crawl.
 

jleach

Registered User.
Local time
Yesterday, 21:35
Joined
Jan 4, 2012
Messages
308
SQL Server does tend to be the next technology in most Access developer's path, but really just the basics of it. SQL (be it SQL Server or any other robust RDBMS) tends to come in two parts: DBA level stuff (administering) and Application level stuff (what we as Access devs are used to). If you do take a course on SQL Server, try to find something a bit less DBA-ish and a bit more practical for application development, such as how to use CTEs and recursive CTEs, how to best leverage stored procedures and temp tables, how to use functions and what to be careful of in terms of performance, how indexing works, etc. With that said, some DBA level stuff is required to make the move from Access to SQL Server, particularly basics on security and backup/restore. All of this can be found easily enough online, but if you should happen to come across a course with such a focus, I'd jump on it, myself (or maybe would have, had I known what I was looking for back then...)

Regarding other languages, I'd stay away from PHP. It's a terrible language and has no relation to Access in any way. There's a rather famous article written on how bad of a language this is: https://eev.ee/blog/2012/04/09/php-a-fractal-of-bad-design/

Java is better, but if you're going to try to learn Java, I'd just assume learn C# instead (which is almost the same: close cousins in terms of syntax and such), but C# integrates much more closely with the rest of the Microsoft stack, which tends to be a natural progression much the same as Access to SQL is in terms of database stuff.

The problem with "Access related" these days is that there really isn't much... Access is a desktop database program, and there's really nothing else like it. Most things have moved to web interfaces (in which case SQL Server, C# and another layer of ASP.NET MVC might be in order), which is hard to say "related to Access". Thereagain, the ball is rolling back around again (go to LinkedIn and you'll constantly be bugged to run their desktop app for a "smoother experience"). Electron is an awesome platform for writing desktop apps (written in JavaScript, using the familiar-to-many html/css/js frontend stuff that every website is made with) and is cross platform and has great adoption: Slack and VSCode are both written with it, as two popular examples. The only other real options for desktop work is WinForms (.NET, but very dated) or WPF (.NET, very high learning curve: "makes the impossible easy and the easy impossible!" (very true - I made a video dance across a screen in hours but it took me three weeks of deep study to change the color of a button's border! crazy)).


Anyway... tl;dr: SQL Server's probably the most solid choice, and from there you have many options.
 

Lightwave

Ad astra
Local time
Today, 01:35
Joined
Sep 27, 2004
Messages
1,521
OK you sound competent to good at access (apologies if I am underestimating you)

I'm going to suggest some steps for you probably in this order. This happens to have been my journey as well.

  • Download SQL Server Express and get it installed somewhere on some kind of network would be good but locally is fine to get started - its free.
  • Practice trying to connect an access front end to the SQL Express - practice starting and stopping the service and creating simple tables
    The advantage of SQL Express over SQL Azure is that its free
  • Try and create a SQL Azure database and try to connect this to an access front end. You can kind of do this instead of the second point but Azure still really needs management studio and it is kind of better to have access to.
    Udemy SQL Azure
    The advantage of SQL Azure over SQL Express is that you use MS hardware and you can avoid any beaurucratic rules in your company. Plus you will be able to learn at home. The disadvantage - cloud costs - not much in the case of Azure about £6 for a cloud development database a month - I personally think that is worth it.
  • If you've got through this then now is the time to probably invest in a full on SQL course.
    This one is very good and I got it for only £10 Udemy frequently have sales
    https://www.udemy.com/microsoft-sql-server-2012-certification-70-461/learn/v4/overview
    This will allow you to take SQL Server certification if you want although that certification is a bit out of date now and that certification is hellishly difficult to pass. I suggest this because although a bit out of date SQL is a very stable language and the vast majority of what you are interested in really isn't changing.

    The next step would be to go asp.net development like JL is talking for someone like you who is thinking about full stack development its is probably better to learn full stack from the back to the front especially if you are competent with access already. ASP.NET is thoroughly tricky and slow at least with access / SQL Azure or SQL Express knowledge you will be able to get working programs up and running for virtually nothing. Web applications introduce a further tier and its very slow going at first.

    The major advantage of SQL Server is that you can develop against the same backend with Access and with any web framework so a nice setup is Access for the real complicated UIs and maybe the web for really simpler distributed viewing and editing. Complicated UIs using web frameworks cost loads of money or loads of time and require a lot of talent.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:35
Joined
Feb 19, 2002
Messages
42,981
I'm going to jump on the SQL Server bandwagon. However, if your company uses Oracle or DB2, you should take that class instead. Although all relational databases are similar in that they have the same basic features. Each implementation is slightly different and different vendors have made different expansions so you would want to study the specific RDBMS that your company uses.

Now that we've decided on database as your target, if you think you might want to move away from programming and become a DBA, then you would look for courses that concentrate on setup and tuning. In most companies, "they" wouldn't let you anywhere near the production server and in many, they wouldn't even let you near the test server so this class is unlikely to actually provide anything useful to your present situation. Personally, I think your company would prefer that you study database design and SQL. This will take the bits and pieces you've probably picked up regarding normalization and queries over the years and shine a bright light on them. Understanding an execution plan can be very useful for optimizing queries. You are not likely to find any classes on using Access with SQL Server so you'll have to work that out yourself or with us. When you use linked ODBC tables, Access makes every effort to send queries to the database engine and simply wait for the resultset. Of course, you can defeat that and understanding how SQL works will help you to understand WHY doing certain things in your Access queries will prevent Access from being able to send the query to the server and require it to do local processing instead. For example, if you create a function that does a calculation, it might not be immediately obvious to you why the Server can't process that - it can't because no RDBMS supports VBA. Therefore, Access would analyze the query and decide that it cannot send that part of the query to the server so it would send what it could and then run the function on the resultset before returning it to you. However, it is possible to make functions in the RDBMS and you might decide to create a server side view using a server side function to substitute for part of your Access query. Hopefully, it is epiphanies like that which you will experience with a deeper understanding of some RDBMS.
 

Villarreal68

Registered User.
Local time
Yesterday, 18:35
Joined
Feb 15, 2007
Messages
133
Hello everyone,

I'm amazed at the amount of knowledge you all dish out! This is the reason why I posted my question here. You all have different levels of knowledge and experience that I can use to make the best decision. Thank you all for your feedback!

The_Doc_Man: I will definitively take your advice and ask management what is their forecast to keep it aligned. Thank you!

Mark_: I come from the Tech Support Networking side so I'm very proficient in that respect. So I do understand why you think it's essential knowledge. Thank you!

Jleach: Kudos for your comment. That really helps me to understand the best path. I figured SQL was the next step, but your clarification on what to focus on was awesome! Less DBA-ish and more application level stuff!

Lightwave: I had considered installing SQL express, but SQL Azure sure sounds like a challenge that I had not considered. Will definitively try that as well. What Azure cloud service do you use? Thank you!

Pat Hartman: I have seen a couple of SQL Server installs and one mySQL install on our servers, but they are for Shrink wrapped application that have been installed. So I would say SQL is the prevalent one in our setup, and we don't have a DBA on hand. So database design and SQL it is. Maybe I can some day cover both DEV and DBA for the company. :)

Once again thank you all for your invaluable time and feedback. This takes me a million miles ahead of where I was when I asked the question yesterday. Cheers!
 

jleach

Registered User.
Local time
Yesterday, 21:35
Joined
Jan 4, 2012
Messages
308
It's not uncommon at all for developers to fall into the DBA role, so that's worth keeping in mind. My sentiment about focusing more on usage rather than administration comes with the assumption that you're staying in application development, but a good DBA is really quite invaluable, so there may be some merit to that as well. Maybe keep a light mind open to it at very least (The Accidental DBA: https://www.sqlskills.com/help/accidental-dba/). In fact, if you prefer learning on your own, taking the development end of SQL Server on by yourself and taking a class on the DBA end might be a very worthy approach.

Regarding SQL Azure vs SQL Express (or whatever other on-prem version of SQL Server): SQL Azure is basically the same thing, hosted in the cloud. In SQL Azure, you have considerably less administration to do (you fire up an instance and get your connection string and go, in a quick nutshell), whereas a SQL installation is very intimidating the first time through. In any case, I think installing SQL Express would server you well in the long run (it's intimidating at first but easily overcome once you roll your sleeves up, and it's also free, which is nice). Also, on-prem SQL Server is much easier for performance than SQL Azure (performance requirements fall in three categories: easy, meh and hard, which are: Access BE, SQL Server BE and Azure BE - see my azure link below for a whitepaper on the topic)

If you want to look more into Azure I have some info here (it was written a while ago and the UI snapshots and step by steps are probably slightly out of date now, but generally speaking the general concepts are still valid): https://dymeng.com/resources/azure/

For either hosting option of SQL Server, DNS-less connections are typically preferred (http://www.accessmvp.com/djsteele/DSNLessLinks.html). The basic idea is: use a DSN to link to the table during development, then write a relink module that runs on startup and resets the tabledef connection values accordingly (if you've done this to auto-relink to backends in Access, you're 3/4 the way there already).

Cheers,
 
Last edited:

Lightwave

Ad astra
Local time
Today, 01:35
Joined
Sep 27, 2004
Messages
1,521
Lightwave: I had considered installing SQL express, but SQL Azure sure sounds like a challenge that I had not considered. Will definitively try that as well. What Azure cloud service do you use?

It is called SQL Database

More information here
Link to SQL Database information

It is quite easy to get lost in SQL Azure as there are SO many options. Microsoft are offering various FREE introductory offers at the moment. It is worth signing up for something - probably best in your own time.

Its not really expensive but expect that at some point you will need to take out a subscription this should not cost you much but expect to pay something like $30 dollars a month to have an very simple developer setup once any free period has warn out. You can stop services at anytime so go in see what you are running and be prepared to stop things if you don't want to have costs.
 

Users who are viewing this thread

Top Bottom