DAO and ADO with Access 2002 problem

I'm trying to optimize some very old code, and I've hit a stumbling block. We
use Access as our frontend, and SQL Server as our backend.
Here's a code fragment from the old implementation of a search for a
specific record:
=================================
' Set focus to NdvID and search
API_Sleep 200
'MsgBox "DoCmd.FindRecord " & vNdvID & "," & acEntire & "," & False & ",
," & False & ", ," & True
Me.txtNdvID.SetFocus
DoCmd.FindRecord vNdvID, acEntire, False, , False, , True
API_Sleep 400
''' Verify requested NdvID was found
If CStr(Me.txtNdvID) <> vNdvID Then
If Not DoRefresh Then
''' If not found and recordset not refreshed, self-call with
refresh
Call DoFindByNdvID(vNdvID, True)
Else
''' If not found and recordset already refreshed, give failure
MsgBox "Couldn't find Individual.", vbInformation, "Search:
Failed"
End If
End If
==============================
I dont like the DoCmd.FindRecord, it takes too long, and as you can see I
need the API_Sleep their.

Here's the general idea of what I'd like to try:
==============================
If Not IsNull(vNdvID) Then
If DoRefresh Then Me.Requery

Set RS = Me.RecordsetClone

'Move first and search
If Not RS.BOF Then RS.MoveFirst
RS.FindFirst "[NdvID] = " & vNdvID

'Determine if record was found, notify if not found
If Not (RS.EOF Or RS.BOF) Then
If RS!NdvID = Val(Nz(vNdvID, -1)) Then
Me.Bookmark = RS.Bookmark
Me.cboFind = Null
Else
MsgBox "Couldn't find Individual.", vbExclamation, "Search:
EOF/BOF"
End If
Else
MsgBox "Couldn't find Individual.", vbInformation, "Search:
Failed"
End If
End If
===============================
Thats a kind of a rough idea, anyway...
The problem is that when I try to do this, I always get a type mismatch error.
I get the type mismatch, when I'm doing the assignment set RS =
Me.RecordsetClone

I take this to mean that the forms recordsource is not a DAO source, so
thats why I get the type mismatch. I looked at my database conection string
for the currentproject, and it is using SQLOLEDB. I guess that is ADO.

I have alot of questions about the database connectivity details, but my
bottom line quesstions is this: Can I use the code that I included that opens
a DAO recordset with this application?
If not, can this strategy still work?

Thanks for any help!

--
David Buttrick, SQL DBA
Parents as Teachers National Center
2228 Ball Drive
St. Louis, MO 63146
(314) 432-4330 x282
david.buttrick.TakeThisOut@parentsasteachers.org

"David" wrote in message

> <snipped>
> Thats a kind of a rough idea, anyway...
> The problem is that when I try to do this, I always get a type mismatch
error.
> I get the type mismatch, when I'm doing the assignment set RS =
> Me.RecordsetClone
>
> I take this to mean that the forms recordsource is not a DAO source, so
> thats why I get the type mismatch. I looked at my database conection
string
> for the currentproject, and it is using SQLOLEDB. I guess that is ADO.
>
> I have alot of questions about the database connectivity details, but my
> bottom line quesstions is this: Can I use the code that I included that
opens
> a DAO recordset with this application?
> If not, can this strategy still work?
>
> Thanks for any help!
>

Your suspicions are probably correct. However, the FindRecord method of
DoCmd is entirely MSAccess dependent. DAO was once the default, then they
switched to ADO, and now I believe it has been flipped back to DAO. Can't
recall what 2002 is at the moment - but doesn't matter as it can be set to
whatever you want.

In light of this, and the fact you say you have other connectivity and
performance issues, you need to post in a MSAccess newsgroup. This group is
primarily devoted to using the DAO library within a Visual Basic
application. VBA is like, but not the same as VB.

hth
-ralph