refresh view

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I there a way programmatically to force a view to refresh? I realize that a
view is static and is not an ideal recordsource for a subform, but I am
forced to this solution because of network traffic exceeding what MS Access
can handle with very large tables being pulled across the network. The only
thing I can find to salvage my application is to use views so I can limit the
number of records being pulled.

Thanks
 
Access only pulls entire tables across the network if you don't have proper
indexing in place. I can query for 100 records out of 80,000 in the table in
under a second and pull a few dozen records out of a half million rows in
just slightly over a second.

What access pulls over the network is the index. It then foes back and
retrieves only the records called for in either a where clause on that index
or all of them if you haven't limited the resultset with a Join or Where
clause on an index. If you want all the records for "Arvin" make sure that
the first name field is the first called for and that it is indexed.

You can requery a subform from inside the subform with:

Me.Requery

you can do that from the main form with:

Me.NameOfSubformControl.Form.Requery
 
Thanks, Arvin. All tables are indexed on all PKs and FKs. Are you saying
Access can handle tables with millions of rows? I was told otherwise. I am
having a network issue having to do with too much traffic.
 
I generally stress test my databases with a million rows. The database I'm
working with now reads a half million rows of dBaseIV data, and processes
them, appending the data to about 50,000 rows and doing lots of calculations
resulting in finished reportable data in under 9 seconds. There are 200
people on this network, with 5 of them doing this concurrently, and there
are no network issues that I've been made aware of.

Access is designed to work very efficiently on Windows networks, so if your
network is using Linux, Novell, or some other OS, I suggest you add a
Windows Server for under $2K which will easily handle the load.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
thanks. This is bad news for me, however, because it means I haven't
identified my problem yet. I'm on a windows network, SQL 2000 is the backend.
I just had the DBA check SQL to make sure the issue wasn't there and it
isn't. My app (.mdb) is hanging, and complaining of odbc issues. Everything
runs smoothly if I reboot the server, only to return in about 15 minutes with
heavy use. Can't reproduce the problem on a standalone test machine.
You've saved me some time here, though. Appreciate it.
 
So this really hasn't anything to do with the number of rows. Access can
handle as many rows as the back-end can handle. I originally thought that
you had an Access/Jet back-end. ODBC is an Operating System applet, not an
Access one. It works with any DBMS., kind of like the printer subsystem. You
may have a problem with the driver. I suggest, first trying to rebuild the
ODBC file, by deleing the existing one, and rebuilding it. If that doesn't
work, try replacing the driver with one from the CD. If that still doesn't
work, I'd open an issue with Microsoft and check to see if there are any
issues with that driver, or if a newer one exists. Here's a KB article from
Microsoft:

How to troubleshoot connectivity issues in SQL Server 2000
http://support.microsoft.com/kb/827422/en-us
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
Back
Top