Results 1 to 11 of 11
  1. #1
    stzoid is offline Novice
    Windows 11 Office 365
    Join Date
    Jun 2023
    Location
    Bay Area, California
    Posts
    7

    Access slow and queries used to work

    I generally program on a different platform, so Access is not my strong point.

    We have an Acdess app that suddenly is 'slow' loading tables, and quires that used work, simple UPDATE sql commands, do not update as they should. All of the data is in linked tables. We have Refreshed and Relinked. If I run the sql native, outside of Access, it works properly. Oh, the IT department rebooted the Access server, perhaps when the application was running a procedure.



    Thanks

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    Need more background information

    tables are linked to what? a .accdb or .mdb? sql server? something else?
    Is this a multi user app? if so, is the FE on each users machine or are they all running the same copy of the FE on the server?
    where is the 'access' server in relation to the FE - on the network/LAN, on the web?
    Please clarify 'Refreshed' - do you mean compact/repair? If the latter was it the FE or the BE (or both) that you refreshed?

    Databases will become slower without proper indexing - so are all tables suitably indexed?

  3. #3
    stzoid is offline Novice
    Windows 11 Office 365
    Join Date
    Jun 2023
    Location
    Bay Area, California
    Posts
    7
    Thank you for the reply.

    Linked to MariaDB tables. Single user app. Access is on a Windows 2016 Server and the database is on a separate server, all behind non-public firewall.

    We ran Compact & Repair. We also ran the Relink and Refresh options on the Link Manager dialog, which respond with "success" when those options are run.

    Indexing - now that is exactly what I most likely need. I do not know how to index the Access tables (if that is what you mean).

    All of the above is on the Access server (I believe what you are referring to as the FE).

  4. #4
    stzoid is offline Novice
    Windows 11 Office 365
    Join Date
    Jun 2023
    Location
    Bay Area, California
    Posts
    7
    Or, when you refer to indexing, are you referring to the indexes on the back end, the MariaDB database?

    We do have a situation where the same query works when entered as a native query in the database, but fails when it runs in the VB script, from Access. That leads me to think the issue is inside Access, but again, that is not my area of expertise.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    indexing is always on the BE - it is a property of the tables.

    I've not used MariaDB so cannot help with that, but what should be indexed is the primary and foreign keys plus all fields which are frequently used in criteria and sorting/filtering.

    What sort of code are you using? Presume you mean VBA not VB script.

    Other points - you should only bring across records you want, not all records and then filter - it takes time to bring records across. Same with using things like domain functions. They are a VBA construct, so all records need to be brought across to enable the function to do its thing. Use the BE functionality to do this

    Other things to check - you are using the latest ODBC driver for MariaDB

  6. #6
    stzoid is offline Novice
    Windows 11 Office 365
    Join Date
    Jun 2023
    Location
    Bay Area, California
    Posts
    7
    Thank you for the reply. Yes, VBA.

    The scenerio I have is that the Access application worked properly until IT rebooted the server, perhaps while it was running a process (in VBA).

    Now, an UPDATE query is not running properly in the Access application BUT that same query runs fine if I run it directly in the sql engine. (Important point)

    The Access application has not changed at all, the database has not changed at all, nor has the ODBC version.

    Another clue is the client says it takes much longer to run any process in the Access application.

    So, the question is, what happened (and of course, how do I fix it)?

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,773
    Have they had an Office/Windows update?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    stzoid is offline Novice
    Windows 11 Office 365
    Join Date
    Jun 2023
    Location
    Bay Area, California
    Posts
    7
    Quote Originally Posted by Welshgasman View Post
    Have they had an Office/Windows update?
    Yes, I believe they did Windows and possibly Office updates, followed by the reboot.

    I did not think of that when writing my original question.
    Last edited by stzoid; 06-06-2023 at 03:25 AM. Reason: clarificaton of process

  9. #9
    stzoid is offline Novice
    Windows 11 Office 365
    Join Date
    Jun 2023
    Location
    Bay Area, California
    Posts
    7
    Now that you pointed that out, I do read that recent Windows updates can cause several problems on an Access application. This server had not been restarted or updated in perhaps years.

    I was going to have IT update just the accdb file, but this suggests I need them to restore the machine to prior to the update/reboot (if they can).

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,773
    I have never had to do it, but try rolling back one at a time.
    Also search to see if other are affected. There were a few issues caused by Office/Windows updates a little while back.

    I would try Office first, and then Windows.
    It might well not be the server at fault but the client PCs?

    Can you find a client PC that has NOT been updated? Perhaps swicthed off when the updates were pushed out? That is how the banks IT dept used to issue updates, after they were happy they would not cause problems.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    stzoid is offline Novice
    Windows 11 Office 365
    Join Date
    Jun 2023
    Location
    Bay Area, California
    Posts
    7
    Thank you. There are no client-PCs, just the one copy on the server.

    I am having IT restore the server from an image prior to their recent reboot and Windows updates. There is no data on the same server, as it is all via ODBC on a different server. So an image restore will bring everything back prior to the issue.

    Side note - this is my first time diving in to Access and VBA. I had to review the VBA to figure out where the application as sticking, and then copy out the sql query to test. It would be interesting to compare how Acceess works to my own environment, Alpha Anywhere. I have direct drives to nearly any database (w/o ODBC) and can run JavaScript along with server-side programming. I notice that a lof of the VBA syntax is mimiced in my server-side programming language.

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

Similar Threads

  1. Parameter Queries run VERY slow...
    By MatthewGrace in forum Queries
    Replies: 8
    Last Post: 01-20-2021, 03:47 AM
  2. Replies: 1
    Last Post: 06-17-2016, 08:12 AM
  3. Replies: 3
    Last Post: 06-17-2014, 02:58 AM
  4. Update queries sometimes very slow
    By GregL65R in forum Programming
    Replies: 3
    Last Post: 12-05-2013, 05:42 PM
  5. Replies: 1
    Last Post: 03-29-2012, 07:56 AM

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