472,992 Members | 3,200 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,992 software developers and data experts.

Increase speed when retrieving data from a Hosted SQL SERVER into VBA


I need to increase the speed when retrieving data from a hosted SQL Server into VBA. I'm using simple SELECT statements.

How important is the speed of my Internet connection? (I have 4mbits)

Should I index my tables or use Stored Procedures? Or is there a kind of "flush" function or readonly function or...

Or is it simply a question of the amount of data transmitted over the Internet?

Thank you for your time and help,

Jakob Petersen
Feb 6 '07 #1
3 3613
1,017 Expert 512MB
Definitely indexes should be on a database.
But not just any indexes it should be carefully thought of depend on kind selections you do and database structure as well.

Stored procedures can increase some speed but not like indexes.
Feb 6 '07 #2
3,237 Expert 2GB
Welcome to The Scripts.
Indexes will help you out, but it really depends on the size of the database, as well as the size of the datasets and the complexety of your query.
Perhaps if you gave us a little more information, we could assist you in speeding things ups.
Feb 6 '07 #3
the database is about 30mb. The queries are quite simple - just "SELECT xx, xx FROM xx WHERE xx=yy".

I just converted my Access 2003 database, which was stored locally, to hosted SQL Server. An update which previously took about 10 seconds now takes about 2 minutes.

I tried to limit the number of rows which helped quite a lot. Then I also tried to change the format of a field from "nText" to "nVar" that also helped a lot. It seems like the amount of data transmitted is the single most important factor - even though I'm running on a 8mbit line.

Most of my data I just need in arrays - I usually don't update any tables. My thought was that it should be possible to send the query result from the SQL Server in some kind of "flat" text array format and not in a recordset format.

I'm using ADODB in VBA with code lines like:

Expand|Select|Wrap|Line Numbers
  2. Set rsData = New ADODB.Recordset
  3. rsData.Open sSql, conGPAM, adOpenKeyset, adLockOptimistic
  4. aData = rsSCD.GetRows(-1)
I have tried to change LockType and CursorType, but that doesn't really make a difference.

Any suggestions?

Feb 7 '07 #4

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

Similar topics

by: WindAndWaves | last post by:
Hi Gurus I am building my first ever PHP site. Should I worry about speed? These are the parameters of my site - MySQL database with about 500 records (about 50 fields each) and a couple...
by: Olaf Gschweng | last post by:
We're new into DB2 and have some problem with DB2 8.1 (?) on a Linux system. We load some big tables of a DB2 database from files every day. We do a "DELETE FROM table" for each table and then we...
by: Neil | last post by:
I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL 7 back end. The view is scrolling very slowly. However, if I open the view in an ADP file, it scrolls quickly. I...
by: Mike Kelly | last post by:
Hi. I've built a page using standard ASP.NET 2.0 features and when I upload a large file (>20MB) to our intranet server, I get a paltry 100KB/s on our 100Mb/s LAN. Simply copying the file, I get...
by: Matt.W.Stephens | last post by:
Here is the scenario. We have a database with patient case information. This database was previously solely used in Access. The problem is our users connect to this database (located in California)...
by: Frinavale | last post by:
Hi there :) I've finally moved my Online Registration project on to the box where it will be hosted. This new server is running IIS6. After moving the project to the new server and running...
by: tc | last post by:
Hi. I have a customer who is running our .net application, multiple clients connecting to an Access database. This ONE customer reports a great speed degredation when more than one client is...
by: AliRezaGoogle | last post by:
Dear members I am working with a 2000 GH P4 Intel, and 512GB RAM. I have a long list matrix 3000 * 15,000 of type double. I have a calculation procedure which can be executed on any single...
by: Devang | last post by:
Hello, I am using php script to upload file. some times if file size is too big(1GB) it takes too much time to upload. Can someone suggest me the way to increase upload speed. thanks
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.