The Difference Between Microsoft Access and SQL Server
ฝัง
- เผยแพร่เมื่อ 7 ธ.ค. 2017
- Everyone knows that Microsoft Access and MS SQL Server are very different, but there is a common assumption that one is an advanced version of the other. This is not at all the case. Today we will dive into what Access is, what a database server is, and how the two main Microsoft products in this arena relate to one another.
Need MS SQL Server, MS Access, or broader database consulting or support? NTG is a non-reseller consultancy that works in all aspects of database design, management, and support including traditional relational platforms like SQL Server, Oracle, MySQL & PostgreSQL and also NoSQL platforms like REDIS, MongoDB, etc.
www.buymeacoffee.com/scottala...
^^^^ Support me and the Channel ^^^^
Linux Administration Best Practices by Me:
www.amazon.com/Linux-Administ...
I have worked as a consultant at NTG, a global technology and business consulting practice for more than a quarter of a century. Reach out for guidance, consulting, IT outsources, etc.
info@ntg.co
ntg.co/
Check out my other channels for more content:
/ @scottalanmillervlog
/ @nicaragua360
/ @cameracafebyscott
/ @nicarumba
/ @drivewarp
/ @takeflightwithscott
/ @thisisnicaragua
/ @scottalanmiller
/ @everydayvloggerscott - วิทยาศาสตร์และเทคโนโลยี
![เจ้าผู้เดียว - ดิด คิตตี้ [OFFICIAL MV]](http://i.ytimg.com/vi/q0NLCYKBOTo/mqdefault.jpg)
THANK YOU! I've used SQL Server professionally for 15 years, and I'm planning to learn Access (so I have more job opportunities), but I'd never known the differences until your tutorial. Very clear. Nicely done.
I'm glad that it was helpful. I'd be surprised if learning (or even mentioning) Access would aid in career development once you know SQL Server. It's very different things. SQL Server is a high paying, enterprise class skill. Access is an end user / secretarial skill that exists only for people who don't have the skills to use other products. So by the very nature of it, one is a high paying career, and the other is an essentially unskilled item. So since you are already in the highly paid and skilled workground with loads of experience, going back to an extremely basic "fall back" skill for people who can't do what you do, is likely a waste of time. Anyone that wants Access, but is considering hiring you, would likely just skip the Access project and do it right instead since they'd have you.
@@samit8178 actually, you can use access as kind kind of a GUI to SQL Server, so end users who don't know SQL server can still use the data. You do the heavy lifting with SQL server and the end user will write basic scripts/use drag and drop in access. That's what we do and it works well, people use access and excel to interact with the SQL server back end.
Access is nice as a GUI to bigger databases, but it's not the best.if you use it on its own unless your project is small.
In that sense it can aid your career, if you want to make users be able.to interact with your database but they may not have experience and need an easy interface.
@@samit8178 I know this is an old conversation, but you are wrong. Yes, Access has some user-friendly built-in functions, however with VBA and SQL skills, you can make very advanced functions in Access, and create applications that compete with the big expensive software solutions.
@@simonmaersk No, no amount of VBA makes Access anything approaching an enterprise application. That's simply untrue. Nothing that runs VB or VBA at all, nothing that requires Access as an application... can even think of being classified as a business tool. Can Access access a real database, sure. Is it ever in any way a proper business tool? Heck no, it's always a total joke. It doesn't meet even the most minimal requirements for something to be considered business class. It has totally unnecessary vendor lock in, huge deployment costs, massive performance issues, an architecture that is an utter joke (client server) and has no place in the current era or for the past several decades. Just because some other crap is "expensive" and bad, too, does not imply that Access can be okay. Anything you can do in Access can be done vastly cheaper, faster, safer, and more flexible using countless actual professional tools (that are free.) There's no benefit to Access, only caveats. Loads and loads or completely unprofessional downsides, zero value.
@@simonmaersk none of that suggests that I am wrong. That you CAN make something bad work doesn't suggest it is good. And yes, some big expensive software is crap. None of that suggests that you should intentionally use bad tools or that you can excuse doing a bad job yourself. Access cannot make software efficiently, cannot produce software that meets any modicum of modern minimal design standards, or can be deployed in any professional way. That it is not alone in being bad and not a requirement for making bad software is very different from it being unable to be good. Access has no place, absolutely none, in software development. There's no way to use it well, and even less way to deploy it.
Wow, that was exactly the explanation I needed! Thank you!
This is great. I was suspecting that a business problem was down to multiple users hitting a database set up with access but this confirmation and fleshed out explanation is valuable confirmation that it’s time for my team to grow into something more robust.
Glad it helped!
Really enjoyed your video here! As someone who's trying to wrap their arms around different solutions as a non-CS or IT person, this was very informative! Thank you!
I'm so glad that it was useful! Thanks for the feedback!
SAM I probably saw a million of your posts on Spiceworks over the years and your avatar and lengthy responses gave me the impression you were a very serious guy with an intimidating level of knowledge. Glad you decided to do videos. It suits you well!
Thanks! That "serious" picture was me being goofy one day in my apartment.
Good and simple explanation, thanks dad!
Brilliant explanation. Easy to understand, and answers the question!
Glad it was helpful!
Thanks! You just cleared my doubts! Keep up with the informative videos!
Glad that it helped!
Amazing explanation! Thanks! I needed this said so understandable.
Very glad that it has been helpful!
Thanks Sam for your assessment - made things clear for me
Glad to help!
Great explanation. Thanks
Thank you
Thank you for the direct and succinct explanation.
Very helpful...Thanks
Yes, it was really to the point and succinct.
Great video. You explained it perfectly
Glad you enjoyed it! Thanks!
Thank you!
Thank you for this clear explanation!
Glad it was helpful!
well explained. thank you.
Glad you liked it
Great explanation!
Glad it was helpful!
THANK YOU SIR
Very well explanation
Thank you!
Very nice explanation. Thanks.
You're welcome. Glad that it was helpful!
Say for the management of organism data (multiple rows of data for each species, few hundred rows total amongst ~60 species), but wanting to be able to organize into species-level data that can be opened up to see the individual rows of individual studies. It sounds like Jet DB (sp?) + Access is the way to go? Currently all of our data is in Excel & CSVs.
SQL Server will work great for that. JetDB will be acceptable. I wouldn't say that JetDB is "the way to go", but it would work.
Does anyone know what the best Database Management Software would be most compatible for Apple / Mac ?
MacOS is a desktop only platform, database management systems are meant to be on servers. So you won't find too much about MacOS + good databases. That said, several enterprise database options like MariaDB, MySQL, and PostgreSQL will run on MacOS just fine. But it would have to be an extreme case where you would do this. Even in an absolutely tiny office with just two or three people, I'd put my database on a server, even a tiny one, and run some flavour of Linux. You don't want any "desktop" product with database in the name in any business situation, not even a two person show.
Under business situations, it is the app that talks to the database, never the database, that would run on a Mac. And all **modern** business apps are going to be web based and they won't care if you are on a Mac or not. But it is always the app, not the database, that needs compatibility with your desktops. But if you have desktop limitations from an app, you've got architectural problems with the app itself.
Question. If you are importing data from Excel into SQL server. Would it be easier to use access as a front-end to SQL server? Or would it be easier to import data directly from Excel into SQL
Either way would work just fine. If you have no other need for Access, that would seem like an unnecessary, extra step. Straight from Excel to any relational database is pretty easy with a trivial script to generate the SQL from the spreadsheet file.
I didn't mean to post that yet I still had more to say... I actually do have a question on the topic. You spoke about the robustness of SQL Server and its many different configuration options and maintenance requirements. In my opinion, for small businesses this is a lot of work for someone that isn't really necessary, considering I haven't ever had any issues working just in Access. Is there any benefit for them to upgrade to SQL Server? Also, you mentioned that SQL Server isn't an application development tool, so at my novice level I'm wondering what other front-end development tools are out there? I also really wanted to thank you for the information too I really learned a lot
So yes, SQL Server offers a lot of knobs and dials for people to mess with. But it is important to remember that the defaults are still exceptionally robust. If you are an SMB, you can just install and use it as it is. And just by switching to MS SQL Server over JetDB (the Access native DB) you get benefits in performance, standardization, scalability, and reliability. Plus almost all backup tools can automatically work with SQL Server to take reliable backups, whereas with JetDB, you have a bit of risk in backups unless you kick everyone out while backing up.
SQL Server actually has a lot more development options than Access does. Access itself is a dev tool that is meant to be used with SQL Server, using it with JetDB is really intended just for the developer himself to do and to switch to SQL Server for production use (keep in mind that SQL Server Express is free, and can be run on Linux for free, too, so there is zero hint of a required cost involved.)
But if you want to move beyond Access, which isn't strictly necessary, SQL Server is a "sky's the limit" kind of tool. Whether you look at other "build apps for me" tools that are "Access-like", or go to a full development stack is up to you (Access is pretty good, not many direct competitors exist.) Most people when looking beyond Access move into full development using a language like PHP or C# and simple talk to MS SQL Server. This approach is the "next step" because it moves to you essentially unlimited power and flexibility. And even lets you break Access' lock in to JetDB or MS SQL Server and lets you choose other RDBMS tools like MariaDB, PostgreSQL, Sybase, or even move to NoSQL options like MongoDB, REDIS, Casandra, and so forth. But the leap is pretty big.
So two steps... Access + JetDB -> Access + MS SQL Server -> Full Language + Any Database You Want
The biggest advantages of moving beyond MS SQL Server is that many are free and can scale to truly epic levels. So many companies save literally millions on deployments by making that transition.
Saved me 8 hours of rabbit hole googling
Awesome.
i haven and desktop application using acces as data base, but data get loss, sometimes consult are not been completed, i am going to change to sql
Yes, that's a good idea. Most of the time if you see dataloss in something like that, it is caused by using the shared access file database (AccessDB / JetDB) rather than SQL Server behind the scenes.
Nicely explained sir
Thanks!
thank you
You're welcome
Hi Thank You For the video Love it, am im making a project and im using visual basic 2019 what database software can i use mySQL or MS access?
VB is a full featured language. It can use any database that you want.
PostgreSQL and MariaDB should be your first points of investigation for a green field project requiring relational data.
I'm an Access developer for small businesses as a side job and hobby (usually companies I work in). I don't really know what the overall market share is like for Access, but I think it's used pretty heavily in small businesses. About 10 years ago I read somewhere that Access is the most heavily used "database program" around, but from what I just learned in this video that makes sense because it must be used as the front-end for many different database servers because it is an excellent front-end development tool. In my experience I haven't had any issues with Access bogging down for concurrent users or data storage limitations.
Ten year ago Access was a big more common. I consult heavily in the SMB and while everyone "buys a copy" because it comes free with O365 subscriptions, I actually never run into anyone using it. The cost of deploying Access apps can be very high as you have to license Access for every user of it, and they have to run on Windows (or in some cases, macOS) it can be a bit limiting today as lots of companies use other things, too (ChromeOS, Ubuntu, Fedora, etc.)
Calling Access a "database program" is tricky. People use the term database pretty loosely. Access itself is neither a database nor a database program, it's a front end dev tool in the MS Office suite. It uses databases, but so does Excel, and Excel is obviously used 10,000% (real number) more than Access. So just within MS Office, Access isn't the leading database anything.
Access is used in the SMB, and that's really the only place. Once you have a company of any size, Access is too limiting, and other options are too common. In an SMB, accessing traditional development resources can be very difficult. But in the enterprise, whipping up the same kind of stuff that Access can do is just as fast or faster for developers to do in code. And then you get more options, flexibility, growth potential.
To combat this, MS created the ability to use Access to build web apps that are then hosted on Sharepoint. This forces you out of Access completely, but you still use it to create the apps. But the apps are converted to C# with web forms, hosted on Sharepoint, and connect exclusively to MS SQL Server. But I've not seen that be super popular as it is still expensive and rather complex.
@@samit8178 were can I contact you,have few questions
Hi sir do they both use same sql language
The purpose of Access is to avoid SQL. SQL Server uses T- SQL. JetDB the default sometimes called AccessDB used Jet SQL. They are both SQL languages but not identical.
Microsoft even thinks it's an Access database! Right-click on the desktop or in a folder, select "New" from the context menu, and the top item is "Microsoft Access Database". WHEN WILL THE INSANITY END??
So... yeah, about that, lol. This is one of those moments where Microsoft obviously knows better, but they think that all of this stuff is "too hard" for end users to know the truth, so they intentionally use incorrect terms with the intent of making it "easy". And, for someone who doesn't even know what a database is, it does. But to someone who does know what a database is, it is insanely confusing when they use the term incorrectly. They do the same stuff with Hyper-V, they actually have no term for the physical machine, but call one of the virtual machines the physical machine... how confusing is that?
access : app building platform (modular)
jet db !!?
sql server : rdbms
JetDB is what is known as a "database engine", sometimes just called a "database", but technically a database is the set of data read by the database engine. In the open source world, JetDB is mostly closely comparable to SQLite. If you look at MySQL, an RDBMS, it uses database engines under the hood. So for example, one of the database engines of MySQL is InnoDB. One of the database engines of MongoDB is WiredTiger. An RDBMS is a management layer on top of a database engine. You always have to have a database engine, that's what touches the data file. But the DBMS is optional.
why you call people "confused" ? You underestimate people :(
No le entiendo nada primero voy a recibir clases de inglés
okie dokie
Complete nonsense. From start to finish. This person has no idea what access is. I developed databases for 20 years in Access for small companies from single user to 50 concurrent users in the dB all day long.
About the only thing this video says that is true is that beyond some point hosting the data in a server is required. The breaking point can be file size for the data storage or the number of users.
In any event, keep looking beyond this video for what access is. This person is clueless.
So you say I'm all wrong, yet you never provide any clarity other than "bragging" that you worked with this technology for 20 years. Is there something in the video actually incorrect that you'd like to point out? Do you have something constructive to add? I can only assume that the info is actually correct given the lack of contrary information.
And yes, I've worked with Access for more than 20 years, but I also work with lots of other things and understand how it compares.
Thank you
You're welcome