Very new to ADO question

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?
hrolsonsAsked:
Who is Participating?
 
HooKooDooKuConnect With a Mentor Commented:
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
 
Neil RussellTechnical Development LeadCommented:
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
 
hrolsonsAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Neil RussellTechnical Development LeadCommented:
If it the PK it should take milliseconds
0
 
hrolsonsAuthor Commented:
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
 
HooKooDooKuCommented:
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
 
hrolsonsAuthor Commented:
Same thing, if I comment out:

myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly

It's super quick.
0
 
HooKooDooKuCommented:
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
 
hrolsonsAuthor Commented:
Compile Error: Method or data member not found on "FieldValue"
0
 
Neil RussellTechnical Development LeadCommented:
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
 
Neil RussellTechnical Development LeadCommented:
MsgBox "Total Number of records = " & CLng( myRecSet!COUNTER)
0
 
HooKooDooKuCommented:
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
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.

All Courses

From novice to tech pro — start learning today.