dcsimg
Results 1 to 5 of 5

Thread: Access Front End for multiple users?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2017
    Posts
    58

    Access Front End for multiple users?

    If I build Access application as front end, and link it to multiple Sybase database tables. The Access file is saved on shared drive, everyone can open it.

    In the Access application, I have a form for Username/Password, I also have a table for Username/Password (The table will have only one record all the time, the stored information is current user's username/password). When an user log in, the program deletes any record from the table (if there is record), then INSERT current user's username/password as a new record. Every time if the user performs a new task (which requires linking to Sybase table, or getting some data from Sybase database tables), then program will used stored username/password to perform the task.

    Now the issue is: if multiple users the Access application at the same time. For example, Mike opens the file first, then Access file has Mike's username/password in the Access table. Moment later, Jennifer opens the file while it is still be used by Mike, then program deletes Mike's username/password, and insert Jennifer's username/password. Now the issue is, when Mike performs some tasks after Jennifer opens the file, it will end up Mike uses Jennifer's username/password to perform the task.

    How to solve the problem?

    Thanks.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,722

    Re: Access Front End for multiple users?

    why have just one name in the table?
    have records for all users, with additional field(s) to identify which user to lookup eg. computer name, login name or whatever criteria
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2017
    Posts
    58

    Re: Access Front End for multiple users?

    Quote Originally Posted by westconn1 View Post
    why have just one name in the table?
    have records for all users, with additional field(s) to identify which user to lookup eg. computer name, login name or whatever criteria
    You may be correct, I may be able to use computer name to identify user too.

    Correct me if I am wrong. I would like to know how it works. I would greatly appreciate if you can read the detailed post. I am trying to explain my questions as clearly as possible.


    Access, by default, is a multi-user platform. Is it correct?


    I have a login form which will ask user to provide username/password (when user opens the Access file).


    I have only one table inside Access (three fields: Computer_name, Username, Password). All other tables are in the back end Sybase database, those tables are already linked to Access manually. Let us say, when I build the application, I manually link the Sybase tables but did not save my password in the Access file. That being said, the program needs to use user provided Username/Password to re-connect the linked table. Since it is multiple user platform, if Mike is the first person opening the file (log in), program use his Username/Password to re-connect all linked tables, how to write the code? I only know below code to link a new table, which is not exactly what I need. I think if I don't save my password there(I use my Username/Password to link the back end tables, then close the Access file), the linked table should lost connection. Whoever opens the file next time, the program will need his/her Username/Password to re-connect to back-end Sybase tables. I have verified that: when I use below code the link Sybase table to Access, then close and save the file, when I re-open the Access file, if I try to open the linked table, a window pop up for my Logon ID/Password. In this case, how to write the code to pass user's Username/Password to re-connect the linked table? The other way to go around it: I realize if I use acLink to link one un-needed table, then all the previously-linked tables are re-connected(as long as all other tables are from same Sybase database), then I go ahead and delete the un-needed table. It seems working, but the method is silly.

    If Mike is the first person opening the file, the program uses his Username/Password to re-connect to Sybase back end tables. A moment later, Jennifer opens the same Access file, while Mike is still opening the file. Since the back-end tables are already connected via Mike's Username/Password. At the time point when Jennifer opens the file, does she open the file (saved by developer without password for linked table) or open the file already linked by Mike? Does the Access application needs to use Jennifer's Username/Password in order for her to run some programs(interacting with Sybase) in the Access application.

    I am not sure if I make my questions clear. Let me know if you have any questions regarding my questions.

    Thanks.

    Code:
    DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
    & "DATABASE=pubs", acTable, "Source Table Name", "HelloWorld", False, True
    Name:  Reconnect.jpg
Views: 47
Size:  33.6 KB




    Name:  Table.jpg
Views: 47
Size:  9.1 KB
    Last edited by VAian; Sep 1st, 2018 at 10:08 PM.

  4. #4
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,223

    Re: Access Front End for multiple users?

    Hi,

    forget all your Docmd...Stuff

    Database Transfer -Stuff- should be only for the Admin(you) to do.

    you have questions all over the place, concentrate on one
    read about Record Locking first..
    http://www.databasedev.co.uk/multi-u...d-locking.html

    I showed you in your other thread how to Transfer(with Sql) or create a Link with another Database

    break you problem(s) into 1 problem at a time

    regards
    chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,722

    Re: Access Front End for multiple users?

    Access, by default, is a multi-user platform. Is it correct?
    access is a simple database, while it can be used as multiuser, it is NOT a database server, possibly you should be looking at a more robust solution
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width