MS Access ODBC connections

[SomethinCool]

Does anyone know the max amount of connections an Access database can have using the jet odbc driver? I am currently writing a vb.net application to write and retrieve data from this database. I don't have access to a SQL server and I can't set up a new server with MySQL or anything so I'm stuck with Access.

Is there a limit to how many connections can be established to the Access db file?

Thanks

[si_the_geek]

Thread moved to Database Development forum

It depends on exactly what each connection is doing (and various other circumstances, such as network speeds etc), but the limit is generally 5 to 10 simultaneous connections - any more will cause regular database corruption.

If you need more simultaneous connections (and/or reliability), you will have to use a server based database system of some sort.

[SomethinCool]

How will this cause database corruption?

I will be dropping a vbscript on a bunch of servers (possibly 200) that will update this access database and an asp.net page that will display the contents of the database.

This web server will be running on a Windows Server 2003 Standard Edition server, so it is not a workstation that only handles 10 connections. Does the Jet ODBC driver only support up to 10 connections, or is there not a connection limit using access databases?

[si_the_geek]

Access databases (or any other Jet databases) are file based, so every computer connected reads/writes that file - and the more connections there are, the more likely it is that there will be a conflict while two or more are writing to it at the same time. If there is a conflict, part of the file is corrupted.

Server based database systems do not have that problem, as only the server writes to the file(s).


I'm not sure how many simultaneous connections Jet theoretically allows (probably about 255), but the reality is that your code needs to be extremely good to allow 10 'writeable' ones without regularly getting corruption.


Is there a particular reason you can't install SQL Server Express (or some other server based database system) on the web server?

[SomethinCool]

Yes, my department doesn't technically own the server. This isnt an official project.. more of a side thing and I can't spend money on licensing, software, hardware, etc. I might be able to get a SQL server, but I'm trying to avoid that configuration.