Results 1 to 9 of 9
  1. #1
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212

    Can I create a table with a name that is local to a user.

    I have a process which creates 45 tables into which I append data temporarily. I use that data to produce a document for a specific event. Then for the next event, the existing data gets deleted, and I append the new data, and produce a document. If more than one user is using the database, it is possible that the second user could override the data needed by the first user, before the first user has produced the document they want.



    My question is when I delete from and append the data to the 45 tables can I identify them to the user who has opened the database so that they act as if they were local? Thanks for any help.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is your setup? Normally with a split database those tables would be created in the front end, so each user would have their own copy. Nobody would interfere with anybody else.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    This is not a split database.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would strongly recommend that any multiuser database be split. Heck, practically any app should be split. If you don't want to, you could certainly use the logged in user combined with the actual table name to keep them separate:

    tblWhateverPbaldy

    But I'd split it. Does it ever corrupt? If not, you're lucky. If so, splitting will probably stop it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks for the advise on splitting.

    As a reminder, the following is the process that I currently have: I have a process which creates 45 tables into which I append data temporarily. I pull those temporary tables into Queries which I use to produce documents for a specific event. Then for the next event, the existing data gets deleted, and I append the new data, and produce documents. If more than one user is using the database, it is possible that the second user could override the data needed by the first user, before the first user has produced the document they want.

    I have created a module that will find the username. I have created a query that connects the 45 temporary table names to the user name.

    In thinking through how to design a process that will use local table names. I have got this far:

    I need a simple piece of code that looks for the 45 temporary table name and renames them to include the concatenated tablename and username I have in a query. I would run this code as the last step in the macro that first deletes records from the temporary tables, and then appends the latest record to those tables. If for any reason this process would not work for a particular customer, I could just make that step false and they could take their chances on 2 users running the process too close together.

    But more than that, I need away of adding something to the temporary tablename when I create them that translates into username. The 45 temporary tables that I use are pulled into various queries that support the printing of a word document with the information from the temporary tables. There is nothing in my limited experience which suggests this is possible.

    I have this in mind when I ask these questions, anything is possible and can be done somehow, but what my experience allows me to do is quite limited. I still like to pit my wits against it and reach for the stars.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Didn't need the reminder, I understand the problem. The simple solution is to split the app. If you insist on leaving it unsplit, I'd start out by including the user in the table name when the tables are created, and of course everything downstream has to be dynamic enough to handle that. Trying to rename the tables after they are created still leaves you open to another user starting the process in the interim.

    It won't be easy; frankly it will probably be quite difficult. Not sure what your aversion is to splitting the app, but the decision will be costly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    I create the tables manually and do not know of a way to add the user name when I do not know what that is going to be in any customer situation.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm confused, as you said "I have a process which creates 45 tables". In any case, why the aversion to splitting, which would appear to solve your problem?

    I suppose another option is changing the setting in File/Options/Client Settings for Default Open Mode and change it to Exclusive. I think that would prevent a second user from opening the database while one is doing this process. That's untested.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    To be more precise, I use a manual process to create the tables, and then a macro to delete and append the data.

    Using exclusive mode is not an option for me. My software application is used in many different customer locations as a shared database. I understand the option for splitting the database but splitting the database is not in my plan at this time.

    So thanks for your responses, they have made me think really hard about the timing issues. In really trying to understand the timing of two users, if they ran the process and immediately printed the documents, a conflict would be unlikely, so its reasonably logical as a training issue to just tell them that if their data does not show up, to re-run the process and print the documents immediately and that would take care of 99.9%, and a further training issue, would be to tell other users to not print documents while they are trying to print.

    So my decision is that I can live with it the way it is. And you have helped me come to that decision. Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-08-2015, 01:02 PM
  2. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 05:49 PM
  3. Inserting Data to Access Table (Local) from MySQL table (Net)
    By gambit1430 in forum Import/Export Data
    Replies: 2
    Last Post: 09-26-2014, 01:34 AM
  4. Replies: 3
    Last Post: 04-30-2012, 12:57 PM
  5. create user interface from table
    By mer in forum Forms
    Replies: 5
    Last Post: 07-24-2011, 08:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums