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

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


John wrote:

John wrote:
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.

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.

--
Terry


"John" wrote:

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/Blo...842A!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.Execute Reader(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.







"John" <John (AT) nospam (DOT) infovis.co.uk> wrote

NGP03.phx.gbl...

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)
-----------------------------------------------------------------------------------------------------------------------

"Miro" <mironagy (AT) beero (DOT) net> wrote

Here's the exact code:


public IDataReader CustomersGetAllReader()

{

//return
Microsoft.ApplicationBlocks.Data.SqlHelper.Execute Reader(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!







"sloan" <sloan (AT) ipass (DOT) net> wrote

"John" <John (AT) nospam (DOT) infovis.co.uk> wrote in
news:e18BJ3mFIHA.280 (AT) TK2MSFTNGP03 (DOT) phx.gbl:

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?

Inline....


"sloan" <sloan (AT) ipass (DOT) net> wrote

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

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 actually the only thing that Access really is - a JET / SQL database
IDE.

"Scott M." <s-mar (AT) nospam (DOT) nospam> schrieb
I think it is important to distinguish. Some people think, using the Jet
requires Access being installed.

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


Armin