I'm developing a relatively small application that will be used by 5 - 10 people. The database will be access... which i understand is limited to 5 concurrent connections.
Is it a good idea to create a global connection object and keep the connection open for each session or should I connect / disconnect each time a db function is called? eg. when the user provides their login criteria, I am currently connecting to the db, authenticating them, and then disconnecting. However, on the next screen that appears, I need to query their user profile to determine which modules I should / should not display. In this scenario, I think it would be better to have just maintained the initial connection...
also, is there any good vb / access sample code available?
the front end will be vb forms. but in the future, i plan to rewrite the front end in ASP.
does it make a difference ?
Also re: 5 users... yes.. you're right - it supports up to 255 simultaneous users. No more than 25 recommended is what I read.
if you plan to use VB and in the future ASP, forget Access NOW and use MSDE.
1. If you will have more users in the future, you will easily upgrade to SQL server
2. if you plan to use different front end, create all main database functions on the server as stored procedures and extended stored procedures. Then you don't care so much about the front end, because all DELETEs, APPEND, MODIFY and other stuff is error-checked and proved on the SQL server (all your CHECK constr. and other LOGIC is written just once).
SQL server does not have problems with many opened connections, so if you don't plan app for 5000 users, keep it opened. Initial hanshake is short, but still it takes sometimes few seconds....