.NET and MS Access

[Ralf J.]

I would like to create an application using VB.NET as the
front end and MS Access as the back end. If I installed
Access Run-Time (using the developer's edition of Access)
on everybody's machine would they be able to open the
reports developed in Access using the .NET interface?
Would I have to make the Access backend a .mde? Would
there be any problems with this type of application
design? Does .Net standard edition come with Crystal
Reports?

 

[Norman Yuan]

If you use Visual Studio.NET to develop database front end, you don't need
Access runtime, unless you need run report created in Access. VS.NET
(professional version or better) comes with CrystalReport.NET (but I do not
know whether VB.NET/VC# standard version come with CrystalReport.NET or
not).

On the other hand, if you'd like to use Access runtime and distribute the
big runtime package, why not simply develop Access front app.

 

[Ralf J.]

-----Original Message-----
If you use Visual Studio.NET to develop database front end, you don't need
Access runtime, unless you need run report created in Access. VS.NET
(professional version or better) comes with

CrystalReport.NET (but I do not

know whether VB.NET/VC# standard version come with CrystalReport.NET or
not).

On the other hand, if you'd like to use Access runtime and distribute the
big runtime package, why not simply develop Access front app.

My main reason for wanting to develop using .NET is

flexibility. We may or may not want to at a later date go
to a more powerfull DB engine such as: SQL Server or
perhaps Oracle. Using .NET should allow us to make a DB
switch more easily. Wouldn't it simply be a matter of
changing some connect code? If we developed completely in
Access and made a DB switch at a later date we would have
to develope a new interface from scratch which, could
delay deployment of any new DB applications. I figure it
would be better to procure .NET, develope apps and be
ready for any DB engine switch that may come at a later
date. In other words: I would rather go through the .NET
learning curve (I already am proficient with Access) ASAP
than develope completely in Access, have "the powers that
be" switch DB engines on me and then be forced to go
through a .NET learning curve and a SQL Server learning
curve at the same time (or other DB engine). Which would
really delay app development..

What do you think?? Any advice??

 

[Norman Yuan]

I basically agree with you that doing it in .NET will give you more
flexibility than doing it in Access, if you have the resources (.NET skill
and time available). In term of report, you either go for third party report
tool for .NET, or go with VS.NET's CrystalReport.NET.

But to make a common data access layer for different data store is no easy
task in NET. In .NET, there are different data providers (organised in
different namespaces), optimized for different database. For SQL Server, you
use System.Data.SqlClient, for Access, you use System.Data.Oledb, for
Oracle, MS and Oracle have their own stuff, and for others, you may use
ODBC.NET provider. I saw someone trying to make a common data access layer,
using Oledb or ODBC namespace, and I do not know the result, good? so so...?

 

[Albert D. Kallal]

flexibility. We may or may not want to at a later date go
to a more powerfull DB engine such as: SQL Server or
perhaps Oracle. Using .NET should allow us to make a DB
switch more easily. Wouldn't it simply be a matter of
changing some connect code?

Sure, but the same applies to ms-access also. Ms-access makes a fine client
to sql server, Oracle, or whatever. I don't see why using .net is going to
make changing the back end datasystem ANY easier at all. If you develop the
ms-access application to work with sql server, or Oracle or whatever, then I
see virtually ZERO time saving to write this thing in .net. In other words,
you might want to use .net, but the argument that it is easer to change the
database engine is nonsense.

If we developed completely in
Access and made a DB switch at a later date we would have
to develope a new interface from scratch which

Why in the world would you have to develop a new interface? If you don't
have
to develop a new interface in .net, the same would apply to ms-access.

If you develop the access application to use, or work with sql server from
the start, then changing to MySql, or whatever engine you use will likely
not be any more work then changing your .net application to use a different
data engine. In theory, you would just change the ODBC connection strings in
ms-access also.

Of course, if you design the .net application to completely hide, or make
the database interface with a nice object that you use for "ALL" database
operations, then yes, I suppose it would be far easier to change the data
engine. However, this ease of change is going to be based on your original
deigns that takes this into account (ie: you isolate all data operations to
a object that you create). However, you could also write a nice class object
in ms-access that isolates most of the database operations also.

You also have to remember that the access runtime can top out at about 160
megs. Normally it is smaller, but it not exactly a light weight install at
all. In fact, it is larger then the whole CLR.

I would say if your plan is to design with a different database engine in
mind, then I would use .net forms, and NOT rely on ms-access at all. You can
most centrally use the JET engine, and thus use the mdb files, but not have
to install ms-access, or the large ms-access runtime.

Either you decide you want to use ms-access as a report writer, or you
don't. However, this is a separate issue from changing the database engine.

So, if your goal here is to not be tied to ms-access, then I would not use
it. You can and most certainly should use JET, but using both .net and
ms-access to avoid using ms-access does not make sense.

 

[Brendan Reynolds \(MVP\)]

Comments in-line ...

--
Brendan Reynolds (MVP)
(e-mail address removed)

I would like to create an application using VB.NET as the
front end and MS Access as the back end. If I installed
Access Run-Time (using the developer's edition of Access)
on everybody's machine would they be able to open the
reports developed in Access using the .NET interface?

Possibly, but you would have to write code to automate Access, and there are
additional issues involved in automating a runtime installation where retail
Access is not present. The KB article at the following URL has the details
....

http://support.microsoft.com/default.aspx?scid=kb;en-us;210111&Product=acc

Would I have to make the Access backend a .mde?

The term 'backend' usually refers to a data file containing only tables, and
changing an MDB to an MDE doesn't do anything to tables or queries. The MDB
that contains your reports could be converted to an MDE if you want to
prevent users from being able to modify the reports or any code behind those
reports. You might want to consider using a separate MDB/MDE for this,
rather than including them in the data MDB, so that you can send out new or
modified reports without overwriting the data file.

Would
there be any problems with this type of application
design? Does .Net standard edition come with Crystal
Reports?

Details of what features are included in each edition of VS.NET 2003 are at
the following URL ...
http://msdn.microsoft.com/vstudio/howtobuy/choosing.aspx

It appears that all editions of Visual Studio .NET include Crystal Reports,
but there is no mention of Crystal Reports in the list for stand-alone
VB.NET (when bought separately from the Visual Studio bundle) so if you were
thinking of buying the stand-alone product, and hoping to use Crystal
Reports, I'd advice double-checking with your software vendor or your local
Microsoft office before parting with your money.