copy table from BE to FE before running big report

hi guys, i've got a big report that requires accessing of the tables on the network quite a few time.

i've got local tables and network tables nicely split.

i would like to copy all the network tables to the FE first before running this report if not it would take ages and clog up the network. what's the best way of doing this guys? i did some research and seems like make table is the way to go.

how do yall usually do this yourselves? thanks guys. = )
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For big reports I often use some intermediate table(s). You can save results of your queries to this table (it could be done in BE only) and run report from saved data.
Gustav BrockCIOCommented:
Don't do that.
Have or create a local database BE, export the tables in question from the network BE to the local BE - or simply filecopy the network BE - and then relink as needed to the local BE and run your reports. When done, link back to the network BE.

Dale FyeCommented:
Check out my article on temp tables.

I'm on my iPad, so cannot post the link.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
<hi guys, i've got a big report that requires accessing of the tables on the network quite a few time.>
Another line of thought...

First, how long, in chronological terms, is this report taking to generate?
Speed/time is relative.

I mean, if the report takes 1 minute to generate, that really a deal breaker?
Are you generating this report every 10 minutes?

If the report takes more than a minute to run, then you may want to take a closer look at the report itself.
If the report is loaded with Aggregate functions, Complex sorting and grouping, Sub reports, has graphics, vba code, charts...etc, may still be "Slow", even with temp/local tables.

I have some report that run from local tables that take over a minute to run.
If your report is still "slow" with local tables, then what are you going to do?

In some cases the very act of creating systems that bring in/refresh temp tables will slow down a system...
(these systems need good error-handling, and may need refreshing, and possibly rollback systems in place)

Also consider that opening the Report in 'Report View' may speed things up, because the report will not spend all of its resources "rendering" every aspect of the report.

So in essence here you have asked, then answered, your own question:
"My report is slow, I will fix it with temp tables, how do you guys do this?"

When your real question perhaps should have been:
    "My report is slow, can you help me identify why?"

Perhaps a temp/local table will be warranted, ...or perhaps you could use a Grouped report instead of a report with sub reports,  or set up more efficient indexes, or simplify the recordsource, ...or add the aggregate functions into the recordsource instead of calculating them on the report, ...or you could filter the report to show less records, ...or exclude unneeded fields, ...etc

In other words, if you post a sample of the report, and include the Recordsource (SQL).
This would go a long way in identifying any issue with the report itself.

Make sense?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
whao Dale, i read your article and i really like it a lot = ) i never thought of option #3 using a separate database. also the code that you posted in the article is really good too = ) perhaps sideline - but one thing i really like is how you've put a line break for each of your function's parameters - makes it a lot easier to read. that's something i'm definitely learning from you Dale = )

Jeff thanks for your insights! i think it's really important and good that we take some time away from our parochial engineering fixated point of view to step back and ask ourselves - what is the bigger problem? what are we trying to solve, how can we solve it? often times due to my inexperience i don't know what else can be done, and also at time being so fixated on solving a certain problem i neglect the bigger picture. definitely very good questions you're raising to me and i'll be working that out in my solution = ) thanks Jeff!

thanks als315 and gustav too for your insights and help! = )
Jeffrey CoachmanMIS LiasonCommented:
I understand exactly, ...and the way you evenly distributed the points shows us that you see the value in having multiple alternatives and Points of view.


developingprogrammerAuthor Commented:
thanks for your help all along the way Jeff, without you and without all the other experts, i really don't think i can make it - correction --> i'd fail without a doubt ha.

was suffering from the very first stress breakdown earlier this week ha, was totally destroyed for a few days, but that led me to learn that 1) viewing from top down approach - this is good for planning and forecasting, 2) working from bottom up approach - this is the only way to actually get things done in any industry, anywhere in the world. when bosses wanted things faster and faster (from my - an absolute developing programmer - but i'm very lucky to have this chance bestowed upon me on the other hand ha), i kept squeezing my brain stressing myself about the top down approach until i snapped. then i realised in convalescence that what's possible is possible. what's not possible is not possible. regardless which boss asks for it, regardless if Barack Obama wanted me to finish my project in 3 days - if it's not possible it's not possible. well we've got the scrum stuff and all but that is more cerebral and planning but i think this lesson is about how to ignore management and just work towards a surely failing deadline happily. no programmer can work when he is unhappy and brain under total lock down. no happiness = no creativity.

the other big thing i learnt - i thing probably the most important - outside technical skills - which your comment made me think of this is --> perception is everything. social perception is congruent to social value and for us to succeed in our jobs, it's not about be a technical wizard many times but rather knowing how to handle the acceptance of workload in a way that allows us to be perceived as yes productive and a valuable member of the team - instead of being a yes man and then taking on the entire world and when we fail our bosses say --> but YOU told us you could do it. so you've failed.

well i was stupid enough to think i could do so much - well i can but just need more time. so my time schedule was dependent on their absurd understanding (or rather non-understanding) of IT. oh it's a computer and he's a smart guy, he can do it in 9 days. ok so why not 9 hours then or 9 minutes? i realised it's not about saying no but rather how to say no in a high value way (as per the video i've attached below about the train example). cause if we go down the technical wizard way, soon they expect us to be technical gods then work gods and then slowly.... just gods. and if we can't do what god can do - bang, we're shot dead.

well anyway really good learning lesson, guess i never reached over my limit before in stress but this time yes. good learning lesson. i still love programming - or rather even more cause i know this is what i want to do my whole life - whether it's a full time job or just a skill. whilst other loafers in the organisation loaf and loaf and loaf, i'll goph and goph and goph and one day my hardwork (and now smart work ha) will bring me good personal results whilst they will always be part of their little corporate game. yes earn a lot yes but what is a lot? and what is meaning in life? i've got more pride than just to play corporate games and talk for a living! i'm a roll up the sleeves Joe Pesci kinda guy! haha = )

thanks Jeff once again for all your support!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.