Access in Multiuser environment

[coolmesh84]

Hi All
This is probably a weird question... please bear with me.

I've actually developed an application in VB.net with access as backend(I'm using OLEDB Jet provider).

But the application handles multiple users. Also the application requires that the access should reside under the local machine. This eventually translates to the fact that my application will handle only one user at a time(no client server model).

I want to put this backend access db in a server or something and make it multiuser. is it possible? If possible how should i go about it?

I've read somewhere that i should put this access mdb file in a file server. Will it be of any help??

[Deepak Sakpal]

Yes... that is the only way by which you can achieve multi-user functionality in your application. Put the Access DB on Server and specify the file path in connection string.

[coolmesh84]

Thanks!
But I'm wondering how.
My application is basically OOPS based. I'm handling the connection string as member of Human objects like employee, technician, engineer etc. so its actually easy to change the conn string.

But I'm wondering how??
Also which system i should put this access db? in XP or some server systems?

If i put this file in simple file sharing i.e. through \\CmpName\Folder\data.mdb(i assume this is how i should modify the connection string) will there be any performance issues??

Please reply!!

coolmesh84

[Deepak Sakpal]

I'm wondering how

You need to create different user roles in your database, so user will login in your system with their respective user credentials.

which system i should put this access db? in XP or some server systems?

Put it on some Server system as normally server has better configuration.

If i put this file in simple file sharing i.e. through \\CmpName\Folder\data.mdb(i assume this is how i should modify the connection string)

Yes, that is how it's done.

will there be any performance issues??

Yes, there will be performance issues. If you put Access DB on server then all the data will be transferring through the network, so it will take some delay in fetching/saving records.

Just remember that MS Access doesn't scales well when there are more than 12 simultaneous connections.

[Stoobert]

If you are looking to go client/server DB based then for scalability purposes I would consider looking at SQL Server (Express edition is free and works like a charm).

[coolmesh84]

You need to create different user roles in your database, so user will login in your system with their respective user credentials.

Yes I've. thats part of the requirement. so i typically have different technicians and engineers logging in. some datas and functionalities are not allowed for technicians. each logs in using their user name.

Put it on some Server system as normally server has better configuration.

Can you quote some exapmles of servers?? I'm zero in client server area.

Can you give me an example of how to build the connection string. snippets/examples will be useful for me because i just not aware of client server model.

Yes, there will be performance issues. If you put Access DB on server then all the data will be transferring through the network, so it will take some delay in fetching/saving records.

Just remember that MS Access doesn't scales well when there are more than 12 simultaneous connections.

Luckily, my application will be used by around 10 people and definitely not at the same time.

But I'm more worried of the time it takes to fetch/save records. I've seen computers hang when we use start->Run-> and type \\COmpName\folder. will the OLEDB connection also experience same speed and notably give less performance?

Thanks for your kind suggestions.

coolmesh84

[Stoobert]

But I'm more worried of the time it takes to fetch/save records. I've seen computers hang when we use start->Run-> and type \\COmpName\folder. will the OLEDB connection also experience same speed and notably give less performance?

You will get less performance as it is over a network, but that delay you see when going to Start -> Run is becuase Windows likes to enumerate files and folders first, so if there are a lot of them, it will take time to gather them all. As you are pointing at a specific file, that delay you are seeing will not happen (as can be seen when directly going to \\server\folder\filename.ext directly.

[coolmesh84]

Thanks stoobert,
I initially considered Sql express 2005. but i've no idea how to deploy it using my application i.e. the requirement is to create this application in a CD.

so i've no idea how to set one system as server and develop my program as client application.

that is why i chose Access to get rid of client and server things. but recently i read we could put access in a server and run like a server based application. so that is why i'm enquiring. Besides i've only around 9 tables(i guess 9 tables is relatively less number for a database). so went with access.

I would really appreciate if you could point me towards articles on SQL express 2005 in the context deployment, creating server side schema, client side requirements etc. I would like to use SQL server for my future projects. I really don't want to use the sinister access.

Coolmesh84

[Stoobert]

Well it is going slightly off the topic (and I dont want to cloud Deepak's answers) but there is a lot of nice info at http://msdn.microsoft.com/en-us/libr...6(SQL.90).aspx for SQL server getting started. I didn't realise the project wasn't going to grow so maybe an access DB is the best way to go for this.