Access vs vb.net/ado.net

[John]

Hi

What are the advantages of writing an app in vb.net/ado.net as opposed to MS
Access? I need this to sell the idea to the management.

Many Thanks

Regards

 

[Miro]

John this is from my point of view - someone who uses dbf's and msaccess tables.

I started writing some dummy apps teaching myself .net and started into using msaccess dbfs.

A couple weeks in - i pulled the plug and started using sql express. ( switched over ).

1. Its more secure once you set up the database on the server.
2. Ms Access seems to be slowely phased out ( if it ever will is another story ), and anything
new / any new books even, teach on ms sql.
3. MS SQL database has settings within it such as rollbacks and other database features you
might want to use or not. -I havnt gotten to this point - and am far off it.

From a personal point - I am very glad I switched to Learning sql express instead of msaccess.

With programming questions... it seems as well more people here already use the sql db than access,
so getting help with code I write, seems to be more people using the same thing.

I am a newbie, i would wait for some more technical answers from the longs, but I like the
new SQL way better than the msaccess.

It just seems to fit properly with .net as well


Miro

 

[cfps.Christian]

Hi

What are the advantages of writing an app in vb.net/ado.net as opposed to MS
Access? I need this to sell the idea to the management.

Many Thanks

Regards

I don't think Access has the option but I know one of the big things
in SQL now with the SOX laws in effect is there is a way to record who
changed a row, when they changed it, what they changed it from and
what they changed it to. Creates a paper trail for people that are
trying to get in there and rip off the company.

 

[Guest]

While you may not want to jump from Access to SQL server at this point in
time - writing your apps with VB now, will make any furture transition much
easier.
By properly seperating the Data Access Layer (DAL) from the UI, if it became
necessary (or desirable) at some point in time to switch from Access to
something else for any number of reasons (database size, number of concurrent
users etc), the transition would be much easier and cleaner. By doing it
this way now, you will have many more options down the line.

 

[sloan]

Access is two things at the same time. So you gotta distinquish.


There's the database, Access. This is when you put tables in there, like
Employee
Department
JobTitles

That's the database part.

Then there's the ... access as an application development tool.
This is when you start creating forms (like to Add a New Employee) and code
into the access database.


Access (as a database) isn't too bad of an idea. Its ok to store
lightweight data in an Access database.

Access as a application development tool.....My advice is RUN AWAY.
Don't create forms. Don't create business rules (like , a new employee has
to be 18 years of age).
RUN AWAY FROM THIS.
..

So you can create an application ... like a vb.net winforms application or
maybe an asp.net webforms application... and then use Access as a backend
database.

As previously mentioned, you want to be very careful how you do this, and
use a proper DAL library.
A DAL is a class that gives you the data you need, but in a way that if you
ever needed to switch out the backend database, you wouldn't have to recode
the entire application.
This is called NLayered Development.


Go here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

Download the code. Get it running. (This is the 2.0 version, if you have
1.1, then go back to the main page, and there is a 1.1 version as well.
If you don't have a Sql Server database somewhere, that's ok. Because I'll
explain how to swap out for an Access database a little later.


In the CustomerData class... there is a method. Its called the
"CustomerData", because this is my DAL object for accessing customer data.


Find this method:

//The use of IDataReader instead of SqlReader, allows the
DataAccessLayer to remain abstract, and thus
//the backend database can be swapped out to something besides Sql
Server (2000).
public IDataReader CustomersGetAllReader()
{
return
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(m_connectionString,
"dbo.uspCustomerGetAll" , null);
}

Now, that code is coded for using Sql Server.

Notice it returns something called an IDataReader.

Well, it would be just as easy to return an IDataReader, but with getting
data from an access database.



public IDataReader CustomersGetAllReader()
{

String myConn ="Provider=Microsoft.JET.OLEDB.4.0;Data
Source=C:\\Nwind.mdb;"; //naturally, you gotta have a database (mdb)
somewhere...change value here
String myQuery = "Select CustomerID, ContractName , City From
Customers";
//myQuery = "Select CustomerID , ContactName , City from Customers;
Select OrderID, CustomerID, OrderDate,ShippedDate,Freight from
Orders;"//try this, I think it works but not sure

OleDbConnection cn = new OleDbConnection(myConn);
cn.Open();
OleDbCommand cmd = new OleDbCommand(myQuery, cn);
return cmd.ExecuteReader(); // actually you want someting like
return cmd.ExecuteReader(CommandBehavior.Close); or something like that

}


Now, that is crappy code, because I have to connection string hardcoded into
it, but you get the idea.
I'm returning the same type of object, BUT I'm talking to an Access database
instead of Sql Server.


In fact, download my sample, and find a northwind.mdb or a nwind.mdb
somewhere (search harddrive or download), and actually try to replace the
code for CustomersGetAllReader, and see if it works!
If should............



In fact, there are things like the EnterpriseLibrary.Data , which actually
make the backend database even more abstract. But let's not bite off too
much right now.



So give that a try, and you'll see what NLayered development is. And you'll
see how you ~can~ use Access if you want, but use it wisely.
As a lightweight datastore, its not too bad.
And if you develop your code using things like IDataReader, then you'll be
able to code against Access now, but not screw yourself in the future.

Keep in mind I say lightweight. Access is kind of a poor man's database.
It won't perform as well as Sql Server, it won't handle multi users as well
as Sql Server.

...

If it were me though, I'd still try and use Sql Server 2005 Express. and
use "mdf" files.



Access as a lightweight database = OK
Access as a application development tool = RUN AWAY, FAR AWAY.


Read (actually sit down and read) my 2 blog entries, the 2.0 and the 1.1
versions. Read both, as they have some info and links in them to help you
out.







NGP03.phx.gbl...

 

[William Vaughn]

Companies large and small are spending millions moving applications off of
Access/JET. While I use Access, I use it as it's intended (IMHO) as a home
or VERY small business database tool--I use it to maintain the choir roster.
Access forms and reports are generally not transferrable to more serious
paradigms. The JET databases it creates (by default) are not securable, not
saleable and not reliable. I would do some additional research before using
Access/JET in any serious way. Some have made it work but many others have
regretted taking that course.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[sloan]

Here's the exact code:


public IDataReader CustomersGetAllReader()

{

//return
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(m_connectionString,
this.PROC_CUSTOMERS_GET_ALL, null);

String myConn ="Provider=Microsoft.JET.OLEDB.4.0;Data
Source=C:\\Wutemp\\Nwind.mdb;"; //naturally, you gotta have a database (mdb)
somewhere...change value here

String myQuery = "Select [CustomerID],[ContactName],[City] From
[Customers];";

//next lines does not work, Access does not support multi resultsets
//myQuery = "Select [CustomerID],[ContactName],[City] From [Customers];
Select OrderID, CustomerID, OrderDate,ShippedDate,Freight from
Orders;";//try this, I think it works but not sure

OleDbConnection cn = new OleDbConnection(myConn);

cn.Open();

OleDbCommand cmd = new OleDbCommand(myQuery , cn);

return cmd.ExecuteReader(CommandBehavior.CloseConnection ); //


}



Download my sample, replace the code above. Make sure you have a
c:\wutemp\nwind.mdb

and it'll run!

 

[Guest]

What are the advantages of writing an app in vb.net/ado.net as opposed
to MS Access? I need this to sell the idea to the management.

You can still use Access as your backend database - it is fully supported
by .NET.

Access is not necessarily a bad choice for small applications - or
applications you don't want to load too many components onto a user's
computer.

It depends what your doing - do you have more details?

 

[Scott M.]

Inline....

Access is two things at the same time. So you gotta distinquish.

Well, actually no, it's not - so you don't.

There's the database, Access. This is when you put tables in there, like
Employee
Department
JobTitles

No, that's actually a JET database. There is actually no such real thing as
an "Access database". Sure, people call it that, but it's really a JET
database.

That's the database part.

Then there's the ... access as an application development tool.
This is when you start creating forms (like to Add a New Employee) and
code into the access database.

That's actually the only thing that Access really is - a JET / SQL database
IDE.

 

[Armin Zingler]

Well, actually no, it's not - so you don't.

I think it is important to distinguish. Some people think, using the Jet
requires Access being installed.

No, that's actually a JET database. There is actually no such real
thing as an "Access database". Sure, people call it that, but it's
really a JET database.

An MDB file has always been called an "Access database". I'd still call an
MDB file being in Access database format.


Armin

 

[sloan]

I think calling it "Access Database" or "Jet database" may be a little
picky, esp for the original poster who we're trying to help, and not pick at
exact syntaxes.
I'm not going to argue whether you're right or wrong, because ... you are
"right".

And yes, you are right that you don't have to have the program installed to
work with a Jet database. But it makes it much more complicated.
You can create tables through code, and relationships, but my guess is that
most people who are considering using a Jet database, are still at the level
of creating Tables via the GUI of the program, and not via code.

So for the OP, I apologize miscueing on a few of my syntaxes, but I hope my
posts have tried to help your situation.

...

Access...I'm sorry....Jet databases ... are NOT true RDBMS, like Sql Server
or Oracle or etc, etc.

But sometimes they make sense. Or if you plan correctly, you can start
there, but still move up to another database.

 

[John]

Thanks sloan.

Regards

I think calling it "Access Database" or "Jet database" may be a little
picky, esp for the original poster who we're trying to help, and not pick
at exact syntaxes.
I'm not going to argue whether you're right or wrong, because ... you are
"right".

And yes, you are right that you don't have to have the program installed
to work with a Jet database. But it makes it much more complicated.
You can create tables through code, and relationships, but my guess is
that most people who are considering using a Jet database, are still at
the level of creating Tables via the GUI of the program, and not via code.

So for the OP, I apologize miscueing on a few of my syntaxes, but I hope
my posts have tried to help your situation.

..

Access...I'm sorry....Jet databases ... are NOT true RDBMS, like Sql
Server or Oracle or etc, etc.

But sometimes they make sense. Or if you plan correctly, you can start
there, but still move up to another database.

 

[sloan]

The biggest thing is
"Cost of maintenance".

If you plan out a new application decently, the future costs of adding a
feature, making it support more users will be less, then by keep on hacking
onto an existing Access application.

People think software costs are the development costs. WRONG.
The costs are the maintenance costs.

..Net is the now and future of MS development. You don't want to do a VB6
app (from start) ... and you don't want to create an Access Application.

There is much more wealth of knowledge on the internet about .Net
development. This alone is a benefit. When you need to get something to
work, eventually you will find it on the internet with a dotnet application.

If they outvote you, find another job.

 

[Jim Rand]

This is not meant as a slight, but if you don't already know the answer to
this question, you are not ready to sell the idea to management.

Moving to .NET is not trivial. The learning curve is incredibly steep. I
must say, however, the results are well worth the journey.

 

[Cor Ligthert[MVP]]

An MDB file has always been called an "Access database". I'd still call an
MDB file being in Access database format.

However that was not in the thread before you wrote it. There was written
"there is a database, Access" which was nearest to your reply.

Scott is in my idea very correct with his answer. Using Access is more than
using a database, it is a complete enviroment for endusers and developpers
while databases can pretend to be that, however seldom are. Therefore is in
my idea the question not to answer. It relies on what the company wants with
development.

Cor

 

[Armin Zingler]

However that was not in the thread before you wrote it. There was
written "there is a database, Access" which was nearest to your
reply.

Scott is in my idea very correct with his answer. Using Access is
more than using a database, it is a complete enviroment for endusers
and developpers while databases can pretend to be that, however
seldom are. Therefore is in my idea the question not to answer. It
relies on what the company wants with development.

I don't understand what you wanna say. It was only about the term "jet
database" vs "access database". Call it whatever you want. And I agreed with
"sloan" because he distinguishes between the database file (mdb) and the
database application (Access). That's all I said.


Armin

 

[Cor Ligthert[MVP]]

I don't understand what you wanna say. It was only about the term "jet

database" vs "access database". Call it whatever you want. And I agreed
with "sloan" because he distinguishes between the database file (mdb) and
the database application (Access). That's all I said.

And all I said is that Scott M is in my idea very correct in his response.

Cor