SOLVED - Problem while opening recordset

[abhid]

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!

[Joacim Andersson]

Do you have a valid connection?

[abhid]

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

[Beacon]

ADODB OR ADODC?

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:

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

[abhid]

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

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.

[veryjonny]

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

[abhid]

it is

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

[Beacon]

Try:

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

[veryjonny]

Just a check :

Is conn your connection object GLOBAL?

[abhid]

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

[veryjonny]

Everthing looks okay .

Cant figure what could be wrong.

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.

[abhid]

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

[abhid]

hey guys i solved it. The query goes like

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

[Beacon]

I still reckon your sql's wrong!

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?

[Beacon]

Oh well good!
We'd get there enventually!