SOLVED - Problem while opening recordset

I am opening ADO.recordset object with following query

SELECT E.empName,E.basic,E.current,S.increament,S.dateOfInc FROM employeeMaster E,empSalary S WHERE S.empCode = E.empCode

But it is giving me error like
"Method 'Open' of object '_Recordset' failed"
I have checked that recordset is declared and set and query is also working.
Please help!

Do you have a valid connection?

of course i do..
Other recordsets are working fine.

ADODB OR ADODC?

VB Code:

SELECT E.empName,E.basic,E.current,S.increament,S.dateOfInc FROM employeeMaster E,empSalary S WHERE S.empCode = E.empCode

empSalary S
Is that meant to be a space?

Do you just want all the fields?

Else:

VB Code:

SELECT E.empName & E.basic & E.current & S.increament & S.dateOfInc FROM employeeMaster E & empSalary S WHERE S.empCode = E.empCode

its ADODB.
about 'empSalary S'. Yes it is space.
i am using S as alias to empSalary table.

VB Code:

SELECT E.empName,E.basic,E.current,S.increament,S.dateOfInc FROM employeeMaster E,empSalary S WHERE S.empCode = E.empCode

this query is working properly in SQL view for MSAcces. It is returing expected no. of rows.
I checked with a simple query like 'select * from empsalary'.
It opened the recordset properly.
Problem is the query mentione above is not working with recordset.
What it could be?
Thanks for the efforts.

Pls. paste ur code - just before the open method - I feel there lies the prob.

it is

VB Code:

Private Sub setSalaryData()
Set rsNavigateSalaryData = New Recordset
strQuery = "SELECT E.empName,E.basic,E.current,S.increament,"_
  & "S.dateOfInc FROM employeeMaster E,empSalary S WHERE " _
  & "S.empCode = E.empCode"
  rsNavigateSalaryData.Open strQuery,conn, adOpenKeyset, adLockOptimistic
End Sub

Try:

VB Code:

Private Sub setSalaryData()
Set rsNavigateSalaryData = New ADODB.Recordset
strQuery = "SELECT E.empName,E.basic,E.current,S.increament,"_
  & "S.dateOfInc FROM employeeMaster E,empSalary S WHERE " _
  & "S.empCode = E.empCode"
  rsNavigateSalaryData.Open strQuery,conn, adOpenKeyset, adLockOptimistic, adcmdtext
End Sub

Just a check :

Is conn your connection object GLOBAL?

thanks Beacon but it still has the same problem.
and yes veryjonny, my conn object is global.

Everthing looks okay .

Cant figure what could be wrong.:confused:

Just two suggestions :

1)Check after removing the cursorlocation,locktype and the cmdText thing.

2) Just change the query - use one table instead and check.

i am really frustrated :(
may be i should try another query.
I am working on it...
Thanks guys for your help.

hey guys i solved it. The query goes like

VB Code:

SELECT employeeMaster.empName, empSalary.increament, empSalary.dateOfInc
FROM employeeMaster INNER JOIN empSalary ON employeeMaster.empcode = empSalary.empCode
WHERE (([employeeMaster].[empcode]=[empsalary].[empcode]))

I still reckon your sql's wrong!

VB Code:

Private Sub setSalaryData()
Set rsNavigateSalaryData = New ADODB.Recordset
strQuery = "SELECT E.empName,E.basic,E.current,S.increament,"_
  & "S.dateOfInc FROM employeeMaster E & empSalary S WHERE " _
  & "S.[empCode] = E.[empCode] ;"
  rsNavigateSalaryData.Open strQuery,conn, adOpenKeyset, adLockPessimistic, adcmdtext
End Sub

Changed the lock just in case!

S and E are tables correct?
I still dont like that FROM bit though? Which ones are the tables?

Oh well good!
We'd get there enventually!