Improve Speed VB6 C/S App  w/DAO to VPN Connection

Posted on 2003-03-28
Medium Priority
Last Modified: 2012-05-04
For the past couple years, I've been writing and maintaining a client's client/server application in VB6 using SQL Server 7.  My connections are ODBC using DAO data controls (yeah I know.. ;-( ).

Due to the large number of ad-hoc queries, I haven't even looked at using stored procedures.

I'm now up against deploying my application on client machines over a VPN T1 line (and/or cable modem) and accesses are painfully slow and inefficient.  I must speed them up significantly, in short order.

It is not an option to rewrite my app using other than DAO.

Besides the data controls, I'm also using DAO.Recordsets to return results.  This seems to be just as slow, if not even slower!  For example:

Sub FillContractTypesCombo(Cbo As fpCombo)
  Dim RS As DAO.Recordset
  Dim SQL As String
  On Error GoTo FillContractTypesComboError
  SQL = "SELECT ContractTypeID, Description FROM ContractTypes"
  SQL = SQL & " ORDER BY ContractTypeID"
  Set RS = dbLimo.OpenRecordset(SQL, dbOpenSnapshot)
  On Error Resume Next
  On Error GoTo FillContractTypesComboError
  If RS.RecordCount > 0 Then
      With Cbo
          Do While Not RS.EOF
              .AddItem Trim$("" & RS!ContractTypeID) & vbTab & Trim$("" & RS!Description)
      End With
  End If
  Set RS = Nothing
  Exit Sub
  LimoLog LOG_ERROR, "FillContractTypesComboError"
End Sub

Any suggestions are welcome.  Please ask for whatever more info you might need.  This has gotten to be a real problem.

Question by:dslocum
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

QJohnson earned 500 total points
ID: 8227572
Perhaps some sort of architecture change may help.  If ALL your data is coming directly from the SQL Server back-end, perhaps you should consider caching some of it locally (the stuff that doesn't change every day, at least).  Let's say your choice for the caching db is a JET mdb.

You could put it on your local server.  All the users would have access to it there from their workstation.  The tables on the SQL Server to which you go most frequently could be set up as linked tables.  This will actually speed up your ad hoc queries (or should) because ODBC doesn't have to work out schema information during the query.  That information is stored locally in JET with the other connection information about the linked table(s).  For data that changes "semi-frequently" you could have a Refresh Local Data procedure that would take care of the dirty work all at once and might only need to be run once or twice a day (you may even be able to partion the data into category according to how static->dynamic the contents are and have different strategies for each category.

Alternatively, you might decide to have a JET mdb local to each user's machine.  This is actually a pretty nice choice because JET is LOTS LOTS faster when you aren't going over the network to use its data.  Depending on the number and size of the local tables and how long it takes to create and populate them, this could be an excellent performer.  Basically, you would use canned SQL DDL to create the database and table structures, link to SQL Server as discussed above, and populate the data cache tables for all your look up lists.  When the app closes, you delete the local database.  The basic idea is the same as the network location, but all the list building for your controls will be SO MUCH faster because you aren't reading across the LAN (never mind across the VN).

Good luck.

Assisted Solution

GivenRandy earned 500 total points
ID: 8227777
For any time performance issues, I first go to DevPartner Studio:


They have a 14-day trial period. I have used it on many projects with great success (including finding database bottlenecks that I sped up 100 times faster). If you're like me, you will end up buying it!

Expert Comment

ID: 8531390
Hi dslocum,
This old question (QID 20566755) needs to be finalized -- accept an answer, split points, or get a refund.  Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.

Expert Comment

ID: 9440771
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

 -->Split between QJohnson and GivenRandy

Please leave any comments here within the next seven days.


Cleanup Volunteer

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question