Access

D

docsql

We have an access db on a sever at HQ that one of the field office is
accessing. The problem is that accessing the DB is on the slow side. The
file is 8MB and we have only a 128k connection to HQ. I have tries to
compress the DB to 3mb. But once I open it, the size jumps back to 8MB.
How can I speed things up without any $ cost?
Help!!
 
B

Brian

How are they accessing it? With a mapped drive across a VPN? On a terminal
server/

Running Access directly across a remote connection is unworkable. Instead,
set up the application on your terminal server or on a Windows XP Pro
workstation hosting an RDP session so that all the processing is local.
 
D

docsql

Can I please ask you why running Access is directly across a remote
connection is unworkable? Does the whole file tries to download to the
desktop?
 
E

Ethem Ýþcan

haber iletisinde þunlarý said:
We have an access db on a sever at HQ that one of the field office is
accessing. The problem is that accessing the DB is on the slow side. The
file is 8MB and we have only a 128k connection to HQ. I have tries to
compress the DB to 3mb. But once I open it, the size jumps back to 8MB.
How can I speed things up without any $ cost?
Help!!
Click to expand...
 
B

Brian

It's more complex than that. It has to do with the ways in which Access opens
& updates records. Think about the logic here. This is not like Excel, in
which you open a spreadsheet, make changes, and then save it.

To the contrary, database programs such as Access save as you go,
record-by-record. When you open the file, Access (more-or-less) opens a link
to the file, loads basic information about it, but then retrieves and/or
saves information from the tables as you process individual records (pardon
my over-simplified explanation here, all you MVP's).

Consequently, when you open a form that is bound to a table, Access
retrieves the information from the file necessary to show you the records. In
the same way, every time you drop down a combo box whose RowSource is a table
or query, Access requests the list of entries from the file. While it can be
done, all of this is so slow that it can make your application virtually
unusable.

There are many ways to deal with this, such as:

Distribute a full copy of the database to the remote office & set up a
replication scheme (search the forums for "replication" to get detailed help)

Distribute a full copy of the database & set up a batch export/import
process that pushes changes between the host & remote databases

Run the application across a remote control session (as in my original post)
so that the processing-to-database connection is on the same computer or
local network and just the mouse/keyboard/video is running across the WAN/VPN.
 
D

docsql

Thanks Brian. The explanation is a big help.
Brian said:
It's more complex than that. It has to do with the ways in which Access
opens
& updates records. Think about the logic here. This is not like Excel, in
which you open a spreadsheet, make changes, and then save it.

To the contrary, database programs such as Access save as you go,
record-by-record. When you open the file, Access (more-or-less) opens a
link
to the file, loads basic information about it, but then retrieves and/or
saves information from the tables as you process individual records
(pardon
my over-simplified explanation here, all you MVP's).

Consequently, when you open a form that is bound to a table, Access
retrieves the information from the file necessary to show you the records.
In
the same way, every time you drop down a combo box whose RowSource is a
table
or query, Access requests the list of entries from the file. While it can
be
done, all of this is so slow that it can make your application virtually
unusable.

There are many ways to deal with this, such as:

Distribute a full copy of the database to the remote office & set up a
replication scheme (search the forums for "replication" to get detailed
help)

Distribute a full copy of the database & set up a batch export/import
process that pushes changes between the host & remote databases

Run the application across a remote control session (as in my original
post)
so that the processing-to-database connection is on the same computer or
local network and just the mouse/keyboard/video is running across the
WAN/VPN.
Click to expand...
 
Top