[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Very new to ADO question

Posted on 2011-10-11
12
Medium Priority
?
239 Views
Last Modified: 2012-05-12
I'm having trouble and just getting started with ADO.  I've been using DAO.

In this code:

                sqlStr = "select * from photos where bookmark='" & item_number & "'"
                myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

I'm just trying to see if a record exists in a table.  It is very slow and the query ends up timing out.  My approach must be wrong.  Any ideas?
0
Comment
Question by:hrolsons
  • 4
  • 4
  • 4
12 Comments
 
LVL 16

Accepted Solution

by:
HooKooDooKu earned 2000 total points
ID: 36951699
I posted some code samples in your other question that might help in creating ADO recordsets when you're used to creating DAO recordsets.

Try replacing your Open statement with the following:
myRecSet.CursorLocation = adUseClient
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly

Just after opening your DB, you can add the following command to try to avoid Timeout Errors
MyConnObj.CommandTimeout = 0
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36951719
Well firstly Why SELECT * when your just checking for the existance a bookmark = item_number ?
Secondly do you store images in the DB?
Thirdly how many records do you have?
Fourthly is the field Bookmark indexed?
0
 

Author Comment

by:hrolsons
ID: 36951814
1st:  You are correct, I should have only selected bookmark.
2nd:  I only store the location of the photo, not the photo itself.
3rd.  160,000 records
4th:  Yes, it is the PrimaryKey.

HooKooDooKu, you're solution worked.  It was still much slower than I expected.
0
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 37

Expert Comment

by:Neil Russell
ID: 36951858
If it the PK it should take milliseconds
0
 

Author Comment

by:hrolsons
ID: 36952119
This takes about 25 seconds:

                sqlStr = "select bookmark from photos where bookmark='" & item_number & "'"
               
                myRecSet.CursorLocation = adUseClient
                myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly

                MsgBox "Total Number of records = " & myRecSet.RecordCount
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 36952223
Your problem might be the "myRecSet.RecordCount"

I remember that when we went from DAO to SQL, if we ever needed to get a record count, it was faster to run a separate query to specifically count the number of records that matched a particular query than it was to ask the record set how many records there were.  I seem to also recall, to get an accurate count, you had to do a MoveLast (depending upon if the cursor was client or server side) before the did a .RecordCount.

Replace the MsgBox with a simple MsgBox "Records Found" and check your timing again.
0
 

Author Comment

by:hrolsons
ID: 36952260
Same thing, if I comment out:

myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly

It's super quick.
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 36952490
Try this:

sqlStr = "SELECT COUNT( bookmark ) AS COUNTER FROM photos WHERE bookmark = ""& item_number & "'"
myRecSet.CursorLocation = adUseClient
Call myRecSet.Open( sqlStr, MyConnObj, adOpenForwardOnly, adLockReadOnly)
MsgBox "Total Number of records = " & CLng( myRecSet.FieldValue("COUNTER") )
0
 

Author Comment

by:hrolsons
ID: 36952498
Compile Error: Method or data member not found on "FieldValue"
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36952787
What you havent said or I missed it is what the backend is? Whats the database? Where is it? On the PC? On a local Server? On the internet?
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36952791
MsgBox "Total Number of records = " & CLng( myRecSet!COUNTER)
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 36953166
Sorry, I'm looking at 10 year old code... FieldValue() was one of my functions (it encapsulated the logic to get the value from a DAO field, ADO field, or Oracel DB field depending upon which DB I had openned).

Does the MsgBox come up before 25 seconds now?
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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

872 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