Using the same table and records as posted in #12, this works when
1. Set rs = db.OpenRecordset("select * from acanvend", dbReadOnly) or
2. Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot, dbReadOnly)
3. Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot)
It results in NoMatch (doesn't work) when using dbOpenDynaset
Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset) OR
Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset, dbReadOnly)
Code:
Sub DoubleQuoteAgain()
Dim rs As DAO.Recordset
Dim findcriteria As String
Dim db As DAO.Database
Set db = CurrentDb
'Set rs = db.OpenRecordset("select * from acanvend", dbReadOnly) 'works
'Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot) 'works
'Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot, dbReadOnly)'works
'Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset) 'does not work
Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset, dbReadOnly) 'does not work
findcriteria = "acanvend.terr_cd = '""D''Loro""'" ': from Cheekybuddha
Debug.Print findcriteria
rs.FindFirst findcriteria
If rs.NoMatch Then
Debug.Print "No matching record was found"
End If
Debug.Print rs!vend_num; rs!TERR_CD
rs.Close
If Not rs Is Nothing Then Set rs = Nothing
End Sub
Sub DoubleQuoteOnceMore()
Dim rs As DAO.Recordset
Dim sql As String
Dim db As DAO.Database
Set db = CurrentDb
sql = "SELECT * FROM acanvend WHERE acanvend.terr_cd = '""D''Loro""'"
Debug.Print sql
'Set rs = db.OpenRecordset(sql , dbReadOnly)
'Set rs = db.OpenRecordset(sql, dbOpenSnapshot) ' identical to above
'Set rs = db.OpenRecordset(sql, dbOpenSnapshot, dbReadOnly)
'Set rs = db.OpenRecordset(sql, dbOpenDynaset)
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
If Not rs.EOF Then Debug.Print rs!vend_num; rs!TERR_CD
rs.Close
If Not rs Is Nothing Then Set rs = Nothing
End Sub
This resulted in SELECT * FROM acanvend WHERE acanvend.terr_cd = '"D''Loro"'
4 "D'Loro"
with all 5 versions of the Set rs....
?? Is it possible that FindFirst is not compatible with dbOpenDynaSet??
According to M$oft documentation
FindFirst
Locates the first record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).