SQL query will not return any records

[Gary]

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

 

[Chris2]

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

Gary,

Please post all variable declarations involved in this code
segement, as well as all object SETs involved.


Sincerely,

Chris O.