SQL query will not return any records

[Guest]

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

 

[MacDermott]

It looks to me as if you've got an ADO connection string, while the rest of
your code is using DAO.
Try something more like this:

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(\\ugsvr01\techdocs\Drawing
Modifications\X_be.mdb)

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

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

Of course, you could create your link programmatically, and just use a
DLookup to retrieve the single item of data you need.
If there's a need, you can then drop the link...