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