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: 158
  • Last Modified:

Slow Connection to access database! ARG!!

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)
1 Solution
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.
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)
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!

Richie_SimonettiIT OperationsCommented:
I am sorry, very sorry. Azra, is there a way to contact Ark?
Hey Richie,
Last time I talked to him I believe his email address was ark@msun.ru.  Does that address work anymore?
HemiboyAuthor Commented:
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.
Richie_SimonettiIT OperationsCommented:
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.
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?
Recommendation - PAQ - No Refund
Hemiboy, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer
Question is PAQ'd and no points refunded.

Community Support Moderator @Experts Exchange

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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