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

[New To Access]

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.

[orange]

??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.

[Micron]

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.

[New To Access]

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.

[June7]

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.

[New To Access]

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?

[Micron]

"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.

[New To Access]

"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

[Micron]

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.