Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

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

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.

2 Solutions
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.
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!
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.
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now