That's really not that many records.  Why not just have the mdb/e run
reports against linked tables or pass through queries?  It would
eliminate maintenance of a copy of your data.

Almost certainly, a report with 77,000 rows of data is, from a usability
point of view, something that needs work.  Consider creating a View on SQL
Server to ensure that the extraction and manipulation is all done
server-side, or a pass-through Query 9 (but, my experience with Access, Jet,
ODBC, and SQL Server has been that, unless your joins are what Jet considers
complex*, the SQL generated by the combination is generally efficient).  If
you only bring across the network the actual data to appear on a
"human-usable" report, I'll bet you would not have performance problems
(unless there is something seriously wrong with the Table design).

 * Jet's criteria for "complex" vary between versions, but
    if it deems "too complex" it will bring, or try to bring,
    all the underlying data to the user's machine on what
    seems to be the assumption that will be more efficient
    than doing it on the server.

Larry Linson
Microsoft Access MVP

Is this "temp" table deleted before each time you fill it up?  If so,
consider creating a *new* table each time you fill it up.  You may
want to do this in a second "temp data" mdb.  Feel free to create any
indexes you want, the jet engine will create them as the data goes
into the table.

Remember that both the front-end (the mdb that is running the query)
and the data mdb need to be on the computer's local drive.  (Having
them both on the same drive is "better".)

Is this a MS SQL Server you're pulling the data from?  If not, we need
to know that.  I know it sounds silly, but different SQL Servers
sometimes require different techniques.

As Larry suggested, if you can create a view, do so.  Views can
rapidly speed up the retrieval process.  (If this is a MS SQL Server,
running a view repeatedly will cause MS SQL Server to sort of create
it's own indexes, regardless of what's created/allowed, which also
speeds things up.)

Also, make sure you have plenty of free space on the local computer
this is running on.  Queries against a (non-MS) SQL Database can eat
memory like crazy.

You mentioned Terminal Server....  That itself can cause problems if
it's not optimized correctly.  Especially when it's a Citrix set-up.
There are times when it's *very* intelligent to hire a Citrix Guru to
set the Terminal Server up properly.  They're expensive in the short
run, but the employee time saved over a few days can offset it rather
rapidly.  (No, I'm not a Citrix Guru, but I've seen the results of
their work, and it's danged impressive!)

from OSBC (MSDN)

"ODBC provides a Driver Manager to manage simultaneous access to
multiple DBMSs. Although the use of drivers solves the problem of
accessing multiple DBMSs simultaneously, the code to do this can be
complex. Applications that are designed to work with all drivers cannot
be statically linked to any drivers. Instead, they must load drivers at
run time and call the functions in them through a table of function
pointers. The situation becomes more complex if the application uses
multiple drivers simultaneously.

Rather than forcing each application to do this, ODBC provides a Driver
Manager. The Driver Manager implements all of the ODBC functions -
mostly as pass-through calls to ODBC functions in drivers - and is
statically linked to the application or loaded by the application at
run time. Thus, the application calls ODBC functions by name in the
Driver Manager, rather than by pointer in each driver.

When an application needs a particular driver, it first requests a
connection handle with which to identify the driver and then requests
that the Driver Manager load the driver. The Driver Manager loads the
driver and stores the address of each function in the driver. To call
an ODBC function in the driver, the application calls that function in
the Driver Manager and passes the connection handle for the driver. The
Driver Manager then calls the function by using the address it stored
earlier"

****

Is it possible that ODBC Driver Manager must load the drivers for each
record (77000) because it identifies the external mdb as a separate
entitiy?

****
I do something similar to what you are talking about (as a backup
actually) by maintaining two servile files, one an MDB and the other an
ADP. The ADP is never opened other than in code from the MDB but just
functions as an ADO/OLEDB connection to the SQL-Server, while the MDB
simple offers a place from which to run the code and to temporaritly
store the tables.

The code below (it's in a statup form module) runs in an mdb (the
equivalent of your home mdb) to move SQL-data to an mdb (newly created
each time) on a different drive. I've never checked time because it's
never given me cause for concern.

Well, I ran it now starting at 05:21. The filetime of the new file is
also 05:21. The SQL database is on an internet enabled MS_SQL Server
3000 miles away. We might be talking only 10000 records max though.

Const ADPFile As String = "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\FFDBABooks.adp"
Const Backupfile As String = "D:\SpecificFiles\FFDBABooks.mdb"

Const b As String = "PROVIDER=SQLOLEDB.1;" _
       & "PERSIST SECURITY INFO=FALSE;" _
       & "INITIAL CATALOG=Database;" _
       & "DATA SOURCE=Server"
Const p As String = "UserID"
Const u As String = "Password"

Private Sub BackupSQLTablesAsJET()
   Dim c As ADODB.Connection
   Dim r As ADODB.Recordset

'    zap old tables
   Set c = New ADODB.Connection
   With c
       .Open CurrentProject.BaseConnectionString
   End With
   Set r = c.OpenSchema( _
       adSchemaTables, Array(Empty, Empty, Empty, "Table"))
   With r
       Do While Not .EOF
           CurrentProject.Connection.Execute ("DROP TABLE " &
!TABLE_NAME)
           .MoveNext
       Loop
   End With

'    refresh TableDefs
   DBEngine(0)(0).TableDefs.Refresh

'    set persist security information
'    in the adp file to true
   SecurityInformation "TRUE"

'    connect to the ADP file
   With c
       .Close
       .Open b & ";USER ID=" & u & ";PASSWORD=" & p
   End With

   Set r = c.OpenSchema( _
       adSchemaTables, Array(Empty, Empty, Empty, "Table"))

'    import the SQL tables (as JET)
   With r
       Do While Not .EOF
           If Left(!TABLE_NAME, 2) <> "dt" Then _
           DoCmd.TransferDatabase acImport, "Microsoft Access", _
               ADPFile, acTable, !TABLE_NAME, !TABLE_NAME, False
           .MoveNext
       Loop
       .Close
   End With

'    set persist security information
'    in the adp file to false
   SecurityInformation "FALSE"

'    copy the tables to the BackUp Device
   SaveAsText 6, "", Backupfile

   DoCmd.Close acForm, Me.Name

End Sub

Private Sub Form_Close()
   Application.Quit
End Sub

Private Sub Form_Open(Cancel As Integer)
   Me.Visible = False
   BackupSQLTablesAsJET
End Sub

Private Sub SecurityInformation(ByVal vPERSIST As String)
   Dim a As Access.Application
   Set a = New Access.Application
   With a
       .OpenAccessProject ADPFile
       With .CurrentProject
           If .IsConnected Then .CloseConnection
           .OpenConnection Replace(b, "FALSE", vPERSIST), u, p
       End With
       .Quit
   End With
End Sub