Populate unbound subform with a recordset

[Pat Hartman]

Yes. Access adds the criteria in the where clause to the query that gets sent to the server.

 

[Etxezarreta]

Hello, this is an old thread, but I am facing the same problem: the reason is I want to migrate the tables into SQL Server, and I read it was better to use the ADO way, and that working with an already "access made" query was a bit risky.
I am not sure it is worth the headache though.
Could you share your experience on this matter please?
Many thanks in advance.
Etxe.

Thanks a lot Pat,
This is the way I am trying to work.
Here is a short sample of code I use, does it fit with good practices?

str_SQL = "SELECT t_ListeContacts.[Nom_contact], t_ListeContacts.[Prenon_contact]," & _
"t_ListeContacts.[Nom_structure], t_ListeContacts.[Fonction], t_StatutJuridiqueSoignants.StatutJuridiqueSoignant, " & _
"t_IntitulesProfession.Profession, t_ListeContacts.[Departement], t_ListeContacts.[Ville] FROM t_StatutJuridiqueSoignants " & _
"INNER JOIN (t_IntitulesProfession INNER JOIN t_ListeContacts ON t_IntitulesProfession.ID_Profession = t_ListeContacts.FK_Profession) " & _
"ON t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant = t_ListeContacts.FK_Statut_juridique"


If (Len(str_CritereDepartement) <> 0) Then
str_SQL = str_SQL & " Where t_ListeContacts.Departement In (" & str_CritereDepartement & ")"
End If
Debug.Print str_SQL

oBjRcrdst_RecordSetSource.ActiveConnection = objConnection
oBjRcrdst_RecordSetSource.CursorType = adOpenDynamic
oBjRcrdst_RecordSetSource.LockType = adLockOptimistic

oBjRcrdst_RecordSetSource.Open str_SQL
With oBjRcrdst_RecordSetSource
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
Set Me.sf_Essai.Form.Recordset = Nothing
Set Me.sf_Essai.Form.Recordset = oBjRcrdst_RecordSetSource
End If
End With

 

[Pat Hartman]

Good practice is to do things the Access way when you're using Access, the COBOL way when you're using COBOL, the C+ way when you're using C+.

You can write as much code as you want or as little as you need. Your choice.