database access via a server
I have a program that will have multiple users accessing a database on a server. I'm new to this and have a question about access.
If the database is already in use and another users tries to access it, will it crash my program the same way it does if i try to access a local database that is already in use, or will the server automatically place it in a Q until the database is available.
If the server doesn't handle this issue is there a script i can put in my program to retry access every 10 seconds until its available and not error out.
Re: database access via a server
It should not crash you application no matter what db you use if more then 1 person access it at a time. What are you going to use for a database (and please if you say MS Access as a networked data base for multiple users change your mind now).
Re: database access via a server
While a network is involved to some degree, you will not be doing any "Network Programming", so that forum is not really apt - Thread moved to 'Database Development' forum.
Re: database access via a server
Databases also have a concurrent user limit that can usually be adjusted. So what you really want to do in your app is open the connection, do what you need to do, then close the connection. Don't let the connection sit open when it doesn't have to.
Re: database access via a server
Quote:
Originally Posted by
BrianS
Databases also have a concurrent user limit that can usually be adjusted. So what you really want to do in your app is open the connection, do what you need to do, then close the connection. Don't let the connection sit open when it doesn't have to.
If your programming language supports it, use connection pooling. Latest release of most database also have connection pooling mechanisms built-in; it only has to be enabled. Consider those, repetitive opening and closing of connections will just place additional load on the database.
Re: database access via a server
I need the connection to stay connected for the current user in that database. If they make changes while not connected and another person gets in and makes a change before they get back in , when the first user reconnects to submit their changes the second users changes will be lost. Each user shouldn't be in the database no longer then a minute. If I can get my program to place the second user in a Que to wait for the database to clear, I think that problem would be solved. unless someone can point me to a script that will search for changes in the database and make them on the server. That's way beyond my abilities. Well maybe not, but would take me a very long time to put it together.
As of now with access to my local databases my program gives me an access violation if the database it's accessing is locked in memory and shows as being used. This usually happens during programming and I get a crash in my script and the excel sheet I'm working on doesn't get shut down proper. I have to go into task manager to release it. I know I need to put in an error handler. I just haven't taken the time to look for how to do it. Might be the same error handler I will use with the server access problem.
I am using Excel and its a very small database.
Re: database access via a server
Quote:
Originally Posted by
Honsolo
If the database is already in use and another users tries to access it, will it crash my program the same way it does if i try to access a local database that is already in use, or will the server automatically place it in a Q until the database is available.
Whichever DBMS system you are using, if it is crashing (whether local or remote) then there is a serious problem with your program itself. Databases are meant to handle concurrent connections. Two or four connections is really nothing for any DBMS system to get overloaded really.
BTW what database are you using and what programming language.
Re: database access via a server
Quote:
Originally Posted by
Honsolo
I am using Excel and its a very small database.
Do you mean you are using Excel as your database?
If so that is a terrible idea because it is not meant for that kind of thing (and will therefore give lots of problems even for a single user), and you should change to an actual database system... perhaps SQL Server Express, but it depends on your situation (such as how many simultaneous users there will be, etc).
Quote:
I need the connection to stay connected for the current user in that database. If they make changes while not connected and another person gets in and makes a change before they get back in , when the first user reconnects to submit their changes the second users changes will be lost. Each user shouldn't be in the database no longer then a minute.
The user does not need to be connected to the database longer than a fraction of a second.
One very brief connection to get the initial values, and another to write their changes back to the database - and depending on how you do the writing, perhaps a check to see if the values have changed in the database in the mean time.
Re: database access via a server
Quote:
Originally Posted by
Honsolo
I need the connection to stay connected for the current user in that database. If they make changes while not connected and another person gets in and makes a change before they get back in , when the first user reconnects to submit their changes the second users changes will be lost. Each user shouldn't be in the database no longer then a minute. If I can get my program to place the second user in a Que to wait for the database to clear, I think that problem would be solved. unless someone can point me to a script that will search for changes in the database and make them on the server. That's way beyond my abilities. Well maybe not, but would take me a very long time to put it together.
As of now with access to my local databases my program gives me an access violation if the database it's accessing is locked in memory and shows as being used. This usually happens during programming and I get a crash in my script and the excel sheet I'm working on doesn't get shut down proper. I have to go into task manager to release it. I know I need to put in an error handler. I just haven't taken the time to look for how to do it. Might be the same error handler I will use with the server access problem.
I am using Excel and its a very small database.
It is not about keeping the connection open per se, it's more about handling concurrency and locking issues via proper system design.
Re: database access via a server
After taking a good long look at my situation I have come to the conclusion that a connection of just a few seconds for each user is possible. The chance that 2 users will access the same file at exactly the same time is greater then the odds of hitting the lottery. We are only talking about 3 total part time users. One big reason for using excel is to have access to the data outside the program. This will store important data that can't be lost. In the event there is a meltdown of my software or i leave the company and there isn't anyone to a maintain my software, it can easily be retrieved by anyone with excel. This is something the company asked for. They have had SQL in an accounting program in the past and after a meltdown it cost them thousands to have the data retrieved and most was lost. I have structured this so that can never happen. The way i have structured it makes the software much faster then having one big database that needs to have regular maintenance. I have one small database with address to each customers own database in it's own folder. We have many pictures associated with each customer. These can be kept in the customers folders as well. When it come time to archive we simply remove the reference to the customer in the index DB and move the customer folder to a CD. I think the index DB will maybe get as big a 500K or 600K is all, and only have 4 fields for each record. There is only one file that can have longer access time and will seldom be used. It's a set up DB. It sounds like the system will handle it on it's own in the event there is multiple access. I will just have to finish my build and check it out. Just want to make sure I wasn't leaving something out that would cause me to have to completely redo the structure.
