How to fetch just a few records in a form

[majullian]

Hi all and thx for the help in advanced, the scenario is like this:

Backend (Database Server): MS SQL Server 2000 with Service packs
Frontend : MS Access 2000

They are working through Linked Tables so the situation that is affecting me is as follows.

I have a form with the next Data source Query

SELECT ordenes_produccion.*, clientes.nombre, vendedores.nombre, vendedores.tipo, clientes.calle, clientes.colonia, clientes.numero_interior, clientes.numero_exterior, clientes.cp1, clientes.municipio, clientes.estado, clientes.calle_alt, clientes.numero_exterior_alt, clientes.numero_interior_alt, clientes.colonia_alt, clientes.cp1_alt, clientes.municipio_alt, clientes.estado_alt, ordenes_produccion.id_usuario_alta AS opida, seguridad_usuarios.nombre, ordenes_produccion.fecha_alta AS opfa, ordenes_produccion.id_usuario_modifico AS opidm, seguridad_usuarios_1.nombre, ordenes_produccion.fecha_modifico AS opfm FROM (((ordenes_produccion LEFT JOIN clientes ON ordenes_produccion.id_cliente = clientes.id_cliente) LEFT JOIN vendedores ON clientes.id_vendedor = vendedores.id_vendedor) LEFT JOIN seguridad_usuarios ON ordenes_produccion.id_usuario_alta = seguridad_usuarios.id_usuario) LEFT JOIN seguridad_usuarios AS seguridad_usuarios_1 ON ordenes_produccion.id_usuario_modifico = seguridad_usuarios_1.id_usuario ORDER BY ordenes_produccion.id_ordenproduccion DESC;

When I try to open the form in the intranet , I mean from any computer in the intranet it works great, it takes like 15 seconds or less to open, but when
I try to open the form from another place like my house for example using a ODBC but through the internet it takes like 5 minutes to open.

I want to know if there is a way to reduce that time or if there is way to tell the form that I only wants 100 records, the last ones for example so it doesnt get all of them.

I have tried setting the "TOP 100" parameter through the SQL querie directly but it still takes the same 5 minutes and just fetch the 100 records so in that case I better get them all.

I have tried too to use a pass through querie and it works just great, it fetchs 100 records in no time but I can just navigate them, I cant update them or Delete them.

Please I really need some help here, I have a deadline and Dont know what else to try.


Best Regards
Majullian
P.S.
The form has a lot of lines of code for what I already tried as well to translate the form to a web page but is hell complicated because of that.

 

[quest4]

It sounds like you are another victim of that age old network problem, size and speed. Are you runing remote on a motem or DSL? Niether will burn up the road or set a land speed record. In either at best you can hope for is 1.5 m bps, with no one else online. Work is probably 100m bps or better, major different. Next is the dbase, it still has to go throught the same number of records so in theory it should take as long. Try runing C&R, Compact & Repair, at least you will get rid of the dead spaces. I have hear of a way around this, you can install Critx or some other metafile software. Very fast stuff, loads quick runs fast and can work on almost anything. If you have W2K, you have 2 licences to run metafiles software already. Just a little FYI 4U. LOL. hth.

 

[majullian]

Running at 64K

Yeah I am running under a 64K Cable MODEM, but I am, just making tests here , the real connection is gonna be a 512K ADSL, so , should I expect to be better over that connection?

I mean Obviously yes, but how much ? if now to me takes me like 5 min how much time do you think it will take them ?


Best regards
majullian

 

[quest4]

I realize what you are saying, but everything is limited to the speed. 512K is nothing compared to a 100 mb fiber cable which is probably what the network is, but could be even more. Critx and the other metafile software are a different technology, that is why they are so fast. You might want to go to Microsoft and check out metafile technology. Sorry I don't know any other way of dealing with your problem.