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
        .Open
    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
  .Open
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

DataGridTrans.Refresh

end sub


(let me know if you need more code to look at)
THANKS IN ADVANCE!
HemiboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
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.
0
AzraSoundCommented:
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)
0
AzraSoundCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

    Save as PAQ -- No Refund.

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

YensidMod
Community Support Moderator @Experts Exchange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.