Access DAO vs ADO Recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to optimize some Access 2002 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, 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
(e-mail address removed)
 
DAO Library Reference, FindRecord
---

Hi David,

Access2002 does not automatically load a Microsoft DAO library

make sure you have a reference to a Microsoft DAO Library

Tools, References... from a module window

scroll to Microsoft DAO 3.6 Object Library and check it

also, when you DIM a variable As Recordset, use as DAO.recordset because
ADO also has recordsets

'~~~~~~~~~~~~~~~~`
here is an alternative to what you are using to find records:

Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
IDfield is the Name of the primary key field, which is in the
RecordSource of the form -- I am assuming your primary key is a Long
Integer data type (autonumbers are long integers)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I'm trying to optimize some Access 2002 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, 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!
Click to expand...
 
Just to clarify, Access does not automatically load the DAO
"type library", that is, the file with the DAO symbol names
and signatures.

The actual DAO code library is loaded: it's part of Access.

So you can use DAO objects, you just don't know what
their names or signatures are.

Also, COM objects include the names of all the methods
they support - you don't need to have the symbol name file
to use named methods. If you loaded the type library, you
would be able to call using the method signatures, but if you
don't, the method name is looked up in the COM object.

So to use DAO objects and methods without the type library:

dim rs as object.

Set rs = Me.RecordsetClone

....etc ...



strive4peace said:
DAO Library Reference, FindRecord
---

Hi David,

Access2002 does not automatically load a Microsoft DAO library

make sure you have a reference to a Microsoft DAO Library

Tools, References... from a module window

scroll to Microsoft DAO 3.6 Object Library and check it

also, when you DIM a variable As Recordset, use as DAO.recordset because
ADO also has recordsets

'~~~~~~~~~~~~~~~~`
here is an alternative to what you are using to find records:

Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
IDfield is the Name of the primary key field, which is in the
RecordSource of the form -- I am assuming your primary key is a Long
Integer data type (autonumbers are long integers)


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I'm trying to optimize some Access 2002 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, 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!
Click to expand...
Click to expand...
 
Back
Top