Using MoveNext in VBA leads to slow communication time - better solution?

[rjallison]

Hi All,

Thanks for the help.

I have a little problem, and i'm hoping somebody here will be able to tell me where i'm going wrong.

I have a program written in VBA that connects to an SQL database. The VBA program reads in the data from the SQL using the MoveNext command - so it reads each individual line in the database, does something with it in the VBA, and then moves to the next line. Wash, rinse, and repeat. This happens for 20,000 rows.

My problem is that the server is in London, and i'm in Sheffield - for my London colleagues this only takes a second or two to do, while for me in Sheffield it takes around 10 times longer! Our machines are equivalent, so i can only assume it is the communcation time.

It isn't a massive issue, but it is something i would like to investigate.

I've tried to work out how to take the entire entry into VBA in one go, which would reduce the communication time, but so far i haven't be able to work it out.

I'm fairly new to both SQL and VBA.

Thanks!

[Seth Schrock]

When you say VBA, what program are you using (ie. Access or Excel)? We would also need to see your code to be able to provide any assistance in making it run quicker.

However, I very much doubt that you will be able to get the time down to the two to three seconds that your London users experience. I use Access connected to a SQL Server for several databases and the users at other locations experience a big lag compared to the local users. How I fixed it was to host the front end on an existing Terminal Server and have the users use a remote desktop connection to view the database. This however would be an expensive solution if you don't already have a terminal server with the licencing needed available. Your best bet is probably to create a web program and host it internally.