>>>>Hi,
>>>>how I can insert this SQL statment :
>>>>" SELECT * FROM unit, unittype, estate WHERE untestateidno = estateid AND unttypeidno = unittypeid"
>>>>
>>>>TO this RecordSet :
>>>>
>>>>With rstUnit
>>>> .Index = "PrimaryKey"
>>>> .Open "unit", myConn, adOpenKeyset, adLockPessimistic, adCmdTableDirect
>>>>End With
>>>>
>>>>Many thanks
>>>>adsc
>>>
>>>
>>>Instead of giving a table name, give your SQL query and use the ORDER BY clause instead of using an index like this :
>>>

With rstUnit
>>>   .Open "SELECT * FROM unit, unittype, estate WHERE untestateidno = estateid AND unttypeidno = unittypeid ORDER BY FieldName", myConn, adOpenKeyset, adLockPessimistic
>>>End With

>>
>>Eric Hi,
>>
>>but I need to use the ,adCmdTableDirect to be able to use the .Seek method
>
>You can't have both!!!

So, what do u suggest Eric to solve this. the origenal statment is :
With rstUnit
.Index = "PrimaryKey"
.ActiveConnection = myConn
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Source = " SELECT * FROM unit, unittype, estate " & _
"WHERE untestateidno = estateid AND unttypeidno = unittypeid"
.Open
End with

then I changed it to this statment
.Open "unit", myConn, adOpenKeyset, adLockPessimistic, adCmdTableDirect

but in this case I could not use the sql statment to get the data from the 3 tables.

what do u suggest Eric.