Results 1 to 7 of 7
  1. #1
    aeqquus is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Texas USA
    Posts
    4

    Question Repainting Access screen when SQL Server running in background


    I have a form that reads through one SQL Server view (using ADO) and adds records to another table based on new key values. There is a status bar (label) on the form and I update the caption as it reads through the source table. The problem is Access goes into Not Responding mode (I assume because of ODBC timeout issue). Is there a way to force Access to repaint the record counter caption (except by setting a huge ODBC timeout number)?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Why use ADO to 'view' the SQL Server view?
    Views can be linked and behave just like a linked table. No timeout issues using that method.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    aeqquus is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Texas USA
    Posts
    4
    The process is reading through the view's recordset and inserting a record into other tables using ADO. Some records are AddNew and some are Update depending on if the record exists in the destination table. I'm not actually viewing either the source or destination tables on screen until after the process completes. Then I view the destination table as a datasheet by binding an ADO recordset. The process takes considerable time because the source table is currently 11K rows and three master tables are updated if there are new records. There is a lable on the load form that shows records processed, how many adds and how many updates. I reset the lable caption as each record is read from the source view and repaint the screen.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The fact that the process takes a considerable time is why I'm saying to directly link this view so it acts like a linked table. The data will be available instantly.
    You can then append that data to another table or use it to update existing tables and do both using append / update sql statements (or queries) which will also be much faster (in fact almost instantaneous).
    You can still log the number of new and changed records and show progress if you wish.

    I do this type of action on a very regular basis, It works well
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    aeqquus is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Texas USA
    Posts
    4
    I agree linked tables are faster but as a whole the application is more unstable. My boss wants the database to be sharable via a common front-end on a network drive. I told him this is unstable due to constant file locking issues. My goal is a front-end consisting only of forms and modules, no queries or tables, including linked tables. I don't want to dynamically link the table, run the procedure, and de-link the table. It's too risky and if the linking breaks and the app wants to restart, the whole thing is hung up until the locking file is unstuck. I'll try it and see. Thanks.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Politely tell your boss that a shared front end will lead to corruption.
    However giving each user their own copy of the FE will work without issues. No file locking should occur.
    With that arrangement for a split database, it is NOT true that applications are more unstable.

    The FE should indeed contain everything except tables … with the possible exception of 'temp' tables used in processing imported data (if needed) and user settings tables.
    All other tables should be in the Access/SQL BE.
    Note that queries are normally stored in the FE.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    aeqquus is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Texas USA
    Posts
    4
    I agree whole heartedly with the feedback. One other note is that I plan to have this migrated to compiled VB. I appreciate all your thoughts and knowledge. I've developed many apps over the years but this is the most complex. Most have been accounting, financial or administrative in nature. This is an investment management and trading system with lots of calcs and reiterations of portfolio models

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

Similar Threads

  1. Running Access from a server
    By sawdustmaker in forum Access
    Replies: 12
    Last Post: 10-14-2019, 12:36 PM
  2. Replies: 4
    Last Post: 12-12-2018, 09:23 AM
  3. Replies: 3
    Last Post: 02-03-2017, 05:04 PM
  4. Replies: 1
    Last Post: 08-08-2014, 06:35 AM
  5. Replies: 4
    Last Post: 07-30-2014, 06:52 PM

Tags for this Thread

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