SQL query will not return any records

[261298]

I have the following code to retrieve records from an access table in another database. If I run a query using a linked table it returns all records as expected. How ever with the sql I have below I recieve the error message 'No Current Record' when .MoveFirst is executed, i.e. recordset is empty. Do I need to change my openrecordset settings?

Set wrkJet = CreateWorkspace("wrkjet", "admin", "", dbUseJet)

Workspaces.Append wrkJet

cn1 = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\ugsvr01\techdocs\Drawing Modifications\X_be.mdb;" & _
"Persist Security Info=False"

Set dbAcc = wrkJet.OpenDatabase("X_be.mdb", , , cn1)

MYSQL = "SELECT Contacts.Person " & _
"FROM Contacts " & _
"WHERE Contacts![Fourman ID]= '" & DrwBy & "' "

Set rs1 = dbAcc.OpenRecordset(MYSQL, dbOpenSnapshot, dbSQLPassThrough, dbReadOnly)


With rs1
.MoveFirst
'Do Until .EOF

Forms![frmRFC]![DrawnBy] = rs1.Fields("Person")

End With

 

[JerryKlmns]

Just check to see if you have any records at all,

With rs1
If .EOF and .BOF Then
`No records found
Else
.MoveFirst
Do Until .EOF
Forms![frmRFC]![DrawnBy] = rs1.Fields("Person")
End If
End With

 

[261298]

Jerry,

I have tried your suggestion, which confirms there are no records returned. So I removed the WHERE statement. This then returns different records as I can use .Movelast to poulate the DrawnBy field with a different name to that for .Movefirst. I have double checked there is a record for the criteria I am accessing with the code, so it must be related to my WHERE statement. Although this works fine on the code I have for an ODBC connection to an Oracle table.

Gary

 

[261298]

All,

I have found the problem with my code/ data. The line below did not specify a path to the backend file. Therefore it was finding an old copy on my c:\drive which did not contain the latest records. Adding the full path has resolved my problem

Set dbAcc = wrkJet.OpenDatabase("X_be.mdb", , , cn1)