SqlAzure BE: possible to use native queries from the propriety sheet to populate controls?

[Etxezarreta]

Hi everyone,
I will have to put the BE of an application in SqlAzure.
What will become the "native queries" in the forms, that allow to quickly populate combo lists form the property (see below) window using values of other controls? Or is it necessary to use Sql queries in VBA procedures?
Many thanks!
Etxe.

 

[pbaldy]

If the tables are linked, the query should work as is. You'll have to judge performance and adjust if it's not good.

 

[Etxezarreta]

Ok, thank you very much.
Etxe.

 

[pbaldy]

No problem, post back if you get stuck.

 

[Etxezarreta]

A last question Paul, very similar:
I use forms and subforms always following the same pattern:
- Something like 10 listboxes in the form : the results are passed into a parameter in VBA.
- Then I run a SQL query in a VBA procedure using ADO: the parameters feed the WHERE clauses.
- The query populates a temporary table which is the date source of the subform
- The subform is refreshed
It is quite boring and long, it would be faster if I could use the query assistant of Access: but I was told it could be dangerous as I need to put the BE in SqlAzure:

In my code, for instance I replace things like "[Formulaires]![DSP_RDV_f_ConsultGestion_HistorRdv]![zlist_Professiont]" by string variables using loops , for example:

Set ctl_Profession = Nothing
Set ctl_Profession = Me.zlist_Profession

For Each var_Itm In ctl_Profession.ItemsSelected
If Len(str_CritereProfession) = 0 Then
str_CritereProfession = Chr(34) & ctl_Profession.ItemData(var_Itm) & Chr(34)
Else
str_CritereProfession = str_CritereProfession & "," & Chr(34) & ctl_Profession.ItemData(var_Itm) & Chr(34)
End If
Next var_Itm


Do you think the code below (it is only an example)would work between an access FE and a SqlAzure BE:

SELECT Contacts_t_ListeContacts.Nom_contact, Contacts_t_ListeContacts.Prenom_contact, Contacts_t_ListeContacts.ID_Contact, Contacts_t_ListeContacts.Fonction, Contacts_t_ListeContacts.Specialite, Contacts_t_ListeContacts.Statut_juridique, Contacts_t_ListeContacts.Profession, Contacts_t_ListeContacts.Adresse_1, Contacts_t_ListeContacts.Departement, Contacts_t_ListeContacts.Secteur, Contacts_t_ListeContacts.Case_du_plan, Contacts_t_ListeContacts.Ville, Contacts_t_ListeContacts.Tel1, Contacts_t_ListeContacts.Courriel, Contacts_t_ListeContacts.Commentaire
FROM Contacts_t_ListeContacts LEFT JOIN DSP_RDV_CR_t_CompteRendus ON Contacts_t_ListeContacts.[ID_Contact] = DSP_RDV_CR_t_CompteRendus.[FK_contact]
WHERE (((Contacts_t_ListeContacts.Profession)=[Formulaires]![DSP_RDV_f_ConsultGestion_HistorRdv]![zlist_Profession]) AND ((Contacts_t_ListeContacts.Departement)=[Formulaires]![DSP_RDV_f_ConsultGestion_HistorRdv]![zlist_Departement]) AND ((DSP_RDV_CR_t_CompteRendus.FK_contact) Is Null));

In a few words, it is quite a long work to work only in VBA and to populate all the subforms using ADO (or DAO) procedures: need to give values to variables, to test, tu run the queries, to create special events if the query is empty etc..
But it seems quite necessary tin my case, but maybe I am wrong on that..
Thanks in advance.

Etxe.

 

[ahmed_optom]

Hi everyone,
I will have to put the BE of an application in SqlAzure.
What will become the "native queries" in the forms, that allow to quickly populate combo lists form the property (see below) window using values of other controls? Or is it necessary to use Sql queries in VBA procedures?
Many thanks!
Etxe.

View attachment 82838

It will work fine.

I use Azure/Sql BE, sometimes odd things need to be changed, like if you have more than one "sort" then it can throw a RTE.

Luckily though going to sql/azure is largely a smooth process with plenty of improvements in terms of speed/security/stability.

 

[pbaldy]

Well, you appear to have apples and oranges. The code loops through the selected items of a multi-select listbox to create a criteria, presumably to filter something else. You can't refer to a multi-select listbox directly with a query criteria as in your query:

WHERE (((Contacts_t_ListeContacts.Profession)=[Formulaires]![DSP_RDV_f_ConsultGestion_HistorRdv]![zlist_Profession] ...

You could if it was single select, but presumably it isn't.

In general, with linked tables queries will work just as they did before the BE was in Azure. The potential problem is one of performance.

 

[Etxezarreta]

Ok, I see a way clearer picture now: I will use Access native queries for single-value critera filterings, and Sql code in VBA procedures for other cases.
Well, that was very useful. Thanks a lot again.
Etxe.

 

[pbaldy]

No problem, good luck! Post back if you get stuck.