Solved Migrate some tables, and keep the other ones locally (1 Viewer)

Etxezarreta

Member
Local time
Today, 16:49
Joined
Apr 13, 2020
Messages
175
Ok, I just learnt how to create a pass trough query.
To make sure I have understood:
- considering the fact that the string "strAllCriteria" is the result of list boxes
- considering the fact that all the other fields come from the SqlAzure tables
- considering the fact that the table finally poupulates "DSP_RDV_PREPA_t_transi_ChoixPraticiensAppels_OuiNon" is local (on top of being way too long, this is true..) and the other ones are in SqlAzure

your recomandation is:
1. to run this query as pass-trough query
Code:
            str_SQL_finale = "INSERT INTO DSP_RDV_PREPA_t_transi_ChoixPraticiensAppels_OuiNon (ID_Contact, Integrer_ListeAppels, Nom_contact, Prenom_contact, Date_dernier_rdv, Nom_structure, Fonction, Specialite, Profession, Departement, Secteur, Case_du_plan, Adresse_1, Ville, Secretariat_telephonique, Tel1, Courriel )" _
                                & " SELECT Contacts_t_ListeContacts.ID_Contact, Contacts_t_ListeContacts.Integrer_ListeAppels, Contacts_t_ListeContacts.Nom_contact, Contacts_t_ListeContacts.Prenom_contact, re_DatesDerniersRdvParContact.Date_dernier_rdv, Contacts_t_ListeContacts.Nom_structure, Contacts_t_ListeContacts.Fonction, Contacts_t_ListeContacts.FK_Specialite, Contacts_t_ListeContacts.FK_Profession, Contacts_t_ListeContacts.Departement, Contacts_t_ListeContacts.Secteur, Contacts_t_ListeContacts.Case_du_plan, Contacts_t_ListeContacts.Adresse_1, Contacts_t_ListeContacts.Ville, Contacts_t_ListeContacts.Secretariat_telephonique, Contacts_t_ListeContacts.Tel1, Contacts_t_ListeContacts.Courriel" _
                                & " FROM Contacts_t_ListeContacts LEFT JOIN re_DatesDerniersRdvParContact ON Contacts_t_ListeContacts.ID_Contact = re_DatesDerniersRdvParContact.FK_contact" _
                                & " WHERE " & strAllCriteria & " "

            Debug.Print (str_SQL_finale)
            objConnection.Execute (str_SQL_finale)

2. to run the querydefs called "re_DatesDerniersRdvParContact" also as a pass-through one (used in the first one)

Thank you for your feed-back.
Etxe
 

Etxezarreta

Member
Local time
Today, 16:49
Joined
Apr 13, 2020
Messages
175
That is the same - a boolean field.
They are generally not very useful as data storage medium.
It's frequently better to store the date of the event rather than simply Yes or No.

e.g. InvoicePaid yes or no
or InvoicePaidDate 24/12/2020

Which is more useful?
You don't need both, as no date means not paid, and the date tells you both that it was paid and when.
Ok, I learnt something, thanks.
 

Minty

AWF VIP
Local time
Today, 15:49
Joined
Jul 26, 2013
Messages
10,355
If you are populating a local table it may still be quicker to retrieve the data to populate it from a pass-through than via the linked tables as the query is run on the server and just returns a data set.

So move all your data gathering to the pass-through - run that as the source for your insert.
 

Etxezarreta

Member
Local time
Today, 16:49
Joined
Apr 13, 2020
Messages
175
Allright then, I will try to create only one string gathering all the data, I haven't managed it so far.
Thank you very much for your time.
Etxe.
 

Minty

AWF VIP
Local time
Today, 15:49
Joined
Jul 26, 2013
Messages
10,355
Don't forget you can store a query as a view on SQL server, so if you need to link a view to your pass through to simplify things you can easily do that.

SQL queries are much easier to write in SSMS than in Access once you get your head around things.
 

Etxezarreta

Member
Local time
Today, 16:49
Joined
Apr 13, 2020
Messages
175
This is very encouraging, thank you very much, and have a good day.
I will try later, and will mark this discussion as "solved" if the process is successful.
Etxe.
 
Last edited:

Users who are viewing this thread

Top Bottom