Question Trying to Design a Split Database Access 2007/SQL Server Express

[Jenaccess]

Hi,

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 maximum of 7 concurrent users, and that's even high, more likely it will never go above 5.

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.

 

[MSAccessRookie]

I believe that I am able to address a few of your points:

You say that you have a maximum of 7 concurrent users, and a current volume of about 30,000 records.

I work on a database that has an average of 15-20 users and a current volume of over 3 million records spread out over 60+ tables. This database was used as an Access Only Split Database with a Front End on each User Workstation, and a Back End located on the Company Server, for over 10 years. They rarely had any issues, performance was viewed as acceptable, and Downtime was infrequent enough so as not to be a major issue for them.

In 2008, I converted the Database to run with the Back End located on SQL Server. Since that time, they have not had any issues, Performance is viewed to be excellent, and there have been no Downtime issues related to problems in the Database.

As you already pointed out, the vast majority of experts posting here will advise you to split any database that is being shared. I might suggest going one step further and saying that you should split any Access Database that you make. After all, who is to say that a Database that is being created for a single user today will not need to be shared next week or next month?

While I cannot say for certainty that splitting your database and making design changes would allow you to continue using Jet, I can say that in your case, it seems like it would be worth the effort of trying to find out.

Both SQL Server and SQL Server Express can handle the workload that you describe, but I believe that SQL Server Express was built for home use, so I would check the fine print on SQL Server Express regarding using it in a Corporate Environment. If I am not correct, then you could probably use either. Another person might be able to supply more detailed differences for you to evaluate.

I hope that these answers address some of your questions, and I am sure there will be others who are willing and able to address more of them. Good luck with your projects, and get back to the Forum with further questions.

 

[TonyE]

Another way to keep using just MS Access is through Citrix....
You put your compiled MS Access application and it's backend DB's in a shared directory in Citrix server or linked server.
Accessing the application through Citrix gives a faster more efficient connection for multiple users.
I have DB systems with many users using this method today.

 

[theDBguy]

Another way to keep using just MS Access is through Citrix....
You put your compiled MS Access application and it's backend DB's in a shared directory in Citrix server or linked server.
Accessing the application through Citrix gives a faster more efficient connection for multiple users.
I have DB systems with many users using this method today.

Hi. This is good information, but please be aware this is a 10-year old discussion. Cheers!