This post will be long, as I have several questions related to this project, but please feel free to only answer any piece you want, however small. Every bit of knowledge will be helpful. One of the programs at my office is in need of a database. I've been researching this for awhile, and going to different forums for advice. One forum suggested a VB.Net Front End and SQL Server Back End. I thought that was a great idea and would love to learn VB.Net, but since I have such limited experience with it, I thought trying to learn in the fairly short amount of time I have would be a challenge I could not responsibly meet.
I'm running Access 2007. I'm very comfortable with Access and have designed databases in it. I can also do a modest amount of stuff with VBA, though my skills in that area are limited. I've never built a database outside of Access, and due to my level of comfort, it is the first resource I turn to. I'm thinking that for my project database, an Access front end, and SQL server express back end would be the best way to go. If you disagree, please tell me. Here are the needs of my program: a range of 5-7 concurrent users, and a maximum of 30,000 records.
This doesn't seem like much, but another database I built had only 400 records, and slowed to a crawl with 4 concurrent users. I think part of the reason is because it was on a network share and not split. From what I've read on this forum, that's an absolute no-no, and I would split any future databases I create. I know splitting the database would improve performance across the network, and I could probably design the database better. In the past, I would do whatever got the job done without giving any thought to performance issues. I'll have to pay more attention to good design in the future.
Do you think if I created a split database and designed it better, I could get away with continuing to use JET? Would it meet the needs I've outlined above? Or do you think I need SQL server? If I need SQL Server, could I use the Express version? I hear that edition is free and very robust and would probably meet my needs. My concern is would Microsoft allow it to be used in a functioning program database, or is the free version to just test out the product? The last thing I need is to have license or copyright violation issues. Also, please don't laugh at me for this, but I don't even really know what SQL Server is. I know we're on a network server, so would I need SQL server in addition to that?
I've only ever worked with the integrated fe/be of Access and JET, so having another back end scares me. Is it really difficult to set up? If I thought I could get away with it, I'd just use JET, but I've read that it's less secure than SQL Server (and the database does need to be secure), but it would be on a secure network, so does that take care of that problem? I will have more questions in the future, but I want to be respectful of everyone's time. Thank you.