[RESOLVED] Database Software Advice Needed

[dataempress]

Hi All,

One of my program databases is a proprietary web-based database written in C#. I'm in the process of researching alternatives. I think a database that I can build myself would be preferred. Then I could do free customizations and maintainance very specific to my needs.

I'm quite adept with Access, but I don't think that would work in this case. Multiple users need to be able to enter data at the same time across a network. I've tried to do this with Access before and it slows things down like you wouldn't believe.

Would anyone have a suggestion as to what type of software I could use to build a database that would be secure, user friendly and able to have data entered by multiple users at the same time? I have very little experience with building databases outside of Access, so I'm not sure where to even begin. Any advice would be most welcome. Thank you!

[Siddharth Rout]

I still use access database as backend for a small intranet application that I have made from my office(100-120 users). To my surprise, it is pretty fast.

I am sure you already know but few things that you have to ensure in such scenarios...

1) You should close the table the moment your work is done with it.
2) Avoid opening the DB in a loop.
3) Avoid using Access Forms as a front end. Either use VB6/VB.NET in case you are making windows based applications...

Still if the above doesn't help then I would advice you to switch over to SQL

[dataempress]

Thanks Koolsid,

I'd just like to pick your brain a little further if that's okay. Did you have 100 users entering data at the same time on your access database? If so, that's amazing to me. I think during my last attempt, I had four users, and the thing was so darn slow.

The problem with front end/back end for my situation is that the end users have a series of laptops that they use. I can't guarantee that the laptops won't be shuffled around, go missing, etc. So I'm quite hesitant to put anything as a front end on any of these computers. I think there are too many variables. Does that make sense?

How would I get a hold of VB6/VB.Net? Is it expensive? Same with SQL. I might be showing extreme ignorance here, but is VB6 a front end and SQL a back end? I can design an Access database for 1-2 users in my sleep, but anything beyond that, I don't even know where to begin.

My needs are to keep demographic and service related information for an eventual 20,000 students. There probably wouldn't be more than 4-5 people entering data at any one time, but I need something robust. I can't be worrying about it crashing all the time, like I worried about my other database. I've heard FileMaker Pro tossed around as an idea. Do you think it's good software?

[Siddharth Rout]

I'd just like to pick your brain a little further if that's okay. Did you have 100 users entering data at the same time on your access database? If so, that's amazing to me. I think during my last attempt, I had four users, and the thing was so darn slow.

No. The limitation, if my memory serves me right, for Access database is 5 concurrent users. SQl allows much more than that. If you search for "concurrent users for Access" and "concurrent users for SQL" in Google, you will find plenty of articles on it.

How would I get a hold of VB6/VB.Net? Is it expensive? Same with SQL. I might be showing extreme ignorance here, but is VB6 a front end and SQL a back end? I can design an Access database for 1-2 users in my sleep, but anything beyond that, I don't even know where to begin.

My needs are to keep demographic and service related information for an eventual 20,000 students. There probably wouldn't be more than 4-5 people entering data at any one time, but I need something robust. I can't be worrying about it crashing all the time, like I worried about my other database. I've heard FileMaker Pro tossed around as an idea. Do you think it's good software?

I would suggest you to go in for VB.Net 2010. It is absolutely free and can be downloaded from Microsoft's site.. You can use it as a front-end and SQL Database as a back-end.

All the more reason for the above suggestion as

The problem with front end/back end for my situation is that the end users have a series of laptops that they use. I can't guarantee that the laptops won't be shuffled around, go missing, etc.

The reason being that you can install the front-end in all the laptops and keep the database at a centralized location so that your database is safe.

Sorry, I have never worked with FileMaker... Never needed to...

VB6 or VB.net as Front-end and Access/SQL as back-end were always sufficient for my needs

[si_the_geek]

Thread moved from the 'Office Development' forum to the 'Database Development' forum

I agree with koolsid's suggestions of VB.Net 2010 and SQL Server - both of which are free from www.microsoft.com/Express

Did you have 100 users entering data at the same time on your access database? If so, that's amazing to me. I think during my last attempt, I had four users, and the thing was so darn slow.

Based on what you have posted, I assume your front end was in Access too - in which case a massive slow down when there are multiple users is entirely expected, because it virtually forces you to use bad techniques.

There are many issues of that nature with Access, which understandably leads to it being frowned upon by many people. That is part of the reason for Microsoft intentionally driving people away from it (via knowledge base articles, increased price of Access, etc) towards the free editions of their "higher end" products that don't have the same kinds of flaws.

As a data store, the Express edition of SQL Server is far superior to Access in many ways (features like stored procedures, increased reliability, easier backups, allows bigger databases, ...), and becomes even more so when multiple users are involved.

As a front end creation tool, the Express edition of VB is also superior to Access in several ways.


The downside is that there is a little less integration between the two parts, so some things takes slightly more effort - but other things take much less, so even with the extra learning time you should still gain overall.

[FunkyDexter]

I'd third what Si and koolsid have said, SQLServer Express and VB.Net Express are your best basic components. You'll have a bit of a learning curve but there are some great tutorials on this forum that will get you most of the way there and folks here will be happy to help with any problems.

The one thing I'd add is that you might also want to look at ASP.Net express. This is purely in response to your concerns about needing to deploy to laptops which may often change. Personally I prefer desktop apps (just what I'm comfortable with really) but in you situation a web app could be the way to go.

I've got some very limited experience of Filemaker and I don't like it. It's OK for throwing quick and dirty data collection and display forms together but it's inflexible and not very powerful. It'll do you as a short term solution but you're pretty much guaranteed to regret it later.

[HowTo]

I still use access database as backend for a small intranet application that I have made from my office(100-120 users). To my surprise, it is pretty fast

Sql Server 2000 can hold upto 10 to 12 GB; how much data can an access database hold at least?

[GaryMazzone]

SQL Server 2000 can have more the 1TB (I have been the DBA on that size system)

[dataempress]

From what I'm hearing a VB6/SQL combo might be the best option. That gives me a starting point to research. Thanks so much for the input!

I just have a couple of questions. I took one class in Visual Basic and use VBA quite a bit in Access. I also took a class in SQL. Do you think any of that will help me with this project, or is it entirely different?

I also took a very basic course in Information Security and now know just enough to be extremely paraniod and afraid to do anything . Does anyone know whether an SQLServer Express and VB.Net Express would present any security issues, or has security features? That's part of why I'm kind of afraid of this project. I could get a working database, but it could have huge vulnerabilities and I wouldn't even know it because I don't have the background. If anyone has any thoughts about that I would deeply appreciate it.

Also, from what I'm hearing, I could basically build this database for free with Express editions of software. That seems so remarkable. Why would Microsoft allow this to happen? Wouldn't they make the free editions lacking so people would buy the pay version? Are there any basic can't live without features that are lacking in the express editions? Is there a time limit one can use them?

Thanks again guys, this is a tremendous help!

[HowTo]

SQL Server 2000 can have more the 1TB (I have been the DBA on that size system)

sorry if i misguided anyone

with how much data can access deal with?

[si_the_geek]

Your previous VBA experience will help a bit (it is different to VB6 and VB.Net, but there are some similarities), and the SQL experience will help.

There are not any particular security worries, but like with anything if security matters to you then you do need to think about it a bit rather than just doing what seems to work.


As I see it, the Express editions being free (and not time limited) is mainly about marketing - getting more people on their tools (rather than the competition), and making themselves look good.

While there are paid editions with more features, very few people actually need the extras... but if you do find yourself at that point, you can upgrade with virtually no effort (marketing again, as you would be foolish to go to a competitor at that stage!), and be very well educated about which edition you need (rather than being 'safe' and spending extra for the biggest you can afford).

with how much data can access deal with?

It depends on various factors, like how well you have designed the code that works with the database.

The theoretical limit is 2GB... but many people have problems over 100MB (and over 500MB is unusable), while some can get to about 1.5GB before things start getting bad.


Note that SQL Server Express is limited to 10GB (the 'full' editions are only limited by disk space).

[HowTo]

It depends on various factors, like how well you have designed the code that works with the database.

Can you please tell the best practises for designing my database code to get the best out of it?

[si_the_geek]

That is a long and complex topic, and what is best depends on your situation (such as what database system and programming language you are using, etc, etc).

Unless you have exactly the same situation as dataempress (and dataempress wants advice on it too), you should create your own thread for that question - and include some examples of the code you use at the moment.

[dataempress]

You've all been very generous with your feedback. I am going to research a VBNet/SQL Server Express combo and see where that leads me. I'm sure I'll have many more questions along the way. Thanks again!