Results 1 to 14 of 14
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Sharing temporary table data

    Because some of the queries in this runtime app I develop take so long to run, I have created a couple of temporary tables to store data. A final query pulls from one of the temporary table to feed a form. For some time now the users keep getting #Deleted in textboxes. I've searched everywhere for explanation and couldn't figure out why, but I think I've concluded that it's due to the temporary table's data being deleted and appended every time that form is being executed.



    So, the question is, what is the other alternative method or common practice I should be using, so that no one loses data when there are users using the same form at the same time?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you view the table, then delete records, you will see #DELETED, until you refresh the query/form
    (F5) or REFRESH ALL button

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The main thing would be the location of that table. Temporary tables are usually in the front-end and if your database is not split they will encounter exactly what you describe. Make sure you split the database and give each user their own front-end with their own temporary table.

    Cheers,
    Vlad

  4. #4
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Gicu View Post
    The main thing would be the location of that table. Temporary tables are usually in the front-end and if your database is not split they will encounter exactly what you describe. Make sure you split the database and give each user their own front-end with their own temporary table.


    Cheers,
    Vlad
    Yes, I have split the database. Most of the tables are linked to a couple of databases on the network. The temporary table is stored with the runtime file. I did think about creating a routine to copy the runtime file to copy and paste the entire file to the users desktop, so that they are using a different file, thus their own temporary file. Since that would prolong the time it takes to launch the app, I guess I can just install a file with just temporary tables on the desktop and link the temporary tables. Thanks, Vlad.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    There are many Access front-end (runtime)launchers available, please feel free to use mine if you wish: http://forestbyte.com/ms-access-util...a-db-launcher/

    Cheers,
    Vlad

  6. #6
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    So I went down this path of creating a db (let's call it TempDB) on my hard drive, I exported these temporary tables there, then link them back. I have a routine that when one opens this app., it will check if the user has the TempDB on their hard drive. If the user doesn't, the routine will create a path and TempDB. The idea is sound, but for every linked table, a security window pops up, and you have to click Open. Anyone knows how to overcome that? "DoCmd.Setwarnings False" does not work.

  7. #7
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    This is the security window, by the way.Click image for larger version. 

Name:	LinkWarningMessage.jpg 
Views:	12 
Size:	45.3 KB 
ID:	34468

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried to add the path to TempDB(C:\MyApp) to the trusted locations? This will have to be done for all your users.

    Cheers,
    Vlad

  9. #9
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Gicu View Post
    Have you tried to add the path to TempDB(C:\MyApp) to the trusted locations? This will have to be done for all your users.

    Cheers,
    Vlad
    No, I didn't. I've never touch the trusted location stuff before, but I did just now added, and it seems to work, I think. The reason I said "I think" is that I don't see the security come up, but at the same time, the "Switchboard" form that is set to open upon executing this app launches this routine, does not open. It seems to have executed this and other routines, but it doesn't open at the end. Should I not have put this routine in the On Open event, maybe?

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I would put it in the autoexec macro because the form might need the temp tables you try to relink to.

    Cheers,
    Vlad

  11. #11
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Sorry, I noticed there was something in the code that closed the form, so now it works now, sort of. It works on my .accdb version. When I launched it as a .accdr version, it still pops up on other users.

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have they added the same path on their machines to the trusted locations?

  13. #13
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Gicu View Post
    Have they added the same path on their machines to the trusted locations?
    No. I didn't want to have to touch everyone's desktop to change their settings, as they won't be able to do it on their own. I I just test it on somebody's file by changing their settings, and it works, so, it sounds like I may have to do that to everyone's, huh?

  14. #14
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I usually have one folder on the C:\ drive of each user that I add to the trusted locations and in there I have all the Access dbs that need permissions. I think your users are still sharing a front-end (accdr) from the network and you only split out the temporary tables. You would get better results if all users would have their own front-end on their local drives. Still, you may want to look at this link as it offers some solutions to set the trusted locations:https: //www.tek-tips.com/viewthread.cfm?qid=1718392

    Cheers,
    Vlad

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

Similar Threads

  1. VBA To Add Primary Key To Temporary Table
    By lccrews in forum Programming
    Replies: 4
    Last Post: 05-25-2018, 12:38 PM
  2. Replies: 5
    Last Post: 01-10-2018, 04:16 PM
  3. Create a temporary Table from Query
    By WickidWe in forum Queries
    Replies: 1
    Last Post: 12-11-2013, 07:19 AM
  4. Update Master Table with Temporary Table
    By kagoodwin13 in forum Programming
    Replies: 2
    Last Post: 10-15-2013, 11:59 AM
  5. Replies: 11
    Last Post: 04-04-2012, 05:48 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