SqlAzure BE: possible to use native queries from the propriety sheet to populate controls? (1 Viewer)

Etxezarreta

Member
Local time
Today, 14:41
Joined
Apr 13, 2020
Messages
175
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.

1592334505028.png
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:41
Joined
Aug 30, 2003
Messages
36,118
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

Member
Local time
Today, 14:41
Joined
Apr 13, 2020
Messages
175
Ok, thank you very much.
Etxe.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:41
Joined
Aug 30, 2003
Messages
36,118
No problem, post back if you get stuck.
 

Etxezarreta

Member
Local time
Today, 14:41
Joined
Apr 13, 2020
Messages
175
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

Registered User.
Local time
Today, 13:41
Joined
Oct 27, 2016
Messages
93
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

Wino Moderator
Staff member
Local time
Today, 06:41
Joined
Aug 30, 2003
Messages
36,118
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

Member
Local time
Today, 14:41
Joined
Apr 13, 2020
Messages
175
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

Wino Moderator
Staff member
Local time
Today, 06:41
Joined
Aug 30, 2003
Messages
36,118
No problem, good luck! Post back if you get stuck.
 

Users who are viewing this thread

Top Bottom