temp tables

[vicsaccess]

i have a situation where our network at work is extremely slow. workable for entry points but for the supervisors that need to run queries and reports its very slow due to the back and forth network travel of a query or series of queries. i remember something i read a while back about using temp tables. Does anyone have any good articles or threads that might help to educate me? my thought is that upon loading it copies the data from the linked tables into temp tables, all queries are then ran off that data and upon exit the temp tables are deleted. sounds like a good idea if its doable. any thoughts?

[NTC]

No the use of temp tables due to network slowness is not advisable. They are needed in certain situations as a scratch pad - for instance one cannot apply a delete or update query to a record set of an aggregate query - and so one must write the data out to a temp table. So the use of a temp table should only be due to a functional logical requirement.

No LAN should be slow. There is some other issue at hand. First test with front/back are on same PC and make time trials if you have not already. Slowness has various causes and one must isolate. I have seen it due to permissions in Active Directory, due to an overloaded server, due to a bad router connector, and due to electronic noise created by a nearby machine on unshielded cable.

[vicsaccess]

Thank you for the reply. i can see the advantage of using a temp table as you described. i'm just trying to avoid uneccessary frustration of individuals trying to run reports if i can. As for the network LAN, it is what it is, slow. We are currently trying to upgrade all our facilities but that is expencive and timely. Unfortunatly some of these values that are needed do require some of the queries to cascade. one value might actually need three layered queries to run to get it, thus requiring more than one attempt to get data from the network. My thought was and i've been doing a little reading on it, if i could insert the table data into a static table, then run my queries that would only need the network for the initial insert query. then everything would run at the speed of the computer rather than the speed of the network. when done have a delete query that removes the data.

[NTC]

The cheapest PCs & routers today are very fast. So it is rare to hear about a slow LAN these days. again - the complexity of what you suggest is not the way to solve a network issue. you do need to put the front/back on the same PC - which removes the network entirely - - and do your time trials there. It is always possible that it is slow in this configuration in which case there are other issues involving the design that should be addressed. But if everything runs nice and fast when the network is not part of the equation then the effort needs to be toward resolving the network delay cause (or noise cause which causes multiple retransmissions).

cascading queries is not itself a time delay cause. Sorting can be a more significant time delay once the record count begins to approach 300,000+ records. Part of this is dependent upon the horse power of the PCs.

[vicsaccess]

Again, thank you. part of what i haven't mentioned is that this is a split DB spread across the United States, not just one location. i believe i have 7 locations, each location has two entry FE's and a supervisor FE, the BE is located on the server. its going to be a little more of an upgrade than just new routers we are currently upgrading my location, current cost is $18,000 and a 3 month timeline. i have and do run the FE and BE on my computer most of the time to debug and work out upgrades just because of the slow network speed and yes it runs very fast in that environment with out the network. depending on the networks mood, daily and monthly reports for these locations can take from 3 to 10 minutes each time they are running them. that's my reasoning for wanting to make the table static within the supervisors FE upon loading then delete contents of folders upon close. I've know this was a sore point with the supervisors but didn't have the knowledge to help them. i'm hoping now that i can continue to research the concept and maybe get some help and pointers.

[NTC]

It is an incorrect and unsupported deployment method to attempt to have a WAN in between the front and back files - although now with faster internet there is some success with this. Attempting to over come that via local temp tables isn't the solution. This should be deployed via a terminal services technology (remote desktop) - there are a lot of hosting companies that offer this....try AccessHosting.com I think... otherwise set each location as their own standalone db - and daily consolidate the BEs manually...i.e. sent as email attachments or put in a common drop box. If the information flow is 1 way to HQ - this approach can work. Or finally rebuild as a web app.

[vicsaccess]

WAN?
either way, i know my network is what it is. i can't change that, just looking for ideals that will help run the queries and reports. downloading the tables onto the supervisor FE from the linked tables into static tables on the supervisors computer seems like it would work if i can find the correct method. thanks for the help

[vicsaccess]

Again I'm sorry but my network at work is what it is, slow. i am just trying to find a better way of dealing with it rather than blaming it or hoping i had access to the network fairy that could change it all tomorrow morning. If temp tables are not advisable then how could i accomplish something of the same nature, take my linked tables and make them static temporarily during the supervisors session for them to do daily or monthly task then delete that static table? how about temporarily making the linked tables local then on exit reversing back to linked for the next session using a variation of Perceptus's code for saving a linked table as local? trying to think outside the box here.

Public Sub LinktoLocal()
    Dim td As TableDef, tdCopy As TableDef
    If Left(CurrentProject.Path, 1) <> "C" Then Exit Sub ' prevents running this file from the share location
    On Error Resume Next
    For Each td In CurrentDb.TableDefs
        Debug.Print td.Name
        If Nz(td.Connect, "") <> "" And (Left(td.Name, 1) <> "~") And (Left$(td.Name, 4) <> "MSys") Then
            Set tdCopy = td ' Copy the old table def information
            Dim strSplit() As String
            strSplit = Split(td.Connect, "=")
            DoCmd.DeleteObject acTable, td.Name 'Deleting the link first ensures the re_import doesnt append an oddname
            DoCmd.TransferDatabase acImport, "Microsoft Access", strSplit(1), acTable, tdCopy.Name, tdCopy.Name
            Set tdCopy = Nothing
        End If
    Next
   Debug.Print "Done"
End Sub