Access Db or SQL server?

[Sam]

I have a project which I have started based on Access Db. How ever I started
considering weather to change to SQL server. I really don't know what would
be the benefit of SQL compared to Access Db. If somebody who knows better
could clear the difference of these methods?

Optimum situation is to have self running application with no need of
Access, but I understood that this is not possible if the basis is designed
with Access. What if the basis is designed with Access, but connected to SQL
server?

 

[Albert D.Kallal]

I have a project which I have started based on Access Db. How ever I
started
considering weather to change to SQL server. I really don't know what
would
be the benefit of SQL compared to Access Db. If somebody who knows better
could clear the difference of these methods?

You are confusing a application development tool like ms-access, and a
database engine like sql server.

A database engine like Oracle, Sql-server, or Sybase is simply a box with a
very good high quality data engine on it.

So, if you choose to use sql server, ALL THAT the server does is run a data
engine, and store data. However, with sql server you can can't build the
user interface. That means with oracle, or sql-server you can't build a
form. In fact, you can't write a report either.

VB, c++ or ms-access is what we call a development tool. So, you might
choose to use sql server, but to built the forms, user interface, reports
and write code, then you must use a development tool. So, you can use sql
server to store your data, but use ms-access or c++ to build the forms,
reports, and build the application.

So, you can't really use ms-access and sql server interchangeability to
build applications. Ms-access is a development tool that lets you CONNECT to
a data engine of your choice. The default engine that ms-access has is a
file share, and it is called JET.

So, while you can choose to use JET, or sql server for the data, JET, or sql
server does NOT have the ability to build forms, reports etc. So, one part
is for storing and retrieving data, and the other part of your application
is the user interface part.

So, keep in mind that sql server does NOT have the user interface part. The
fact that sql server does NOT have the user interface part is both good, and
both bad. It is bad, since you then have to choose a tool to build the
application part with. It is good, since then you are FORCED to choose a
tool to build the application part with!! So, for example, if you are build
a web site, VB, ms-access, or c++ is not going to help you make forms.
However, setting up a web server does need a data store, and thus sql is a
good choice. So, if you had application written in ms-access, and also a web
site, BOTH ms-access, and the web site application can BOTH use the data on
the sql server.

So, the main advantage of sql server over JET is that sql server is designed
to allow many different types of connections to the data engine. For a large
company, the desktop applications, the web site, and even some pda (personal
digital assistant) ALL MAY need to use that data. So, in these cases, sql
server is a good choice to store the data with, since then MANY applications
can consume that data.

So, you can most certainly write an application in ms-access. For you data,
you can use JET (the default engine), or sql server. However, you can't
choose JET, or sql server, and then make the application, as you need some
development tools to make the user interface. That development tool can be
ms-access, or VB, or perhaps you want to make a web site, and thus use ASP.

Optimum situation is to have self running application with no need of
Access

It is not possible to have a application without some application!! You can
certainly purchase the developers edition of ms-access, and that allows you
to create a standard windows package that install like any other
application. This would allow you to package a version of ms-access with
your application, and the end users would NOT have to purchase, or have
ms-access on their computer.

What if the basis is designed with Access, but connected to SQL
server?

If you are going to use sql server, then either the user must already have
sql server installed, or they must install it. Of course, setting up,
installing, and maintain sql server can take a good deal of training. Often,
many companies have a full time database administrators. That persons full
time job is to maintain, and manage their database server. There are new
lightweight editions of sql server, and they requite much less
administration and maintains, but still would have to be installed as a
separate application besides your desktop application.

 

[Sam]

Thanks guys these are very good point of views and advices. Yes actually I
pretty much knew that SQL works only as a "store" and have to be connected to
UI from other source. probably I didn't give such a good picture what I want
to accmplish. Main thing was to know which one (Access Db or SQL) would be
better to connect to form/UI made with Access.

The project I'm working is going to be used with people who doesn't know so
much about computers, so maintaining SQL server would be pretty much
impossible for them. And it's going to be located physically so far that I
cannot be always there to fix problems. So I would hope to accomplish
something that could work pretty long without any false activity.

The project is to be a hotel booking interface with six tables (six next
months). And every month it will make new "future month table". So there is
not so huge amont information in it. There is going to be just one user at
the time and it will be used only locally.

I was considering this self working application because I try to keep the
costs this project causes for the customer pretty low. But I think this
developers edition of ms-access would be pretty good choise.

But still for future consideration and other project it's good to know facts
about SQL possibilities.

 

[John Vinson]

Thanks guys these are very good point of views and advices. Yes actually I
pretty much knew that SQL works only as a "store" and have to be connected to
UI from other source. probably I didn't give such a good picture what I want
to accmplish. Main thing was to know which one (Access Db or SQL) would be
better to connect to form/UI made with Access.

Access works well with either one.

The project I'm working is going to be used with people who doesn't know so
much about computers, so maintaining SQL server would be pretty much
impossible for them. And it's going to be located physically so far that I
cannot be always there to fix problems. So I would hope to accomplish
something that could work pretty long without any false activity.

You can use Citrix or Terminal Server to maintain databases remotely -
I'm routinely doing so for a couple of my clients. But a SQL database
involves MUCH more administrative work (and more difficult work) than
a JET fileshare.

The project is to be a hotel booking interface with six tables (six next
months). And every month it will make new "future month table". So there is
not so huge amont information in it. There is going to be just one user at
the time and it will be used only locally.

WHOA! My normalization module just lit up with red lights like
fireworks! Storing multiple tables of the same structure is EXTREMELY
BAD DESIGN and a recipe for trouble.

If you're storing the same amount of data, you should really consider
storing it in *one* table - even if each table has millions of rows,
storing it in one table will be simpler and easier to maintain than
six tables; and you can use a Query selecting the data in one month
for the purpose you are now using separate tables. And what about a
customer who arrives on August 28 and leaves September 4?

I was considering this self working application because I try to keep the
costs this project causes for the customer pretty low. But I think this
developers edition of ms-access would be pretty good choise.

But still for future consideration and other project it's good to know facts
about SQL possibilities.

Both SQL and JET backends have their place; sometimes the decision may
depend on the developer's experience and comfort level with one or the
other software environment.

John W. Vinson[MVP]

 

[Sam]

Probably you are right about the multiple tables. I just don't have clear
picture of how should I separate months, correct days in month and 100 rooms
that can be booked at anytime half a year forward from current moment. I
would be interested to hear more about that one table design.

I am working on with the booking of days in two tables at the same time. I
won't say it's easy but I have made pretty far already.

 

[John Vinson]

Probably you are right about the multiple tables. I just don't have clear
picture of how should I separate months, correct days in month and 100 rooms
that can be booked at anytime half a year forward from current moment. I
would be interested to hear more about that one table design.

I am working on with the booking of days in two tables at the same time. I
won't say it's easy but I have made pretty far already.

A Booking event has two attributes: a StartDate and a (perhaps
optional) EndDate. What's hard about that!? If you have a booking for
September 30, you put #9/30/2005# in the StartDate; if it's expected
to run until October 4, you put #10/4/2005# in the EndDate. You can
display (or search) the month using the Month([Startdate]) function as
a calculated field, or display the month name by setting the format of
the date field to "mmmm".

The record would also have the RoomNumber (as a link to the table of
rooms) and a CustomerID (as a link to the table of customers, who's
booking it).

If you have a booking for April 6, 2006... same drill; just put that
date in the StartDate field.

If you want to find all bookings for July, simply use a Query with

BETWEEN #7/1/05# AND #7/31/05#

on the StartDate field.

Maybe I'm misunderstanding the business requirement...!

John W. Vinson[MVP]