Which Database file to use?

Question

2.67/5 (3 votes)

See more:

Hi Guys,

I've been going round in circles with this one.

I need a database to use with my VB app.
The situation is this; We create a database and then give the database and app to the client at the end of the job.

When we are adding data to the database this may be completed by a single user or by a group of users all working from the same database over the network and out of the office at the clients site, so SQL Server is a no go.

I started the project using Access but its performance was a joke.
I've tried VistaDB but its performance over the network is also really poor.

I tried SQlite and found that it corrupts when used over a network with multiple users.
Next I tried SQL Express 2005 but this won't connect to the database over a network.

My question is, does anyone know of a stable database that performs well over a network and is free and allows multiple connections?

Thanks in advance,
G

Posted 6-Apr-11 5:41am

nikon17

Updated 6-Apr-11 22:54pm

Dalek Dave

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 6-Apr-11 13:07pm

Database... file?! Unless it is a typo, it looks you need to learn the notion itself: what a Database really is.
--SA

nikon17 7-Apr-11 8:25am

I didn't explain fully. I understand Server databases but this isn't an option for me. I've used Access, SQLite and VistaDB all of which use database files. My app is currently using SQLite but this is no good when two engineers need to share the same database file.

Sergey Alexandrovich Kryukov 7-Apr-11 13:08pm

I can see "contradicting paragraphs" in you requirements.

In one place you say you need "database that can handle multiple connections over a network." In the other place you say "The app is used mainly out of the office by our engineers on the road. They generally only have mobile internet connectivity and as we do work overseas may not have any internet access at all."

Did you know there is no such thing as miracle?

--SA

Dalek Dave 7-Apr-11 4:54am

Edited for Grammar and Readability.

nikon17 8-Apr-11 4:10am

Hi Dalek,
The app is used in various situations. It could be used in Japan for example by a single engineer. It may also be used by 4 engineers linked on a adhoc local network on site in a factory. I do also know that there are no such things as miracles but that does not help me.

5 solutions

Solution 2

Depending on the size you need for the DB, there are several options for a free DB,as there are
-SybaseExpress
-Postgres
-MySQL

My favourit for small to middle sized DBs is MySQL. But there are a lot of things you have to think of.
Do you need transactions, stored procedures, large BLOBs, replication?
Depending on your level of security for your data, you should consinder not to expose your database to the public.
Set up a webservice to be the only instance allowed to talk to the database.

SQlite is no option except for storing some config data for your app at localhost ;)

hope this helps
if not ask me more :)

Posted 6-Apr-11 6:11am

Michel [mjbohn]

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Accepted Answer · 2011-04-06T06:08:00

SQL Express 2005 should be fine for what you're trying to achieve.

What you need to think about here is how the SQL instance is exposed over the network and how clients are going to connect to it. This is going to be the same issue for any RDBMS you might choose, such as MySql
[^]

For example, within your office network all your users should be able connect to the instance. However, connecting to the instance from a remote location (clients site) wont be immediately possible because they're not on the same network.

In this case, you've probably got a public facing firewall that you could configure to forward requests to the SQL instance [^]

It depends entirely what you're trying to do. If you want a database to always be accessible from multiple locations, you'd need to host a central database somewhere and expose it over the internet in a way described as above. Other options here might be creating web services that your application uses for data access rather than making direct database connections.

If you just need to do this during development time, and then you're going to hand over everything to the client to host internally (no longer requires public access), then keep the SQL instance on your network and think about setting up a VPN. People working in remote locations can then access the SQL Server through the VPN - you can do your dev work, then hand over.

Wendelius · Accepted Answer · 2011-04-06T06:52:00

Hi,

The choice depends on many different factors, like:
- performance requirements
- underlying OS
- upgradability
- licensing issues (possible existing licenses at client etc)
- estimated size in 1,2,5 years
- number of (concurrent) users/transactions etc

If you were unable to connect to the SQL Server over network, it's a configuration issue, not a restriction in SQL Server. In order to connect, you'll need the ability to communicate to port 1433 (default setup) to the specified IP address. So for example a firewall may be blocking the communication.

But to the question. My 'favourites' would be (in no order):
- Sql Server (starting from Express)
- Oracle (starting from XE)
- DB2 (starting from Express-C)
All of those are free but have limitations in size, memory usage etc. What's good about all of them is that they are actually limited versions of very widely used 'major' databases so reliability is in good shape. Also if the amount of data, users etc grow, it's easy to upgrade to 'larger' versions. Also one point is that they all have very good support organizations if that's needed.

About Access and similar products, I would stay as far as possible. Access isn't a database, but simply a flat-file (I know this is a dangerous statement in this forum, but still...). It isn't capable of for example:
- proper privilege handling
- secure logins
- proper transaction log handling etc.

The current state of SQLite doesn't look promising. Some kind of transition in progress and developing a program using it could be difficult.

Good luck to your project :)

Sergey Alexandrovich Kryukov · Accepted Answer · 2011-04-06T07:11:00

Solution 4

You already got an overview of the criteria, see the Answer by Mika.

Now, you have something to select from and perform comparison. See http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems[^] and http://en.wikipedia.org/wiki/Comparison_of_object-relational_database_management_systems[^].

—SA

Posted 6-Apr-11 7:11am

Sergey Alexandrovich Kryukov

Comments

Wendelius 6-Apr-11 15:31pm

Good links. Teamwork again :) my 5

Sergey Alexandrovich Kryukov 6-Apr-11 15:32pm

Yes, thank you :-)
--SA

Espen Harlinn 6-Apr-11 16:59pm

Nice links, 5ed!

Sergey Alexandrovich Kryukov 6-Apr-11 17:49pm

Espen,
You already saw nearly identical Answer from me, that time in addition to your criteria for selection of the product (very reasonable ones, by the way).
Thank you very much.
Teamwork again! :-)
--SA

nikon17 · Accepted Answer · 2011-04-06T22:35:00

Some good answers there to look in to thanks.

Some further details.

The database size will not grow more then 200mb on average so size isn't an issue.
The app is a Windows App.
SQL Server is not an option as the client would need to buy this and we can't expect them to layout for this cost. Some of our clients may be very small companies with little budget. I've tried to use SQL Server Express but hit a problem when placing the .mdf file on our NAS. I forget the error but it reads along the lines of you can not connect to a database over a network.

The app is used mainly out of the office network. We have a group of engineers which go to the clients site to make assessments. Whilst there they use the app to record the assessments. They will link together through a switch to share a database.

It would be very unusual for more then 4 users to need access at the same time.
Security is not an issue the data stored is not sensitive.
There is no need for stored procedures.
VPN isn't an option our office network is VERY secure. The only way for our engineers who are out and about is to use a mobile connection to access our VPN, which isn't reliable enough.

The old software which I'm replacing used VB6 and Access. This has worked almost perfectly for 7 years with only a couple of databases corrupting due to size. My perfect solution is to have this sort of system. A simple flat file database with no installation of a database server required. But I guess I'm not going to get this.

Thanks for your replies, I'll keep looking.

Which Database file to use?

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0