Slow Connection to access database! ARG!!

Posted on 2003-02-20
Medium Priority
Last Modified: 2010-05-01
Hi all.
I spent the last two days reading posts about n this but I still cant wrap my head around it.  I'm using ADO and access 97 to display two grids on the screen.  When the user selects a customer from the customer grid, I want the second grid to display all the customers trasactions.

To get the transactions I store all the open sales and the closed sales into a temp table called DisplayTrans.  Everything works, but the reaction of the transaction grid is slow, about 3 seconds.  Most of my program is doing the same thing so I think its the way I'm connecting to my database.

~ summary ~
1) clear DisplayTrans table
2) insert OpenSales to DisplayTrans
3) insert ClosedSales to DisplayTrans
4) put all of DisplayTrans into a recordset
5) refresh DataGridTran grid with recordset

here is some of my code:

'My form Load
Private Sub Form_Load()

Dim i As Integer
Dim dbsile As String

    ' set connection
    With cnLive
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = gDatabaseLocation
    End With

 blAdd = False
    SQLA = "SELECT * from Customer"
    Set conn = New ADODB.Connection

    ' create a recordset using the provided collection
    TbrsCust.Open SQLA, cnLive, , , adCmdText

With conn
  .ConnectionTimeout = 15
  .CommandTimeout = 30
  .CursorLocation = adUseClient
  .ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & "DBQ=" & gDatabaseLocation
End With

Set rsconn = New ADODB.Recordset
rsconn.LockType = adLockReadOnly
rsconn.CursorType = adOpenForwardOnly
rsconn.Open SQLA, conn

Call UpdateDataGrid
Call InitFields

End Sub

'On a user clicking the Customer Grid do..

'Last Transaction Grid ~~~~~~~~~~~~

   'Empty temp DisplayTrans table
cnLive.Execute ("DELETE DisplayTrans.* FROM DisplayTrans;")

'Store open sales
apnd1 = "INSERT INTO DisplayTrans ( SNum, CustNo, WorkDate, Paid, Owing, DatePaid ) " & _
"SELECT OpenSalesHeader.osh_OrderNO, OpenSalesHeader.osh_CustomerNO, OpenSalesHeader.osh_Date, OpenSalesHeader.osh_Paid, OpenSalesHeader.osh_Owing, OpenSalesHeader.osh_paidDate " & _
"From OpenSalesHeader WHERE (OpenSalesHeader.osh_CustomerNO = " & ValueClicked & " );"

'Store closed sales
apnd2 = "INSERT INTO DisplayTrans ( SNum, CustNo, WorkDate, Paid, Owing, DatePaid ) " & _
"SELECT ClosedSalesHeader.csh_OrderNO, ClosedSalesHeader.csh_CustomerNO, ClosedSalesHeader.csh_Date, ClosedSalesHeader.csh_Paid, ClosedSalesHeader.csh_Owing, ClosedSalesHeader.csh_paidDate " & _
"From ClosedSalesHeader WHERE (((ClosedSalesHeader.csh_CustomerNO)=" & ValueClicked & "));"

'Fill DisplayTrans with transactions from user selected
cnLive.Execute (apnd1)
cnLive.Execute (apnd2)

SQLA = "SELECT * from DisplayTrans"
Set TbrsCust = New ADODB.Recordset
TbrsCust.Open SQLA, conn
Set DataGridTrans.DataSource = TbrsCust


end sub

(let me know if you need more code to look at)
Question by:Hemiboy
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
LVL 43

Expert Comment

ID: 7987750
If you want to improve speed, with connection to access 97 (only, this will not work at all with access 2000) then use the OLEDB.3.51 driver rather than OLEDB.4.0 and that is pretty much all there is to it. The access 2000 compatible OLEDB4 provider is actually much slower in connecting to access 97 than the 3.51 provider so if you are never going to use access 2000 then use the provider that was designed for that version.

If all else fails then perhaps you could consider upsizing to MSDE or MSSQL desktop edition. This would give you better response.
LVL 28

Expert Comment

ID: 7987780
Seems you are going to some lengths to fill this temporary table with data.  Have you tried just using a single query to join your Customer table and sales tables and showing the information that way?  E.g.,

SELECT * FROM ((Customer INNER JOIN OpenSalesHeader ON Customer.CustomerNo = OpenSalesHeader.osh_CustomerNO) INNER JOIN ClosedSalesHeader ON Customer.CustomerNo = ClosedSalesHeader.csh_CustomerNO)
LVL 28

Expert Comment

ID: 7987808
Actually, you are looking at two separate joins to get the data you need, in the format you want, but hopefully you get the idea.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 16

Expert Comment

ID: 7988089
I am sorry, very sorry. Azra, is there a way to contact Ark?
LVL 28

Expert Comment

ID: 7988482
Hey Richie,
Last time I talked to him I believe his email address was ark@msun.ru.  Does that address work anymore?

Author Comment

ID: 7988755
What referance do I have to have to be able to use OLEDB.3.51 ??
I keep getting "provider cannot be found" errors when I try it.
LVL 16

Expert Comment

ID: 7995337
why don't you use:
.connectionstring="Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & strDBPath
instead of set every value by its own?
To Azra:
I tried every e-mail address that i have, including one form freevbcode without success. Also posted comments in Q that he was answering but nothing appears to work. Thanks anyway and Hemiboy, please forgive me to bother you but it is for a good cause.

Expert Comment

ID: 8901371
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
LVL 28

Expert Comment

ID: 8902317
Recommendation - PAQ - No Refund
LVL 49

Expert Comment

ID: 8963956
Hemiboy, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer

Accepted Solution

YensidMod earned 0 total points
ID: 9104268
Question is PAQ'd and no points refunded.

Community Support Moderator @Experts Exchange

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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
Course of the Month13 days, 7 hours left to enroll

800 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