Thread Starter

Claudio Pires

Dear Friends,
I would like to identify the advantages of using MS-SQL Server instead of MS-Access in different kinds of applications that uses sql querys (SCADA systems, historical reports, web ASP files, ...). Hope this question helps others' decisions too !
Claudio Pires.

Rajan Dandegaonkar

Hello Claudio,

The biggest disadvantage of MS Access is that it
cant handle data more than 1 GB. Also MS Access
is a Standalone Database and not Networked one.
Condidering its limitations it can only be used
for small Stand alone applications whereas MS-SQL
Server can be used in Large and Networked Applications.

Rajan Dandegaonkar

John from MDL Control Services Ltd

I have done many applications using InTouch to interrogate a MS Access database for Inserting, deleting, modifying, creating etc. Which is easy to get the grasp of, but it is not the one to use if you want to make a big database, we are about to start a new job using a MS-SQL database which I hope will be easy, I will let you know how we get on if you like!
Regards John!

William F. Hullsiek

I have done a quite a bit of process-oriented database work using Ingres, R-Base, MS-Access 1.0, MS-Access 2.0, MS-Access-95 MS-Access 97. Did some Oracle (not Microsoft friendly). Then ended up doing most of our recent development and integration work using SQL-Server 6.5 / 7 and 2000.

MS-Access is great front-end tool. Use it for building prototypes - figuring out requirements. But since it is a file based protocol - it generally fails after around 3-4 connections.
The files generally get corrupted every month - requiring a rebuild/restore.

SQL-Server 6.5 / 7.0 and 2000 generally run without problem, providing you have a good Database administrator. (For that matter you could use Oracle, Informix or Sybase). I have an irrational Negative personal opinion of anything from IBM - so I purposely omit them from a list of database vendors.

I would talk with your application vendors and see what type of local and corporate support they offer. If you have Oracle expertise on staff, I
would go with Oracle. If you have Microsoft expertise on staff I would with Microsoft. If you have both. Then look at the cost.
Microsoft started out very competitively priced - but they changed the rules recently.

From a technical perspective, look at your IDE - or development environment. If you are a VBA environment, then you may have better luck with SQL-Server.

If the question is SQL-Server vs. Access, then I would recommend SQL-Server if you value your data. If the data is secondary, then deal with all of the file corruption issues that MS-Access has to offer.

Personally, I would use MS-Access 2000 as a front-end tool to SQL-Server 2000. Build the forms and reports with MS-Access - but use SQL-Server 2000
to actually store the data.

Like "configurable" or "programmable" products, you get different performance characteristics based on your SQL queries and how you design the database schema. People can make MS-Access work. But it does take time.

Like most "office-products" you get stochastic performance - not deterministic performance. Which mean you have some element of uncertainty
in how the system is going to behave.

Some processes require deterministic performance - in which I would recommend a real-time historian.

However, in most "shop-floor" cases, a standard grade relational database is
good enough.

We selected Microsoft SQL Server because we are in a VBA-6 environment with iFIX. Plus our MES vendor Cimnet had built-in support for SQL-Server. The approach was to keep the architecture straightforward using "off-the-shelf" technologies as opposed to using a open-source. (That is a different thread altogether).

I generally recommend that we send our control engineers to SQL Server Database Administration For Developers. (The course title may have
changed by now). This provides a good overview to the product, plus gets people started in writing code.

From a hand's on perspective the book "Hitchhiker's Guide to Visual basic and SQL Server" By William R. Vaughn is useful.

William F. Hullsiek
We have gone thru this evaluation in the past and there are many reasons to use SQL Server over access. SQL is much more robust, handles much more data, is designed to have redundant servers for better data retention. It also has the ability to roll back a record if it is not correct or transferred properly. You can run stored procedures on the data in the server which saves a lot of communication time and traffic. SQL has a pretty large install base and much third party software is already developed for this platform. These are great reasons to use SQL
Server if your data is considered mission critical, but access will do a wonderful job for small applications and is much less expensive. The access query and display engine are quite adequate for reviewing data, but again I believe it depends on your application.


Steven Keller

I have done a lot of programming with both Access and MS-SQL. If you are going to be logging or updating a lot of information SQL is the only way to go.

An Access database can grow out of control if you update a lot of records. I learned on my first data logging application. I was updating about 100 records every 1sec. or so, after about 48 hours the HDD was full and I needed to do a compress and repair on the database to get it back down to its original size. I found out that if a record is updated some of the old information does not go away until you do a compress/repair.

I now use MSDE, a free version of SQL from Microsoft. It comes with Access 2000 and above, and Microsoft's development software and I believe it can be downloaded from the web for free.
( "": )
MSDE is a stripped down version of SQL, it is limited to 5 users and 2GB of data. Once you get used to using the advanced features (triggers, stored procedures, etc) in SQL you will never want to use Access again. In one application I have a stored procedure that uses COM to pull data out of Think and Do and update a table every second. It also updates tags in TnD with various types of data. Because both apps are on the same computer it is very fast.

A stored procedure is a program that you can write in SQL. It is compiled on the server so it runs much faster than a standard SQL request.

A trigger is part of a table and is 'triggered' when something happens to the table (i.e. a record is inserted, deleted, or updated). It is like a stored procedure that is called when the data in the table is changed or added.

MS-Access vs MS-SQL server.

- Access is limited to 1GB of space, thus when logging real time values you will find that your database will quickly become unmanageable
and even slow. MS-SQL Server is not limited like access, it is efficient and robust, and will not slow down due to large data stores.
- When deleting rows in an access table, the database will not purge this space from memory, thus you will find that even though you delete
records, the database size will not decrease, the only way to do this is to compact the DB, and unfortunately cannot be done when a
connection is open to the database. MS-SQL Server does not have this problem.
- MS-Access is good for front end apps, with SQL linked tables.
- MS-SQL has the advantage of stored procedures and views, making data manipulation faster.
- MS-Access is relatively easy to puchase compared to MS-SQL server.
- Access has limited security features compared with MS-SQL server which is a major down fall.
- Sorting in access is slow, access is generally slow.
- Access does not allow "real time" data links.
- Both have good abundance of help resources on the web etc.

The list goes on.

Paul Rucci

Access 97 had a 1 GB maximum size (uses non-unicode) Access 2000/2002 have a 2 GB max size (uses unicode) Note, however that there is nothing stopping you from having multiple .mdb files that each contain a single huge table, and then linking from your front end to each of your backends.
Also, these file limits assume you are using a JET backend to store the data. Access 2000 comes with MSDE (SQL Server Desktop) or you could always get the full blown version of SQL server for the data and then have virtually unlimited volumes of data (limited more by your hardware than your software).
As far as speed of any operation goes, that is more a function of proper coding, indexing, table design, and planning.

Mark Hill
Dear Jone,
Which kind of project you have done using IN TOUCH to connect with MS Access?I am newer but eager to learn more the mechanism of using
Databace to exchange Data between IN Touch,so how to get further stuff or even free examples?

Regards Zhu