Similarities / differences b/w Access and SQL Server Express

[ryguy7272]

I am just starting to learn SQL Server (express version). I have been using
Access for about 10 years, very light for several years early on, but VERY
heavy in the past year. Now, I am pretty comfortable working with all things
in Access, including VBA.

As I mentioned, I am trying to learn SQL Server, and I am wondering just how
steep the learning curve will be. I Googled around for a bit, and found out
that the two are somewhat similar. One web site described SQL as Access,
split, with the Linked Table Manager Wizards, so that you have two databases,
one containing the tables and one containing queries, forms, reports, macros,
modules and linked tables. I’m perfectly fine with this. The site also
said, with Access-SQL Server, the architecture can be identical, the tables
are in a SQL Server database and all the other objects are in an Access
application. Finally, I found out that, in SQL Server a lot of the
processing can take place on the computer (server) running the back end. This
can be critical across a network. For example, if you run an Access query
that selects one record from a dynaset of 1,000,000 records all of the
records have to come across the network because the Access logic to select
the records is running on the client computer. In SQL Server the selection
logic can run on the server computer and only the one selected record has to
travel across the network back to the client computer.

So, my question is as follows: just how similar, or how different are the
two? If a person knows the nuts and bolts of Access, is it easy to learn SQL
Server? What are the advantages of learning SQL Server, over Access? Do you
use Access as a FE and SQL Server as a BE? Does .NET or Excel work with SQL
Server? Does SQL Server integrate well with web browsers? I used to use ASP
to create web queries for an Access DB that I managed. Would it have been
easier to use SQL Server? I don’t want all technical details (saw lots of
stuff on the web). I am just looking for a few personal experiences of
people who crossed over from Access-world to SQL Server-world.

Thanks so much,
Ryan---

 

[Tony Toews [MVP]]

One web site described SQL as Access,
split, with the Linked Table Manager Wizards, so that you have two databases,
one containing the tables and one containing queries, forms, reports, macros,
modules and linked tables.

Simplistic but essentially correct.

I’m perfectly fine with this. The site also
said, with Access-SQL Server, the architecture can be identical, the tables
are in a SQL Server database and all the other objects are in an Access
application. Finally, I found out that, in SQL Server a lot of the
processing can take place on the computer (server) running the back end.
Correct.

This
can be critical across a network. For example, if you run an Access query
that selects one record from a dynaset of 1,000,000 records all of the
records have to come across the network because the Access logic to select
the records is running on the client computer.

This is absolutely wrong. If the records are indexed, including the
selection criteria and sorting then only the index pages and MDB file
pages required travel across the network. Proper indexing is required
to get performance but this is true of SQL Server as well.

In SQL Server the selection
logic can run on the server computer and only the one selected record has to
travel across the network back to the client computer.

Now one performance advantage is that a given record in Access might
require, just guessing, 10 index pages and one data page to come down
from the server. SQL Server would only send the one record. So
definitely faster. However this may not be noticeable in your
environment. That is will the user notice 1.2 seconds vs 1.1
seconds.

So, my question is as follows: just how similar, or how different are the
two? If a person knows the nuts and bolts of Access, is it easy to learn SQL
Server? What are the advantages of learning SQL Server, over Access?

You can still use Access, or other development environments, for the
users to use forms and reports but instead the data goes in SQL
Server. The advantages of learning SQL Server would be more career
paths for you in the future.

SQL Server is better for more than 30 or 50 users, if the data is such
that it can't be rekeyed such as in a call centre environment or other
similar factors.

Easy to learn SQL Server? Yeah, it's not too bad. But, like Access,
you'll always be learning something new.

Do you
use Access as a FE and SQL Server as a BE?
Correct.

Does .NET or Excel work with SQL Server?
Yes.

Does SQL Server integrate well with web browsers?

Better than Access if you have any volume of writes or updates to the
website.

I used to use ASP
to create web queries for an Access DB that I managed. Would it have been
easier to use SQL Server?

Easier no. Probably about the same as a few queries would've needed
tweaking and connection strings changed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Albert D. Kallal]

One web site described SQL as Access,
split, with the Linked Table Manager Wizards, so that you have two
databases,
one containing the tables and one containing queries, forms, reports,
macros,
modules and linked tables. I'm perfectly fine with this.

Keep in mind you don't wanna be confused about SQL server vs that of
ms-access.

MS access is not the database system, it is the "jet" engine that is the
datbase.

The reason why that comparison to SQL server is very difficult is because in
SQL server you cannot build a form, you cannot build a report, you cannot
build combo boxes or list boxes etc.

So we're talking about extremely different tools here. One would not
normally
make a comparison between VB6 and SQL server. VB6 is a developer stool that
led to build an application, you might read an application in VB6 that
connects to the jet database engine, or perhaps SQL server express.

You don't develop applications in SQL server, but you most surly do develop
applications in MS access.

So, we're really talking about two completely different things that should
not be compared all. if you're going to make comparisons then you'd be
comparing the "JET" database engine with sql server. in this case there both
data engines that don't allow you to build forms are reports.

So you really can't compare access to SQL server because they are completely
different tools. ms-access is a development tool and system that lets you
build an applicaon + interface to some database engine. That database engine
can be oracle, MySql, sql-server or the built in engine called JET.

The site also
said, with Access-SQL Server, the architecture can be identical, the
tables
are in a SQL Server database and all the other objects are in an Access
application.

Sure, that's sounds good. Over the years i assume you learned to split your
applications? In this case you'll have a front end that was linked tables to
a backend mdb file. You can also take that front end and link the tables to
SQL server now. So there's not a lot of changes that need to occur in your
front end.

Finally, I found out that, in SQL Server a lot of the
processing can take place on the computer (server) running the back end.
This
can be critical across a network. For example, if you run an Access query
that selects one record from a dynaset of 1,000,000 records all of the
records have to come across the network because the Access logic to select
the records is running on the client computer.

The above is it often quoted in scene but 100% incorret. MS access does not
read and load all records across the network if it does not need to.

Ask yourself the following question:

When you load an access database do all the records get read into memory?
(no, they do not).

When you ask access to retrieve one record from the hard disk, do all
reocrds get read from the disk drive? (no). MS access does not know or care
if the drive is "c:\" on your computer or another computer down the hallway.
Access does not "know" or "care" where that disk drive is.

If Access cannot use an index on the requested record retrieved from drive
"c", then access has to read all the records into memory (ram). In this case
the whole table will be loaded even when using drive c: (your local drive).
In that case if you move that back end to a network share, then once again
since it's reading the whole table, then the whole table will come accross
the network.

However in the case when access can use an index, then it does NOT read in
the whole table into memory and access only retrieves the one record.
Changing the location of drive C. to some shared drive in your network does
not change this behavior for access. MS access views that network drive as a
simple disk drive drive and sends commands to read that drive across the
network. The same commands used to retrieve the one record from a local
table are the SAME commands that are sent over the network to retrieve that
one reocrd. The deciding factor here has nothing to do with the network and
has everything to if access can use an index to read the one reocrd into
memory. If access can advoid reading the whole table in memory from drive C,
then it will most certainly do the same thing if you're reading the same
data from a network drive.

So, my question is as follows: just how similar, or how different are the
two? If a person knows the nuts and bolts of Access, is it easy to learn
SQL
Server?

As mentioned they are completely different animals. Everything you learned
and used in access now will not really change. I mean if you plan to use
SQL server perhaps do web development for the front end part of the user
interface, then you're not using access anymore at all. I don't think you're
hinting you're suggesting that you're about to drop MS access here, but I
think you're just considering to use a different database engine then the
built in jet engine.


So everything you've learned access will be there in the same during the
development process if you're going to continue to use access as the
development tools and system here. The only new things to learn is how to
manage and create tables on SQL server as opposed to creating the tables in
access/jet back end.

What are the advantages of learning SQL Server, over Access? Do
you
use Access as a FE and SQL Server as a BE?

Sure, that sounds about right. Rememember that many developers use VB6, or
even VB.net or even ASP to connect to a "jet" mdb file as their back end. So
using "jet" as your database engine was never restricted to just being used
wiht MS access as the front end.

Does .NET or Excel work with
SQL
Server?
Yes.

Does SQL Server integrate well with web browsers?

It's not a question integrating well, however sql server scales a lot better
than using "JET" as a database engine. So once again some web developers
actually do use "JET" for their backend database engine. However there is
little advantage to using JET because you're not really using any the
features of MS access UI or development part.

SQL server is superior to JET in this regards because the connectivity
options for SQL server are not limited to a file connection, but you can use
an IP address (socket connection). Since you don't get to use any of the
user interface part of access when using "JET", then most people just use
SQL server because there are additional scalability issues that make it work
better with web based applications.

I used to use
ASP
to create web queries for an Access DB that I managed. Would it have been
easier to use SQL Server?

You were never really using MS access on that web server, but only placing a
mdb file on that server, and then using connection strings on the web server
to that "jet" database. The advantage of using SQL server means you can use
a lot more tools to modify create and change the table structure right on
the web server without having to transfer files back and forth to your local
computer to modify the JET mdb file for example .

So likely one of the most major vantage's of using SQL server on the web
server side, is you can modified and maintain the tables somewhat easier "in
place" on that server and not have to transfer some file back and forth to
another computer that actually has a graphical user interface called
"ms-access" to modify the "jet" database. In fact the SQL server management
tools actually allow you to connect over the Internet directly to that SQL
server running on your web server. So you don't even have to transfer
anything that all, and you can still use a rich user interface called a
visual studio express tools for sql server that work "over" the itnernet.

MS access never allowed to you to connect to a data base over the Internet,
the SQL server tools do allow this. So, you can use your rich windows
interface and the sql server desktop tools to manage that sql server
remotely. This is likely the number one reason why it's better to choose SQL
server for your web system if we were to ignore the issues of scalability
(sql allows a greater number of users for a web site).

The learning curve to get up and running is not steep at all. However, like
any complicated server system there's a lot to learn about SQL server in
terms of security and all kinds of other features that sql server has.
However for just creating some tables like you do in access, you can be up
and running in a few hours your time.

The reason why SQL server can be difficult to learn is because it has so
many more features to deal with. And, sql server also supports a server-side
codeing lanuage called t-sql.

The additional learning occurs because to get a maximum performance out of
your applications you want to ensure as much processing and work load is
done on the server side because then that prevents data from being sent over
the network to your local computer for proceessing. (in your network
connection tends to be a real bottleneck in most applications).

 

[Tom Wickerath]

Hi Ryan,

I haven't crossed over yet, but I can answer some of your questions....

First, this statement is just plain incorrect:

For example, if you run an Access query
that selects one record from a dynaset of 1,000,000 records all of the
records have to come across the network because the Access logic to select
the records is running on the client computer.

If you use proper indexing, you can avoid full table scans in most cases.
See the sections of my Multiuser Applications paper subtitled "Use indexes"
and "Use JETSHOWPLAN" for more details:

http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Do you use Access as a FE and SQL Server as a BE?

Certainly. Pick up a copy of the book "Microsoft Access Developer's Guide to
SQL Server", written by Mary Chipman and Andy Baron (SAMS Publishing). Here
is a link to the book at Amazon.com:

http://www.amazon.com/dp/0672319446

Does .NET or Excel work with SQL Server?

Yes to both questions.

Does SQL Server integrate well with web browsers?

I believe it would need to integrate with a web server, not a web browser.
Many web sites are created with dynamic capabilities, which are driven by SQL
Server.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Fred]

My main qualification is that I've done both and only know 1/20th of what
these other guys know and so can give the mere mortal level viewpoint.

SQL doesn't replace Access, it just sort of replaces the "back end" of
Access. So, you either have to use Access as a "front end", or else learn
other software products or programming to do things like createing reports
and forms.

In short, SQL is more more powerful for heavy duty applications, but takes
more expertise and more work / man-hours to use. My advice: if you NEED to
use SQL, or if you WANT to use SQL (to learn etc.) do it, use it; otherwise
not.

 

[David W. Fenton]

Does SQL Server integrate well with web browsers?

No. There is not database that integrates with a web browser
(except, perhaps, the one that Firefox uses for its bookmarks, i.e.,
SQLLite). Web browsers cannot connect directly to a database of any
kind. They can only load web pages and other web content. There has
to be an application running on the web server in between your web
browser and the database, and that application provides the
connectivity and interface to the database.

This is structurally no different than an Access front end to a Jet
or SQL Server back end. The Access application is like the web
browser. The front-end MDB with forms/reports/modules/etc. is like
the application running on the web server (e.g., ASP or PHP), and
the back end is the back end, accessed via the application logic.

SQL Server is a better back end for websites than Jet because it is
designed to support more connections simultaneously than Jet. In a
high-volume website, Jet could quickly fall over. In a low-volume
website, it could do just fine, particularly a read-only site.
Another issue is that Jet is not multi-threaded, whereas SQL Server
is, so that can make a huge difference in terms of server-side
memory usage and reliability.

I have never built a website on top of Jet because I refuse to use
Windows-based web hosting, as I feel that IIS is not a secure enough
web server. I do all my web development on PHP running on Apache
with MySQL as the database. On my local PC this is all running on
Windows, whereas on my web hosts, it's running on Linux. Jet cannot
be used on Linux without using unreliable reverse-engineered data
access technologies, and even then, I don't like the idea of storing
a Jet MDB on a non-Windows file system. So, for me, Jet has never
been a viable option.

That said, I did set up a little address book application for one of
my clients that ran Apache/Cold Fusion on a desktop that had the
Access application on it (MDB back end), and could serve up a search
page to others on the client's peer-to-peer network. It worked just
fine because it was read-only and had only a half dozen users.

But that was almost 10 years ago and is no longer in use. I could
easily do the same thing again (though I'd use PHP instead of CF) if
a client needed it, but the need hasn't come up. Very few of my
clients have apps that are used by one or two people that have
information in them that a wider audience would find useful, so I've
never had the need to do it again.

For a public website, I'd never use Jet, though. On the other hand,
Michael Kaplan's http://trigeminal.com is running with a Jet back
end (at least, it used to be, according to Michael's own reports),
and at one point was getting (according to Michael) 100K hits per
day and was holding up just fine. Those hits were all read-only, of
course, and so that put it within the realm of what Jet could do
reliably.

But I doubt there are very many such websites out there. Given that
more and more people are managing their websites with content
management software and blogging software that come with builtin
databases (usually MySQL), the choice of database for most websites
becomes one where there is no choice. You decide on your content
management platform and that determines the database you will be
using.

 

[James A. Fortune]

I am just starting to learn SQL Server (express version). I have been using
Access for about 10 years, very light for several years early on, but VERY
heavy in the past year. Now, I am pretty comfortable working with all things
in Access, including VBA.

As I mentioned, I am trying to learn SQL Server, and I am wondering just how
steep the learning curve will be. I Googled around for a bit, and found out
that the two are somewhat similar. One web site described SQL as Access,
split, with the Linked Table Manager Wizards, so that you have two databases,
one containing the tables and one containing queries, forms, reports, macros,
modules and linked tables. I’m perfectly fine with this. The site also
said, with Access-SQL Server, the architecture can be identical, the tables
are in a SQL Server database and all the other objects are in an Access
application. Finally, I found out that, in SQL Server a lot of the
processing can take place on the computer (server) running the back end. This
can be critical across a network. For example, if you run an Access query
that selects one record from a dynaset of 1,000,000 records all of the
records have to come across the network because the Access logic to select
the records is running on the client computer. In SQL Server the selection
logic can run on the server computer and only the one selected record has to
travel across the network back to the client computer.

So, my question is as follows: just how similar, or how different are the
two? If a person knows the nuts and bolts of Access, is it easy to learn SQL
Server? What are the advantages of learning SQL Server, over Access? Do you
use Access as a FE and SQL Server as a BE? Does .NET or Excel work with SQL
Server? Does SQL Server integrate well with web browsers? I used to use ASP
to create web queries for an Access DB that I managed. Would it have been
easier to use SQL Server? I don’t want all technical details (saw lots of
stuff on the web). I am just looking for a few personal experiences of
people who crossed over from Access-world to SQL Server-world.

Thanks so much,
Ryan---

I think you've been given good advice. The theory about why Microsoft
seems to throttle JET in nearby threads seems logical enough. Ignore
the following speculation on the theoretical underpinnings of JET if
you'd like, but I'm still rolling with the "Maybe JET doesn't really
exist" theory :

http://groups.google.com/group/microsoft.public.access/msg/73676b210f71b2d3

As far as supporting replication in the future, it seems clear that JET
replication will not be used as a paradigm for anything else for
whatever reason. That makes its future uncertain.

Letting a powerful server do a lot of the data retrieval work can be
beneficial, but personally, I like doing any intensive calculations on
the local copy of Access once that server has successfully and
efficiently pushed out only the raw data I need.

In spite of the danger of looking at data through Access colored lenses,
I found that the database concepts used in Access carried over well to
SQL Server. Note that because of the large number of concurrent edits
in the Access environment I support, by necessity I had to use a large
number of unbound forms. Not thinking in terms of "bound" data all the
time made it easier for me to adapt to using SQL Server behind ASP. But
even using "bound" data prepares you for the idea of data binding found
in, say, Windows Communication Foundation (WCF) with it's ABC's
(Address, Binding, Contract). Once you are aware that coming up to
speed with SQL Server will take more time than coming up to speed with
JET you just have to decide when to go for it.

For me, that moment was during the year 2000 for an e-commerce
application. I was able to find nearly all the information I needed to
transition from JET to SQL Server online. I had to get creative in a
few spots, but I knew beforehand where those spots were likely to be.
One problem I had was that the script I discovered on the internet to
allow people to contact the company by email using an ASP page, although
it worked perfectly, could also allow third parties to send anonymous
email! The tech people at the ISP, who BTW got to see how much nicer
the vbscript was than the CGI they were using with their web customers,
managed to notice the potential problem with the contact page before it
became a problem. All in all, it was a positive learning experience.
Plus, I got to learn some ADODB techniques that Microsoft assured would
be the wave of the future . Before I took on the task, two large
software companies had been unsuccessful at creating the same application.

If my experiments with Access using .NET work out -- see:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/6e86b7ff8d3b7894#

then using something like Windows Presentation Foundation (WPF) in a
browser with WCF data binding to a service with a SQL Server backend
starts looking much better than using ASP or ASP.NET with a SQL Server
backend because, frankly, getting the look and behavior you want in a
browser is much harder than getting the look and behavior you want using
Access. I will have to do a lot of experimentation with other
technologies to be able to evaluate the pros and cons of .NET and to be
able to make decisions about when and where .NET should be used. I
don't have Microsoft's luxury of having that rare combination of rich,
yet naive customers who don't mind someone coming in and imposing a
particular paradigm . Actually, I do..., in a way..., to a certain
point..., but I like to think that I make the choices in a more
objective way.

James A. Fortune
(e-mail address removed)

LARRY, HAPPY AS - Completely happy.

-- Dictionary of Australian Slang, Second Edition, Sidney J. Baker,
1943 (Price: Three shillings & sixpence)

 

[ryguy7272]

Awesome! Thanks everyone!
I'm off to the bookstore, for some literature on SQL Server.

Ryan---