Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    47

    Unanswered: Selecting a record in a multi-user environment

    Just a little question related to a db that I might have to build.

    Since I started coding with VBA and Access, I only had to build small applications that were only used by 1, 2 or 3 users at the same time and I always used replication for my multi-user systems. But now i have to build a system that could have up to 35-40 users simultaneously in peak periods.

    My problem is I'd like to know how to select a record from a table without flagging a field in a table. In other words, lets say that you have a table for clients, and another table for orders. All orders are linked to a client found in the Client table. How is it possible to select a client, show its information, while at the same time showing only this clients' specific orders. Also adding and deleting records in the orders table while browsing a client's info must be possible. All of this in a multi user environment.

    Usually, when I face this kind of problem in a single user environment I use a boolean field as a selecting flag. And use a couple update queries in a macro to select records in all tables. Though this way, it is impossible to use multi user environment if not using replication... And I hate replication.

    I know there must be a better way to do it, plz help me!!!


    Thank in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think you are going to get alot of people telling you not to do this project in Access. You can use it for the front end, but I think you will need something more robust for the back end. I personally have not created an application that 40 people would be using. I am creating one now that about 10 people might be using and I am concerned about whether Access is the right tool.

    As far as your specific question about setting a flag to select a record, I am not sure I understand what you are trying to do. Are you marking records that need to be deleted? Because Access is a Multi User environment.

    In the past I have created a table that had the username as a field in the table to store things that the specific user wanted to do. Another approach is to have a split database that has a front end and a back end. The back end has all the common tables and the front end has everything else, including parameter and working tables. Then you distribute the front end to each user. And the tables in the front end would be were you store what that user is doing. This approach assumes that the tables are somewhat temporary. Tables that could be overwritten without any worries. Because when you distribute a newer version they will be overwritten.

  3. #3
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Hi,

    don't worry if access can handle if a user is deleting a record and other is querying it. Access does that kind of stuff with a leg behind his backs. The problem is when the database grow to a considerable size (400MB ~1GB), if you are runing with 5~10 users, there is no problem, if the database is well dimensioned, the frontend has unbounded forms (bounding them with code when necessary) and the server capable.

    The best thing to do is to upsize the backend to SQL Server, and if you do not want to make new querys and put your code in ADO, i mean, if you don't want to make a lot of changes in the frontends, link the tables with ODBC to the SQL Server backend.

    Anyhow, you get better results if the frontend is an ADP Access project.

  4. #4
    Join Date
    Nov 2004
    Posts
    47
    First of all,

    I doubt 40 people will be on it simultaneously, will be more like 5-10 active, 10 less active, and another 20 people with the front end installed but using it very sporadicly.

    Do you think Acces would be the right choice?

    Secondly,

    DCKunkle:

    As far as your specific question about setting a flag to select a record, I am not sure I understand what you are trying to do. Are you marking records that need to be deleted? Because Access is a Multi User environment.
    I'm marking records that are in different tables that need to be displayed at the same time,... might be a very basic question for you but I'm quite new to this and its been the only quick way I found to do it. I know there must be another way in SQL to select records that are in different tables.

    My major problem when I did that, was that the query wouldnt let me add more records.


    Miguel,

    link the tables with ODBC to the SQL Server backend.

    did you mean link the querys with ODBC to the SQL Server backend? Cause if not, I dont know what table you are talking about since the tables would now be in SQL Server ...

  5. #5
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Quote Originally Posted by fuujin

    did you mean link the querys with ODBC to the SQL Server backend? Cause if not, I dont know what table you are talking about since the tables would now be in SQL Server ...
    No, when you link an SQL table to access the only way to do it is by ODBC. You are linked to a secure database with an Access frontend. When the requests to the server in a multiuser enviroment are being too many, the only thing that can happen is that the time for ODBC expires, and the user can't see the records in the table or query at that precise time, but if that user try again to open the query and if the requests to the server are not so many as before, it is possible that he can see the records in the table or query.
    The only thing that you are sure is that your data is secured from corruption, thing that you're not if the backend is an Access database (.mdb).

    Other type of linking an SQL table to an Access frontend, is with Pass-Through querys, this querys simulate the "VIEWS". With this, the data is processed in the server and then reported to the client, rather then processed in the client as the linked ODBC tables or other access table. With this querys the trafic on the net reduces a lot, making the responses from the server more quick.

  6. #6
    Join Date
    Nov 2004
    Posts
    47
    How can I build up pass-through querys? And since I've never used SQL Server, is it very different building tables in that than in Acces? Could I do it without knowing much about it?

  7. #7
    Join Date
    Nov 2004
    Posts
    47
    And would MySQL do the job instead of SQL Server? I just downloaded a windows version. I dont feel like paying the 5K$ license to try it hehe

  8. #8
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Fuujin,

    What version of Office do you have?

    If it is 2000 or 2002 or 2003, you have the MSDE in the cd, all you have to do is install the MSDE (SQL free version). Or go to the Microsoft's website and download the MSDE. I think it's free.

    If you don't have any of this Offices, just forget the SQL.
    I'm not sure if you can do it with Access 97 (with all SP's of course)

    This version of the SQL Server (MSDE Micrsoft Data Engine) have only the basics of the SQL Server for enterprise or developers.

    With MSDE you can only create databases through Ms Access and not directly. That's why you got to have a superior version of Access (from 2000 and foward).

    Now, relating to your question about Pass-Through Querys, it's like this:
    -This kind of querys are dificult to made, you have to program them in SQL, and not with you are used to do with normal querys (selecting the fields, etc..).
    -I don't now very well how to do it, i never need it.

    Making tables in SQL is easy, is much like Access, only the data types are diferente, ex.Text => varchar) (Double => real) etc... The rest is identical.
    But you can make in Access and then Upsize to SQL.

  9. #9
    Join Date
    Nov 2004
    Posts
    47
    I have office 2003, might be interesting to check.

Posting Permissions

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