SQL or no SQL that is the question

[Anonymous]

I would like to know what is the benefit of using SQL  for the databases instead of the default Access. I would like to know what can you do using SQL that you can't do with Access.  Are there any Gottas with using SQL?

Thanks in advance for the responses.

[dougmcalexander · Accepted solution]

SQL should appear to retrieve records faster when you share the same database over a network.  One trade-off is that you will not be able to set up a secondary Lookup Database for your project.  Some of my clients want a main database with only their preferred vendor parts data, to force consistency.  But they want the ability to toggle to a secondary database that is fully loaded with all vendor parts, just in case they have a special order that requires a non-preferred part. 

Of course whether SQL or Access you still edit via the Catalog Browser.  I don't recommend editing the database directly with MS Access, unless you are intimately familiar with how to do so and not damage the tables.  SQL cannot be edited with MS Access, so it will be much more difficult for anyone to "hack" your database.

My personal experience is that computers and routers are so fast today that the speed increase from SQL can be barely noticeable.  If your network infrastructure is not state-of-the-art, SQL may show more of an increase in the response time of a part number Lookup.  Because I teach the software, I am set up both ways.  I see no discernible difference in seek time on my teaching laptop, but I'm not being routed over a network in that scenario.

[Anonymous · Accepted solution]

I accepted this as a solution but I would also like to hear from other users feedback too.

Thanks everyone

[dgorsman · Accepted solution]

In general, SQL Server (even the free Express version) can handle many more simultaneous connections than an Access file-based database.  It is also faster for operations on much larger data sets.  However, very few CAD offices are going to see the conditions where those benefits come into play - or at least, where the benefits outweigh the additional administrative overhead.

The more likely situation is where another function in the office is using (or looking at using) SQL Server, and CAD users can make use of the existing infrastructure and support.  Unlike a lot of other functions, it's best for CAD systems to be local to the SQL Server instance, whether it's a rack-mounted server or a spare-box-in-a-corner.  CAD systems are much less tolerant of the latency you would see if the SQL Server instance is in a remote location, compared to say, a cost-lookup database for a procurement department.

[TRLitsey · Accepted solution]

Hi there,

I like the replies here.  This is just a little article that does a compare outside the CAD environment.

Thank you

https://database.guide/microsoft-access-vs-sql-server/

[rhesusminus · Accepted solution]

I'd just like to add that with SQL ,you can't change the name of the fields, like you can do in access.
That is... Instead of having a column named "USER 1", you can give it the name "PRICE".

personally, I recommend that people stay with access database as it's more versatile, for now.