Results 1 to 8 of 8
  1. #1
    fratilaionut is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Location
    Bucharest
    Posts
    6

    Append macros

    Hello guys,

    I have a database where the main file is a linked table from excel. Based on this table i have created some append queries.
    I have a total of 36 append queries to tables and i have used a button to append all of them at once.

    The main issue is that in the moment i press the Add button for that macro to run, is taking a while so the tables can be updated but sometime i get a "Not responding" error when the macro is loading or when i want to save the database.

    The database is not big, at the moment it has 4,6 MB.
    I even tried with split data but i have the same issue and i cannot work at all.

    Can someone help me, please?

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,364
    36 queries could take some time.
    Are you sure they all work correctly, it may cause the slowdown.
    did you try them manually?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,576
    Not responding just means it's busy running your queries. It will eventually "recover" but how long that takes will be a function of how complex your queries are. I used to have queries that took 20 minutes or more to retrieve data from tables with several million records so I know of this. Solved that problem by running them at night when no one was there.

    If the operation takes too long for you, you'll have to look into why they are slow to complete.

    EDIT - it might help to see a pic of your table relationships if you created them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    fratilaionut is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Location
    Bucharest
    Posts
    6
    Quote Originally Posted by ranman256 View Post
    36 queries could take some time.
    Are you sure they all work correctly, it may cause the slowdown.
    did you try them manually?
    Hi,

    Yes, they work correctly. I have tried them manually and I fixed all the issues that i had.

    Thank you for your answer.

  5. #5
    fratilaionut is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Location
    Bucharest
    Posts
    6
    Quote Originally Posted by Micron View Post
    Not responding just means it's busy running your queries. It will eventually "recover" but how long that takes will be a function of how complex your queries are. I used to have queries that took 20 minutes or more to retrieve data from tables with several million records so I know of this. Solved that problem by running them at night when no one was there.

    If the operation takes too long for you, you'll have to look into why they are slow to complete.

    EDIT - it might help to see a pic of your table relationships if you created them.

    Hi Micron,

    The database is not recovering no matter how much i wait. The database is not large and it doesn't have milion records.

    Also, the relationships are only to link the tables. There are not so many.

    The idea is that the update from append querry macro is not taking much to run. I have trouble working after the update.

    Thank you.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,641
    suggest show the code you are using - maybe introducing some pauses will make a difference - that is effectively what is happening when you run the queries manually

    perhaps also include some debug.print lines before starting each query so you can see which one(s) are causing the issue

    excel files are not indexed so all elements used for joins, filtering and sorting will be slow

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,576
    I reviewed post 1 and this may be telling
    or when i want to save the database.
    That suggests to me that sometimes there is a connection problem, especially if the db is not large as you say. Are you opening the db as a local file (one where the front and back ends are on the hard drive of the pc you're using) or over a network, or remotely and if remote, are you using a vpn? Is the db split?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    chris-smith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2023
    Posts
    7
    Hi,



    I just googled the problem and found out that this can be fixed by opening Access in Safe Mode.



    Here's a reference to the solution I found.



    Hoping this might come handy to other people.

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

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2021, 08:32 AM
  2. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  3. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  4. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  5. Replies: 7
    Last Post: 07-21-2011, 01:01 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