Results 1 to 10 of 10

Thread: database access via a server

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2010
    Posts
    80

    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.

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    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).
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.

  4. #4
    Frenzied Member
    Join Date
    May 2003
    Location
    So Cal
    Posts
    1,564

    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.

  5. #5
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: database access via a server

    Quote Originally Posted by BrianS View Post
    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.
    Last edited by leinad31; Jun 23rd, 2010 at 10:38 PM.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    May 2010
    Posts
    80

    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.

  7. #7
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: database access via a server

    Quote Originally Posted by Honsolo View Post
    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.
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." — Charles F. Kettering

    Read articles on My Blog101 LINQ SamplesJSON ValidatorXML Schema Validator"How Do I" videos on MSDNVB.NET and C# ComparisonGood Coding PracticesVBForums Reputation SaverString EnumSuper Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: database access via a server

    Quote Originally Posted by Honsolo View Post
    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).

    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.

  9. #9
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: database access via a server

    Quote Originally Posted by Honsolo View Post
    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.
    Last edited by leinad31; Jun 24th, 2010 at 07:26 PM.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    May 2010
    Posts
    80

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width