By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,969 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,969 IT Pros & Developers. It's quick & easy.

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

P: 1
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.

Oct 24 '13 #1
Share this Question
Share on Google+
1 Reply

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Oct 31 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.