It has been recently requested that I publish a survey which will be accessed by possibly up to 3000 individuals. These individuals will be requested to fill out an online Intranet survey consisting of 50 questions all of which will be captured in an Access DB file.
Are there any Access issues that I should be aware of when dealing with such a large number of contributors? Would SQL be a better solution in capturing this data?
Any information that can be provided would be greatly appreciated.
To clarify, I have am referrring to Access 2003 and SQL Server 2000.
Cost is not an issue. I have access to both solutions but wanted to get some factual information as to why I should used one server option over the other?
I've read where Access issues can occur when the number of concurrent users reachs a certain number? I've used this same Access DB file in a similiar situation but with only 450 contributors. With the siginificant increase (3000) in contributors I wanted to get as much information in order make the correct DB selection prior to development.
If you have access to SQL Server, I would recommend going with an adp/ade.
SQL Server is designed to handle this size project and much greater if necessary. By using Access as a front end to sql server, you retain Access' rapid development capability (its primary strength in my opinion), as well as MSSQL's back end fexibility and control.
I would recommend SQL Server. Especially if money's not a problem. MySQL may be fast, but SQL Server has a lot more to it (ie. configuring Tranlog backups every hour). We use linked tables in the Access application. The only thing you have to worry about is ODBC and making sure the DSN names are all consistent.
I've found that more than 3-4 users using the same Access tables at the same time can cause some problems unless you program your application with unbound forms (i.e write a lot of code to write/retrieve/update records in the tables).
If you do use SQL Server, below is a neat little app which will spawn a database. This helps with certain situations (ie. when you have to do make tables, etc.):
I developed a spawning technique to copy an Access file with the users name attached to it. I had a little help writing the code and it works fantastic.
We use MDE's and a "spawning" technique which works quite well. Below is a sample vbs script for spawning a database with the users name added to the MDE file. This makes it easy for us to copy new code to our shared working drive without having all the users exit out and works very well. It's also another way to find out who's using the database as you will see the database name with the user name at the end and an ldb file associated with it.
Here's the vbs code to "spawn" a database called "WhosIn2004.mde" located in \\SQLServer\Databases\Databases\WhosIn\WhosIn2004. mde (UNC Name). Change the LUName to the location of your Access file.
Create a vbs script in notepad (saving it with a .vbs extension) using the following code, then make the vbs script your central file to run for all users:
Set WshNetwork = CreateObject("WScript.Network")
GetUser = WshNetwork.UserName
Set WshNetwork = Nothing