Mar
27ADO.Net, Microsoft Access and Networks
Posted in: Tech Talk
For a future release I’m thinking of offering a networkable version of my software allowing people to access a central database from more than one PC. I could offer two types of networking:-
- One where the central DB is locked while someone has a copy of my software open.
- One where more than one user can access the database at a time.
I’ve decided to use MS Access and ADO.Net to implement the database and software interface. Access is a doddle to use and ADO.Net fits in very well with my current data access objects. I know I might have to bundle the Jet engine reditributable with my app but the installer side of things looks OK too. As an extra bonus, it looks like, for larger amounts of data in a local database, Access is quicker than my current database system.
The thing is though, I’ve got no experience with networked DBs. I’m wondering what sort of issues I might run into in the field. Also, is performance likely to be an issue if say I use ADO.Net with a few thousand records?
Similar Posts:
If you get anything from this reply, get one thing, DON’T USE MS ACCESS.
I could go on and on about this all day, its easy to use and easy to access data, by design and therefore its security is very poor.
Also as you’ve said you have lots of baggage, with MDAC and Jet etc.
If you develop your software around MS Access, to gain the best performance you’ll end up having a system tied to MS Access and it will be very difficult to move to another database, once the MS
Access 2GB limit is reached.
Use MySQL, its free and you can install it on an intranet pc / server. Provide a MS SQL version too, for serious customers if you can.
For your local database, use XML and the DotNet data objects, then you can cater for users going out of the office too.
Networking database is quite simply, for a basic system, use locking flags on your records, this should be a timestamp and their user id. If the users machine crashes check for the users login record
and the current time against the timestamp, then ask the user to decide about the lock.
I can help you about any locking questions you may have, Ideaspad is designed to run on network ms access database
but it does work OK.
I’ve also written 20 network user systems in the past.
Another point…
The only reason people in business use MS Access is that its easy to use, as I’ve said, but MS Access isn’t aimed at saleable software developers.
If you were an in-house developer, it would be the best choice.
Of course all my comments apply to MS Access 2003 and older, I cant comment on MS Access 2007.
The main reason I’m using MS Access at the mo’ is for its
ease-of-use. I tried MySQL but couldn’t get it to run on
my local PC though it’s a doddle to use from PHP on the
web.
I understand what you mean about security but the size
of the DB isn’t an issue. I seriously doubt the DB will
ever be much more than 20Mb.
As for the interface ADO.Net hides the DB from you, so
by changing the connection string you could swap in MS
SQL, MySQL or whatever. The ADO.Net classes don’t let
you access any DB-specific methods.
I’d appreciate help on locking etc. as it’s this real-world
problem stuff I haven’t got any experience of. What
happens when the network goes down and so on.
Another aspect of ADO.Net is its disconnected nature.
Basically the access classes cache a copy of the data in
RAM, allowing another user to manipulate the data. Then
when you want to insert, update or delete you connect
again to the DB and tell ADO.Net to sort everything out
using SQL commands you preconfigure.
What I intend to do is to cache all accessed data in RAM -
as the user reads it - this means there’s a one time hit -
a second or so for 4000 records - then everything is
blindingly quick as there is no DB access involved except
for the occasional write to update a single modified
record.
I’ve got admit, for a local database I’m struggling to think of an alternative :(. In my case I’d probably use a RealBasic Database, but you can’t do that.
How about SQLLite
http://www.mikeduncan.com/sqlite-on-dotnet-in-3-mins/
That is very interesting! Zero install is what I like to
hear. I’ve heard of SQLLite before BUT I’d kind of
discounted it as I’ve heard it’s not suitable for networked
DBs. Have you heard the same? Maybe I misread
something.
It does say client / server and websites
http://www.sqlite.org/whentouse.html
That’s the very page I read before about SQLLite. It was
this section I was thinking of:
“If you have many client programs accessing a common
database over a network, you should consider using a
client/server database engine instead of SQLite. SQLite
will work over a network filesystem, but because of the
latency associated with most network filesystems,
performance will not be great. Also, the file locking logic
of many network filesystems implementation contains
bugs (on both Unix and Windows). If file locking does not
work like it should, it might be possible for two or more
client programs to modify the same part of the same
database at the same time, resulting in database
corruption. Because this problem results from bugs in the
underlying filesystem implementation, there is nothing
SQLite can do to prevent it.”
I’m not sure this makes it any worse than MS Access if
there’s a file locking problem. Maybe they’re just being
honest when they talk about corruption.
It is tempting to try it out though as in my experience
something that’s easy to install is a big advantage. I’ve
know a number of odd JetEngine DLL-type
incompatibilities on version releases in the past.
All my code at the mo just accesses the base OleDb
abstract classes. In theory I could try out SQLLite simply
by adding the DLL to the build and switching the
connection string.
I think I’ll give it a go next week.
Oh right.
Sorry, I meant, why don’t you use SqlLite as your local database and mysql as your server database.
Actually, why don’t you use sql server express, or better still make it work with both.
I don’t want to use something like SQL Server as it’s a
pain to install. I’ve had people fielding Vista installation
problems for months with SQL Server. It’s big, fat
software for big, fat apps.
SQL Server Express can’t be used for commercial
purposes.
Have you heard of any problems with a networked
Access database?
>Have you heard of any problems with a networked
Access database?
No, I haven’t
What version do you plan to use?
Maybe 2007 might be better?
SQL Express can be used for commerical use. It has some limitations compared to SQL Server 2005.
In fact at GE we have a new Lean system being rolled out to production which requires an SQL Server 2005 Central store and then each production Cell PC will have a local database store which uses SQL
Express.
We have also installed / received software from many vendors in the past who give us an option to use Express or buy SQL 2005.
Yeah, I was thinking that, just thought Mike was right.
I think the limitation is that it has throttling and a user limit. But this isn’t a problem is you do your own user management.
I stand corrected. I thought I’d read somewhere that the
Express version has a license like the C# Developer
Express - personal use only.
Anyway, here’s where my ignorance shows … My app is
v. simple, with small amounts of data. It’s likely to be
used by less than 5 people on a network. Also, if I’m
honest, the networking angle I was thinking of for an
initial release is quite crude - the DB can be on a
network drive but only one person can write to the DB -
it gets locked by the first user opening the package. I’ve
been asked for this type of operation by quite a few
people.
Is it normal to have a local storage as backup? My users
are likely to be inputting such small amounts of data that
a loss wouldn’t be the end of the world if handled
gracefully.
Thoughts?
Hi Mike,
I recently gave some advice to our development department at GE they have some software which they soon want to make multi user.
Basically they will now be doing the following (written briefly let me know if i should expand on anything)
1. User will be prompted during install to install network server version or network client
if they choose network server (works on desktop) it will install MS SQL Express.
Network Clients when installed will prompt for the network server computer name or IP to talk via TCP/IP to the database.
2. The database will have a users table for acccess control this will handle logins and also extra columns would be created on data tables to say that the record is already in use etc
3. The application has an administator option when the admin logs in he / she can create new users specify permissions etc which get stored in MSSQL.
Thats pretty much it in simply terms for a larger user base GE will be advising clients to purchase SQL 2005 Server for better performance but the majority of customers the network software is aimed
at are pretty good with it running like above.
Another angle is Cloud Computing (You host the app, and all edits done via web site) where clients pay monthly for the service. This is becoming very popular now infact GE are moving away from office
apps to cloud computing in the near future.
>Is it normal to have a local storage as backup?
Where I use to work, we used an access database locally to hold static data which didn’t change often.
If you need to store a small amount of data locally, create some sort of data file or use XML.
@Blue,
Thanks! A nice script for a startup sequence. I’ll see
about doing some trials with SQL Server Express.
@Dipsy
Thanks to you too. That makes it clearer for me. I
currently use XML for static data so I could use that for
config. type data or for the small edits the user does if
need be.
—————–
I can see a few things I need to do but first I need to find
out about SQL Server Express and network server usage
and give SQLLite a look over.
I think I’m going to end up with a scale of capability:
Classic versions of my package, Pro versions and an
Enterprise/ Network version with a correspondingly
scaled set of prices.
Thanks guys. I’ve learned quite a bit from this post.
One of the main things I’ve learned is that I don’t have
to use the same DB for local and network storage. So I
could (unless SQLLite seems really good) go ahead with
Access locally then user SQL Server Express for network
storage.
Access solves (or makes handling easier anyway) a
couple of other items for me (as would any other DB
probably). One is localisation - I won’t have to write code
to convert to the correct locale when saving/ loading.
Also it allows me to raise the storage limits and improve
speed with larger numbers of records. I’m 80% done on
the implementation of ADO.Net as a storage mechanism
and I’m glad it looks like I could switch to SQLLite or SQL
Server Express even at this late stage without too many
problems thanks to the DB-agnostic nature of ADO.Net.