cprobertson1
Registered User.
- Local time
- Today, 04:17
- Joined
- Nov 12, 2014
- Messages
- 36
Good Morning folks!
I'm using Access as the front end for an SQL server (I'm unsure of the underlying configuration/architecture of that server - I just know it's address and how to pull data off of it)
In this front end I have a monstrous abomination of a query - 18 tables, 30 joins involving 6 subqueries, and a bunch of headaches.
But I finally got that side of things out the way and... I've hit another problem - while I was testing I was using a static copy of those 18 tables to avoid bogging down the server - now that I've got the query made up, I've hit a bit of a snag.
When I try to run that query using live tables (instead of the imported tables) it takes several hours to complete - and it reduces server performances while it's running (though it's not as bad as it might have been!)
My interim solution/workaround was just to delete the tables and re-import them - except when I do this I lose all my relationships between the tables - which will make developing other queries using this front end more of a hassle than it needs to be.
Is there a way I can have a group of local tables that pull data from the server and save it locally on command? (i.e an import - but without deleting the relationships).
The only solution I could think of (I haven't tried this out yet!) would be to use SQL to clear the existing tables and then re-import the data (um, I'm not actually sure how to do that in VBA but it can't be too hard to them) - but that seems like a cumbersome way of doing things - so before I dedicated myself to that method, I figured I better consult the forums!
Many thanks in anticipation!
--EDIT--
May also be possible to create the 18 linked tables, in addition to 18 static tables - and just clear the static tables and copy the data from the linked data into them (or, perhaps even a query to only insert new entries - though that might be more hassle than it's worth)
I'm using Access as the front end for an SQL server (I'm unsure of the underlying configuration/architecture of that server - I just know it's address and how to pull data off of it)
In this front end I have a monstrous abomination of a query - 18 tables, 30 joins involving 6 subqueries, and a bunch of headaches.
But I finally got that side of things out the way and... I've hit another problem - while I was testing I was using a static copy of those 18 tables to avoid bogging down the server - now that I've got the query made up, I've hit a bit of a snag.
When I try to run that query using live tables (instead of the imported tables) it takes several hours to complete - and it reduces server performances while it's running (though it's not as bad as it might have been!)
My interim solution/workaround was just to delete the tables and re-import them - except when I do this I lose all my relationships between the tables - which will make developing other queries using this front end more of a hassle than it needs to be.
Is there a way I can have a group of local tables that pull data from the server and save it locally on command? (i.e an import - but without deleting the relationships).
The only solution I could think of (I haven't tried this out yet!) would be to use SQL to clear the existing tables and then re-import the data (um, I'm not actually sure how to do that in VBA but it can't be too hard to them) - but that seems like a cumbersome way of doing things - so before I dedicated myself to that method, I figured I better consult the forums!
Many thanks in anticipation!
--EDIT--
May also be possible to create the 18 linked tables, in addition to 18 static tables - and just clear the static tables and copy the data from the linked data into them (or, perhaps even a query to only insert new entries - though that might be more hassle than it's worth)
Last edited: