Database Development - Access or MSDE?

[Mitchell Vincent]

I would like to start a discussion of what everyone is using as an
embedded application database with .NET - specifically VB.NET.

The MSDE package is *far* to huge to be of any real use at over 70 megs,
so I'm left with Access.

Access has had a bad rep in the past but I'm wondering if recent
versions (like, the last 5 years) have addressed the issues that it had
previously. I remember speed and relability with multiple users were its
two shortcomings.

I would also like to know if anyone has any VB.NET code to *create*
Access databases and add DSNs programatically so that I don't have to
rely on the user to set everything up correctly.

Thanks!

--
- Mitchell Vincent

[scorpion53061]

Mitchell,

I use MS Access all the time in apps. I find if properly managed(i.e. No
nulls, care taken with concurrency issues etc), Access is a terrific
tool.

The 10 user limitation that is generally recommended with Access is
probably true. However, I had an app that at the time had less than 10
users and ended up with about 25. Never had a day's problem - until the
users accidentally deleted the database. :)

However, I never use it for authentication as it is pretty useless in
that regards.

"Mitchell Vincent" <mi************ **@gmail.com> wrote in message
news:#E******** ******@TK2MSFTN GP14.phx.gbl:

I would like to start a discussion of what everyone is using as an
embedded application database with .NET - specifically VB.NET.

The MSDE package is *far* to huge to be of any real use at over 70 megs,

so I'm left with Access.

Access has had a bad rep in the past but I'm wondering if recent
versions (like, the last 5 years) have addressed the issues that it had
previously. I remember speed and relability with multiple users were its

two shortcomings.

I would also like to know if anyone has any VB.NET code to *create*
Access databases and add DSNs programatically so that I don't have to
rely on the user to set everything up correctly.

Thanks!

--
- Mitchell Vincent

[Mitchell Vincent]

Thanks for the reply!

What kind of care should be taken with regard to concurrency issues?

scorpion53061 wrote:

Mitchell,

I use MS Access all the time in apps. I find if properly managed(i.e. No
nulls, care taken with concurrency issues etc), Access is a terrific
tool.

The 10 user limitation that is generally recommended with Access is
probably true. However, I had an app that at the time had less than 10
users and ended up with about 25. Never had a day's problem - until the
users accidentally deleted the database. :)

However, I never use it for authentication as it is pretty useless in
that regards.

[Barret Bonden]

Access is very robust now; I too hated it in the mid 90's, and now use and
like it; have had 5 to 7 users pound away on my apps , all happy ...
Very straightforward code, fast development, none of the silly OO
complexity found in VB .net

"Mitchell Vincent" <mi************ **@gmail.com> wrote in message
news:#E******** ******@TK2MSFTN GP14.phx.gbl...

I would like to start a discussion of what everyone is using as an
embedded application database with .NET - specifically VB.NET.

The MSDE package is *far* to huge to be of any real use at over 70 megs,
so I'm left with Access.

Access has had a bad rep in the past but I'm wondering if recent
versions (like, the last 5 years) have addressed the issues that it had
previously. I remember speed and relability with multiple users were its
two shortcomings.

I would also like to know if anyone has any VB.NET code to *create*
Access databases and add DSNs programatically so that I don't have to
rely on the user to set everything up correctly.

Thanks!

--
- Mitchell Vincent

[Bonj]

> The MSDE package is *far* to huge to be of any real use at over 70 megs,

so I'm left with Access.
Is it? I've got an installer for it called MSDE2000A.exe and it's 43,259KB...

Access has had a bad rep in the past but I'm wondering if recent
versions (like, the last 5 years) have addressed the issues that it had
previously. I remember speed and relability with multiple users were its
two shortcomings.
In my experience, it's very good for any database that is more of the
megabyte size range than the gigabyte size range. It does get a bad press
because of this as people tend to believe it is a 'toy' database, when it's
not - it's just that people try to force too much data through it. When used
correctly, it is a very professional tool and can handle extremely complex
things. It's also very nifty because of its single-file based architecture.

I would also like to know if anyone has any VB.NET code to *create*
Access databases and add DSNs programatically so that I don't have to
rely on the user to set everything up correctly.
No but I've got C# to create one... obviously relies on some version of
Access being installed.
public static bool CreateNewAccess DB(string fullpath)
{
Type accesstype;
object appAccess = null;
try
{
accesstype = Type.GetTypeFro mProgID("Access .Application");
appAccess = Activator.Creat eInstance(acces stype);
accesstype.Invo keMember("NewCu rrentDatabase",
BindingFlags.In vokeMethod | BindingFlags.Ig noreReturn,
null,
appAccess,
new object[]{fullpath});
accesstype.Invo keMember("Quit" ,
BindingFlags.In vokeMethod | BindingFlags.Ig noreReturn,
null,
appAccess,
null);
return true;
}
finally
{
if(appAccess != null) Marshal.Release ComObject(appAc cess);
appAccess = null;
}
}
I converted it to VB.NET at
http://www.developerfusion.com/utili...sharptovb.aspx
and it comes out as this: (although I haven't tested it)

Public Shared Function CreateNewAccess DB(ByVal fullpath As String) As Boolean
Dim accesstype As Type
Dim appAccess As Object = Nothing
Try
accesstype = Type.GetTypeFro mProgID("Access .Application")
appAccess = Activator.Creat eInstance(acces stype)
accesstype.Invo keMember("NewCu rrentDatabase", BindingFlags.In vokeMethod
Or BindingFlags.Ig noreReturn, Nothing, appAccess, New Object() {fullpath})
accesstype.Invo keMember("Quit" , BindingFlags.In vokeMethod Or
BindingFlags.Ig noreReturn, Nothing, appAccess, Nothing)
Return True
Finally
If Not (appAccess Is Nothing) Then
Marshal.Release ComObject(appAc cess)
End If
appAccess = Nothing
End Try
End Function

I suppose if you were sneaky you could get around the necessity to have a
version of Access installed, and just store the bytes of a blank Access DB in
a resource of your assembly, and then just spool it off into a file to create
one... not sure about the legality of this though ;-)

A DSN is just a text file in a certain directory, like "c:\program
files\common file\odbc\data sources" or something, have a look at some
existing ones to glean the format, although DSNs are about as fashionable
these days as DDE...

Thanks!

--
- Mitchell Vincent

[Bonj]

> However, I never use it for authentication as it is pretty useless in

that regards.

you sure?
....IMHO it's not *pretty* uselss at authentication, it's *absolutely* useless.
Largley centred around the fact that if you don't have the workgroup file
"set", you default to the admin user - hence have full permissions!
Which makes it an opt-out system, when it should be an opt-in one (or the
other way round)...either way it's fundamentally flawed.

[Cor Ligthert]

Bonj,

This is all what is needed in VBNet to create an access DB.

set a reference to COM adox ext 2.x for dll and security
\\\
Dim catNewDB As New ADOX.Catalog
catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
Source=C:\db1.m db")
///

:-)

Cor

[Mitchell Vincent]

Bonj wrote:

The MSDE package is *far* to huge to be of any real use at over 70 megs,
so I'm left with Access.

Is it? I've got an installer for it called MSDE2000A.exe and it's 43,259KB...

Perhaps I downloaded more than I should have... sql2kdesksp3.ex e is what
I got - from a reference in MSDN I believe. It is almost 70 megs in size.

Coming from using PostgreSQL and SQLite even a 40 meg install for an
embedded database is hefty. I don't think I could convince users of my
shareware that a 50 meg install file is OK :-)

Based on the comments here I think I'll give Access a toss. Thanks!

Access has had a bad rep in the past but I'm wondering if recent
versions (like, the last 5 years) have addressed the issues that it had
previously. I remember speed and relability with multiple users were its
two shortcomings.

In my experience, it's very good for any database that is more of the
megabyte size range than the gigabyte size range. It does get a bad press
because of this as people tend to believe it is a 'toy' database, when it's
not - it's just that people try to force too much data through it. When used
correctly, it is a very professional tool and can handle extremely complex
things. It's also very nifty because of its single-file based architecture.

--
- Mitchell Vincent

[Cor Ligthert]

Mitchel,

Althoug MSDE is fine when it that is done, is it in my opinion a hell to
install and to manage. That install will be a lot better in the next version
(Express) is told.

I thought that Herfried has written in this newsgroup however that the next
versions (Express) are no freeware.

Cor

[scorpion53061]

Isn't that what I said?

Or were you just posting to say you agreed?

"Bonj" <Bo**@discussio ns.microsoft.co m> wrote in message
news:09******** *************** ***********@mic rosoft.com:

However, I never use it for authentication as it is pretty useless in
that regards.

you sure?
...IMHO it's not *pretty* uselss at authentication, it's *absolutely*
useless.
Largley centred around the fact that if you don't have the workgroup file

"set", you default to the admin user - hence have full permissions!
Which makes it an opt-out system, when it should be an opt-in one (or the

other way round)...either way it's fundamentally flawed.