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

Etxezarreta

Member
Local time
Today, 16:17
Joined
Apr 13, 2020
Messages
118
Hi,
I will migrate an Access back end into Sql Azure, but I do need to keep a couple of those at the local level, as I use them to select rows with yes/no fields before sending the data into to the proper table. What would be the best and safest way to do so please?
Many thanks in advance.
Etxe.
 

Minty

AWF VIP
Local time
Today, 15:17
Joined
Jul 26, 2013
Messages
7,793
For any lookup tables with small infrequent updates required, create them locally and then simply update their contents on opening the database.
If you have an Admin options form to update these lookup tables values, update the Azure master version then refresh the local copy in the FE.

I actually simply delete their contents and update them every time open the database. I have a local tblTables which lists all the tables in the database, it has a flag field to identify that a local copy should be maintained. If it doesn't exist the routine creates it, if it does it deletes all the records and reloads from the Server version.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:17
Joined
Feb 19, 2002
Messages
30,597
I don't understand your explanation of the tables you want to keep local. Local tables should not contain changeable data. For example, in my apps, I have two local tables
Switchboard Items - this changes but it is related to the FE so it only changes if the FE changes
RevisionLog - this documents changes.

The tables stay in the FE because they reflect changes made to the FE rather than data shared by other users.
 

Etxezarreta

Member
Local time
Today, 16:17
Joined
Apr 13, 2020
Messages
118
Hi,
Not sur if my explanation was clear enough.
I think I need to create those tables before migrating: why? Because they must be linked to others tables and to a query. Maybe I am wrong here, you will probably tell me.
Then my thought was that I could click on the linked table and chose "transform in local table": but in my case something went wrong, impossible for Access to find the tables after this.
Thanks.
Etxe.
I don't understand your explanation of the tables you want to keep local. Local tables should not contain changeable data. For example, in my apps, I have two local tables
Switchboard Items - this changes but it is related to the FE so it only changes if the FE changes
RevisionLog - this documents changes.

The tables stay in the FE because they reflect changes made to the FE rather than data shared by other use

Here is the background: users select their data, that is sent into the intermediate table ( cleared and populated with INSERT INTO all the time) and managers choose the relevant rows by clicking on a yes/no field: this is why I need a table and not a query as a source of my form. Once clicked, the data from the intermediate table is sent to the final table, into SqlAzure.
I guess there will be problems when different users will try to use the intermediate table at the same time. This is why I want to keep it local.
 

Minty

AWF VIP
Local time
Today, 15:17
Joined
Jul 26, 2013
Messages
7,793
I think I get it, you are looking at a local temporary store, before committing the records to the back end main data?

In which case yes local FE table is the only way to handle it, otherwise you could be discarding another users input if you press the undo or cancel button.
 

Etxezarreta

Member
Local time
Today, 16:17
Joined
Apr 13, 2020
Messages
118
Hi Pat Minty and Isaac,
Thanks for your answers. I have a very basic knowledge about how Access deals with shared data:
my fear is the intermediate table that is often cleared and populated becomes a "bottle neck" table: can Access manage 5 users changing many elements of a table in the same time?

many thanks again.
Etxe
 

Etxezarreta

Member
Local time
Today, 16:17
Joined
Apr 13, 2020
Messages
118
I think I get it, you are looking at a local temporary store, before committing the records to the back end main data?

In which case yes local FE table is the only way to handle it, otherwise you could be discarding another users input if you press the undo or cancel button.
Exactly, this is what I meant to say, not that easy for me in english.
I just ran a few trials, but it is not very successful: I read that relations beetween local tables and linked ones could be a problem.
I had another idea: maybe creating a sql azure intermediate table for every user: in this case I will have to find a parameter to make sure that every user data goes to the correct table
 

Minty

AWF VIP
Local time
Today, 15:17
Joined
Jul 26, 2013
Messages
7,793
There is no intrinsic problem with links between local and remote tables, however, you wouldn't normally really need them, except at the point of possibly running the insert/update query.
There is certainly no need to normally define a relationship with a local table. They are normally only used for combo lookups or temporary holding and processing tables.

Sometimes a query based on a local and remote table can run slowly as the ODBC driver has to accommodate both sources, rather than just passing everything to the server, or dealing with everything locally.

I certainly wouldn't go down the route of tables per user. That is a black hole disaster in the making.
 

Etxezarreta

Member
Local time
Today, 16:17
Joined
Apr 13, 2020
Messages
118
Very useful answer, thanks Minty.
Here is the bottle-neck step:

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)
you can see that I refer to a re_DatesDerniersRdvParContact for the left join: this is a querydefs.
it is now at the local level, I may move it to SqlServer too: I keep it as a querydefs object because it is too complex for me to insert it in the str_SQL_finale string, here is the code:

Code:
SELECT DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.FK_contact, Max(DSP_RDV_PREPA_RDV_t_ListeTournees.Date_rdv) AS Date_dernier_rdv
FROM DSP_RDV_PREPA_RDV_t_ListeTournees INNER JOIN DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables ON DSP_RDV_PREPA_RDV_t_ListeTournees.Id_tournee = DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.FK_tournee
GROUP BY DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.FK_contact, DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.Professionnel_present, DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.Rdv_AvecSecretaire, DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.Rdv_TeleOuVision
HAVING (((DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.Professionnel_present)=Yes) OR ((DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.Rdv_AvecSecretaire)=Yes) OR ((DSP_RDV_PREPA_RDV_t_ListeAppelsPrealables.Rdv_TeleOuVision)=Yes));

Would you keep the querydefs, or inserting it in the str_SQL_finale string should be a priority?
In the case you think it is acceptable to keep it as a querydefs object, would it be better to migrate it to the BE or to keep it local?
Thanks again
Etxe.
 

Minty

AWF VIP
Local time
Today, 15:17
Joined
Jul 26, 2013
Messages
7,793
Am I correct in assuming all the data you are adding to
DSP_RDV_PREPA_t_transi_ChoixPraticiensAppels_OuiNon (that is a ridiculously long table name if you don't mind me saying)
Is in Main BE tables?

I'm not sure I understand your statement
I keep it as a querydefs object because it is too complex for me to insert it in the str_SQL_finale string, here is the code:

If everything is in the BE except the criteria then use a pass-through query to execute the insert on the server.
 

Etxezarreta

Member
Local time
Today, 16:17
Joined
Apr 13, 2020
Messages
118
Am I correct in assuming all the data you are adding to
DSP_RDV_PREPA_t_transi_ChoixPraticiensAppels_OuiNon (that is a ridiculously long table name if you don't mind me saying)
Is in Main BE tables?

I'm not sure I understand your statement


If everything is in the BE except the criteria then use a pass-through query to execute the insert on the server.
You are right, the data are in the BE. And the criteria as well. (once again, I assumed it was safer to put the table that is populated by those data at the local level because I am concerned about 5 different users doint many delete insertinto queries at the same time at the same time: I may be wrong, maybe Access can manage it)
I am sorry Minty: I dont understand what a pass trough query is..
Thanks again.
Etxe
 

Minty

AWF VIP
Local time
Today, 15:17
Joined
Jul 26, 2013
Messages
7,793
If everything is in the BE I'm not understanding what your bottleneck is.
ODBC is clever enough to simply pass the entire query through to the server unless the data is only available locally.

If your criteria are stored locally (Do they even need to be stored??) then you will make a bottleneck.
If you put them into a BE table add an employeeID and pass that in your query to limit the updates just to that employee's criteria?

It is difficult to visualise your exact situation, as I am guessing this is a complicated process.
 

Isaac

Lifelong Learner
Local time
Today, 07:17
Joined
Mar 14, 2017
Messages
3,273
ODBC is clever enough to simply pass the entire query through to the server unless the data is only available locally.
I've heard this said before but would like to learn a little more about what people actually mean by it.

In my experience, a pass through query written in the remote RDBMS's sql is usually a lot faster than a simple Access query written against a linked table to that RDBMS.

In what way, and to what effect, does the ODBC let the server process the query? Somehow it definitely falls short of a PT. imho
 

Minty

AWF VIP
Local time
Today, 15:17
Joined
Jul 26, 2013
Messages
7,793
I've heard this said before but would like to learn a little more about what people actually mean by it.

In my experience, a pass through query written in the remote RDBMS's sql is usually a lot faster than a simple Access query written against a linked table to that RDBMS.

In what way, and to what effect, does the ODBC let the server process the query? Somehow it definitely falls short of a PT. imho

Generally speaking, I would actually agree with you, server-side appears to always be quicker, but I have to be honest seeing the ODBC query strings that are generated when it can all be done remotely (e.g. no VBA functions etc.) I don't know why either, but suspect it is to do with maintaining or updating the local cache for the tables affected.
 

Isaac

Lifelong Learner
Local time
Today, 07:17
Joined
Mar 14, 2017
Messages
3,273
Thanks for the info.
That inspires me, next time I do hard core SQL server dev I'm going to make it a priority to become more familiar with tracing (etc etc)
 

Minty

AWF VIP
Local time
Today, 15:17
Joined
Jul 26, 2013
Messages
7,793
Hello Isaac, in my case this is a yes/no field
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.
 

Users who are viewing this thread

Top Bottom