>>>>>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.

Sorry but you can't have it all!!! Normally, the best solution is to forgot the Seek method and use the Where clause to retrieve to least amount of records.