Results 1 to 9 of 9

run a macro or a query that is located in another database

  1. #1
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21

    run a macro or a query that is located in another database

    Hey Guys,



    I made a split database, back end located on a server and front end for users localy. The issue here was that it was running really slow.

    So in an attempt to find a solution i am thinking of giving the users the full database localy, this database would update every time they open it (update meaning: deleting a table from wich they get data and fetching the updated version that i store on the server, by using an append query)

    The issue i am having is that i can't seem to find a way of opening this append query that is in the other database on my server. I found some info saying that i should use a button with a macro openquery etc but i cant select the append query from the other database, if anyone knows what i should do it would realy help me out a lot.

    Some info:
    the database is called: All_Documents_Tbl (same name in both databases)
    the append query is called: AppendToUsers_Qry

    I also made a macro that basicly runs the append query but still cant find a way to run the macro from another database
    the macro is called: AppendToUsers_Mcro

    Thanks in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,329
    ??Suggest you tell us more -in plain English - of what you are trying to do - no database jargon.
    Why 2 databases? How are they related? Subject matter you are dealing with...etc.
    Last edited by orange; 11-27-2019 at 06:20 AM.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,428
    I think I get it - not sure I'd do it. How will you synchronize multiple copies of the same table? I download and edit. You download and edit. Now what? Will propagating my and your changes over write one another? I'd be looking for a solution to the speed problem first.
    Is it a network issue because even working with other Office files (Word, Excel) is very slow?
    Is it a db issue because queries are not optimized? Too much use of aggregate functions across the network?
    You are storing attachments in the db (not a good idea)?
    etc. etc.
    You really have 2 databases with the same name? I'd say that everybody having a complete local copy defeats the primary reasons for splitting in the first place.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  4. #4
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by Micron View Post
    I think I get it - not sure I'd do it. How will you synchronize multiple copies of the same table? I download and edit. You download and edit. Now what? Will propagating my and your changes over write one another? I'd be looking for a solution to the speed problem first.
    Is it a network issue because even working with other Office files (Word, Excel) is very slow?
    Is it a db issue because queries are not optimized? Too much use of aggregate functions across the network?
    You are storing attachments in the db (not a good idea)?
    etc. etc.
    You really have 2 databases with the same name? I'd say that everybody having a complete local copy defeats the primary reasons for splitting in the first place.
    Hey Micron,

    Thanks for your response.

    there are no attachments in the db, and the queries are optimized as far as i know. It will only be one person (like an administrator) that will edit the data that is in the db that is located on the server, all regular user only have their individual copy in wich they only "replace" one table in their local db with a table that is in the db that is located on the server. This is what i need the append query for.

    Indeed it will no longer be split, as they only will have to get one table from the db that is on the network. But i could also split the local version of the access in a back-end and a front-end, the front-end's tables will be linked to the back-end and the back-end will (daily or on opening the db) copy the table that is in the db on the server. In the front-end i will have all the queries and forms etc and in the back-end only the tables to have the front-end run faster maybe? Correct me if im wrong

    The reason why i am looking into this solution is because, as mentioned, the speed is realy not good (also it runs slower once multiple users use it so a local version ensures that only one user uses it wich benefits the speed i think). It is indeed a network issue i believe because other office files also run slower than local office files.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,352
    If you are deleting a table then you are not just appending, you would be repeatedly creating table. This means cannot use autonumber primary keys.
    Exactly how do you expect a query located in backend to create table and copy records into each user copy of frontend?

    This SQL action should be in frontend. It could be a query object or accomplished with VBA executing SQL statement.
    Last edited by June7; 11-27-2019 at 01:55 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by June7 View Post
    If you are deleting a table then you are not just appending, you would be repeatedly creating table. T
    his means cannot use autonumber primary keys.

    Exactly how do you expect a query located in backend create table and copy records into each user copy of frontend?

    This SQL action should be in frontend. It could be a query object or accomplished with VBA executing SQL statement.
    Hi June7,

    I was thinking about using some VBA code in the OnOpen event of the form that the endusers will be using, ths vba code deletes all records in the local table and then i was hoping to find a way to automaticly run the append query that gets all updated records from the table on the server. The only issue i have now is how to get that append query to open or run.

    So i should have the append query in the frontend? but how does it get the records out of the table that is in the db on the server?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,428
    "how to get that append query to open or run." You could use AutoExec macro (macro can run query directly, open a form, or run code) or have code behind startup form.
    "So i should have the append query in the frontend?" Yes.
    "but how does it get the records out of the table that is in the db on the server?" Easiest is to link to that table via Linked Table Manager.

    I suggest that before making all these changes, see if opening a persistent connection will help. As a simple test, open a form in the front end that is based on a linked table in the back end. The form doesn't have to be visible. See if that improves performance.

  8. #8
    New To Access is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    21
    Quote Originally Posted by Micron View Post
    "how to get that append query to open or run." You could use AutoExec macro (macro can run query directly, open a form, or run code) or have code behind startup form.
    "So i should have the append query in the frontend?" Yes.
    "but how does it get the records out of the table that is in the db on the server?" Easiest is to link to that table via Linked Table Manager.

    I suggest that before making all these changes, see if opening a persistent connection will help. As a simple test, open a form in the front end that is based on a linked table in the back end. The form doesn't have to be visible. See if that improves performance.
    Hey Micron,

    I got it to work!

    My fault was that i did not use a linked table. I added a linked table to the local db (via linked table manager) and the table in the local db gets the data from that linked table (table in db on server), so problem solved i guess.

    Thanks for the help guys

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,428
    Sounds like you linked to a table that was also linked. I don't know if that will negatively affect performance but you should be able to link to the source directly instead?
    Anyway, it seems like you're on the right path at least.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

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

Similar Threads

  1. Replies: 6
    Last Post: 03-06-2017, 01:39 PM
  2. Replies: 4
    Last Post: 06-12-2015, 11:39 AM
  3. Replies: 14
    Last Post: 04-16-2014, 11:05 AM
  4. Replies: 5
    Last Post: 02-12-2014, 12:13 PM
  5. Replies: 0
    Last Post: 02-26-2009, 04:30 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
  •  
Tech Forums: Microsoft Office Forums