Compacter et ordonner les tables de la Base

**Tortille** · 15/07/2020, 21h00

Bonsoir,

Est-ce qu'il est possible de compacter les tables pour qu'elle n'aient plus de numéros vides dans leurs clés en numéroAuto même quand elles ont des tables liées. Et en même temps les ordonner suivant un ou plusieurs champs.

Merci pour vos idées.

**loufab** · 16/07/2020, 08h26

Bonjour,

Supprimé les trous des numéroautos n'a pas de sens puisque ce type de colonne n'est pas destiné à être une donnée utilisateur.

Cependant on peut faire ce genre de manip à l'aide de requêtes :
https://docs.microsoft.com/fr-fr/off...tonumber-value

Si par ordonner tu entends avoir un tri les requêtes Select sont là pour ça.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select * from table order by colonne1, ..., colonneX [DESC];

Cordialement,

**Tortille** · 16/07/2020, 12h20

Bonjour,

Merci Loufab pour le lien, je vais essayer d'automatiser tout ça sur un clic.
Je veux compacter les numérosAuto parce que chaque fois que je crée une fiche dans le formulaire et que je le referme sans enregistrer, un numéro est créé.

Je vais rajouter ordonner, c'est à dire en même temps que je bouche les trous des numAutos, je trie la table et affecte le nouveaux numAutos à ce tri.

J'ai réfléchi à cette manière pour une table T_Parent avec NuméroAuto Id... et Champs Ch1,Ch2, Ch... liée avec T_Fille sur le champ IId... et avec les champs ChA, ChB, Ch...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
' Début de la procédure
 
Sauvegarde des relations
Suppression des relations
 
' Début de la compression
 
' TABLE PARENT T_Parent
' =========================
' Processus de trie
Ajoute le champs IdAnc à la table T_Parent
Copie le champs [Id] dans le champs [IdAnc]
Renomme la table T_Parent en T_ParentTmp
Duplique la table (structure uniquement) T_ParentTmp en T_Parent
 
Crée la requête qui insère dans T_Parent les données ordonnées de la table T_ParentTmp
INSERT INTO T_Parent (Ch1, Ch2, Ch3, Ch…) 
SELECT Ch1, Ch2, Ch3, Ch…
From T_ParentTmp 
ORDER BY T_ParentTmp.Ch2
Exécute la requête.
' Maintenant la table T_Parent est compactée et triée sur le champ Ch2 par ex.
 
 
' TABLE FILLE T_Fille
' ---------------------
Ajoute le champs [IIdAnc] à la table T_Fille
Copie le champs [IId] dans le champs [IIdAnc]
Copie [Id...] de la table T_Parent dans [IId...] de la table T_Fille
Maintenant la table T_Fille est compactée 
 
' Processus de trie
Renomme la table T_Fille en T_FilleTmp
Duplique la table (structure uniquement) T_FilleTmp en T_Fille
 
Crée la requête qui insère dans T_Fille les données ordonnées de la table T_FilleTmp
INSERT INTO T_Fille (IId, ChA, ChB, ChC, Ch…) 
SELECT IId, ChA, ChB, ChC, Ch…
From T_FilleTmp 
ORDER BY T_FilleTmp.ChC;
Exécute la requête.
' Maintenant la table T_Fille est triée sur le champ ChC par ex.
Supprime la table temporaire T_FilleTmp
Supprime [Id...Anc] de la table T_Fille
 
' Boucle sur TABLE FILLE Si plusieurs tables liées
 
' FIN TABLE PARENT T_Parent
' =============================
Supprime [IdAnc] de la table T_Parent
Supprime la table temporaire T_ParentTmp
 
' Fin de la compression.
 
Restauration des relations
 
' Fin de la procédure

Encore un peut de travail pour automatiser la création des requêtes INSET INTO, peut-être aussi rajouter une sauvegarde de la base avant de lancer cette procédure serait plus prudent.

Question, est ce que ça peut fonctionner ?
merci pour vos avis.

**loufab** · 16/07/2020, 14h12

Bonjour,

Personnellement je n'ai jamais perdu de temps avec les numeroautos. C'est une donnée que l'utilisateur ne doit pas voir, ça sert uniquement pour les relations entre les enregistrements.

La création de trous en cas d'annulation est un phénomène normal. Avec une capacité de plus de 2 milliards de chiffre positif je pense que c'est du temps perdu d'essayer de combler les trous.

Sinon oui il vaut mieux faire une sauvegarde.

Je suis curieux de savoir pourquoi tu veux "corriger' ce fonctionnement.

Cordialement,

**tee_grandbois** · 16/07/2020, 15h21

bonjour loufab et Tortille,

Envoyé par loufab

Bonjour,

Personnellement je n'ai jamais perdu de temps avec les numeroautos. C'est une donnée que l'utilisateur ne doit pas voir, ça sert uniquement pour les relations entre les enregistrements.

La création de trous en cas d'annulation est un phénomène normal. Avec une capacité de plus de 2 milliards de chiffre positif je pense que c'est du temps perdu d'essayer de combler les trous.

Sinon oui il vaut mieux faire une sauvegarde.

Je suis curieux de savoir pourquoi tu veux "corriger' ce fonctionnement.

Cordialement,

complètement d'accord avec loufab, je serait également curieux de connaitre aussi la raison et je constate que vous êtes de plus en plus nombreux à vouloir "combler les trous" (et pas celui de la sécu et du budget, dommage) laissé par ce fichu numéro automatique qui n'est qu'un numéro technique, une sorte de ticket d'arrivée (horodateur) et n'a nullement vocation à remplacer un identifiant administratif tel qu'un numéro de facture ...

**Tortille** · 16/07/2020, 20h04

Bonjour,

Merci à vous deux de vous pencher sur mon idée, je suis d'accord avec vous sur le rôle du numéro Auto, mais pour moi, il n'indique aussi le numéro de la fiche, et dans ma tête, quand je suis sur la fiche 50 et qu'il y a 100 fiches dans la table, je sais que je suis au milieu de la table, par contre si je suis sur le numéro 15478 et que j'ai toujours 100 fiches, bien malin sera celui qui me dira si je suis au début, au milieu ou à la fin de ma table.

Il y a aussi celui qui a conçu les numéros automatique n'a pas voulu se casser la nénette vu le nombre d'entrées possibles, mais s'il avait inclus le compactage à chaque fois qu'on supprime un enregistrement, il n'y aurait pas de trous et tout le monde trouverais ça normal aujourd'hui.

Et voilà le code qui me crée la chaine des champs et celle des valeurs des requêtes INSERT INTO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Option Compare Database
Option Explicit
 
Dim m_chainChp As String ' chaine des champs et celle des valeurs
'
Sub SqlChamp(TableNom As String)
'Crée la chaine des Champs et celle des Valeurs
'dans le SQL de la requête INSERT INTO
'pour la table TableNom
Dim cdb As DAO.Database
Dim oRst As DAO.Recordset
Dim Chp As DAO.Field
Dim virg As String
 
    Set cdb = CurrentDb
    Set oRst = cdb.OpenRecordset(TableNom)
    m_chainChp = vbNullString
    virg = vbNullString
 
    For Each Chp In oRst.Fields
        If Not (Chp.Attributes And dbAutoIncrField) Then
            m_chainChp = m_chainChp & virg & Chp.name
            virg = ", "
        End If
    Next Chp
 
    oRst.Close:     Set oRst = Nothing
    cdb.Close:      Set cdb = Nothing
 
End Sub

Plus qu'a essayer j'espère que ça ne va pas me faire péter les trous de nez.

**tee_grandbois** · 16/07/2020, 21h47

Par contre si je suis sur le numéro 15478 et que j'ai toujours 100 fiches, bien malin sera celui qui me dira si je suis au début, au milieu ou à la fin de ma table.

ben voyons !

l'important n'est-il pas que la fiche soit bien enregistrée et qu'elle soit identifiable ? à quoi bon savoir si c'est au milieu, à la fin ou au début ? En général l'utilisateur lambda ne se pose pas ce genre de question, vraiment.
De toute façon, ton exemple est très exagéré puisque avec 100 fiches, à moins de s'amuser à faire des Undo ou des Echap à tout bout de champ histoire de s'amuser à perdre sa saisie et donc quelques numéros, il est rare d'arriver à 15 000 numéros perdus et même 150 me parait exagéré.

**tee_grandbois** · 16/07/2020, 21h57

Il y a aussi celui qui a conçu les numéros automatique n'a pas voulu se casser la nénette vu le nombre d'entrées possibles, mais s'il avait inclus le compactage à chaque fois qu'on supprime un enregistrement, il n'y aurait pas de trous et tout le monde trouverais ça normal aujourd'hui.

dans d'autres logiciels aussi il est fort improbable qu'un champ de ce type (timestamp, autonumber ...) perdu soit récupérable en temps réel, c'est le principe même de l'identifiant unique.
Mais quelle importance franchement, pourquoi se prendre la tête pour si peu ? J'ai vraiment du mal à comprendre ...

**Tortille** · 17/07/2020, 15h09

Bonjour,
Merci pour ta réponse tee_grandbois,
Comme tu le dis : "je constate que vous êtes de plus en plus nombreux à vouloir "combler les trous", c'est qu'il y a une demande, et la procédure existe pour combler ces trous, lien que loufab m'a indiqué https://docs.microsoft.com/fr-fr/off...tonumber-value, procédure manuelle fastidieuse que je viens d'automatiser sauvegarde préalable de la base incluse en trois clics de souris : 1-ouverture du formulaire "Compacte et trie", 2- choix de la table à compacter, automatiquement les tables en relation seront traitées et 3- valide le compactage qui lance la procédure, et voilà magique, plus de trous !

Maintenant que ma base n'a plus de trous, j'essaie de trouver la raison de cette demande pour toi tee_grandbois.
Je suis un développeur du dimanche qui "développe" une base planning en même temps que je l'utilise réellement.
En gros le matin ma base fonctionne, dans la journée je développe des procédures pour l'améliorer, et là ça plante souvent grave et le soir je remets le plus possible en état de fonctionnement.

Alors les copie et recopies de données dans les tables ça y va, et par malheur si une colonne des 2 tables ne correspond pas, direct les données dans la table des erreurs avec en prime les numéros auto incrémentés, si bien que si j'oublie de compacter la base, à la deuxième tentative de copie, tous les numéros autos sont décalés du nombre d'enregistrements de la table.

Après 15 ans de bidouillage, j'en ai marre de ces manips qui elle m'ont fait perdre plus de temps que celui que j'ai mis pour faire cette procédure de compactage automatique.

C'est donc une procédure utile aux "développeurs" autodidactes qui avancent dans leur projet à mesure des besoins comme moi.

Tiens les besoins, qui aurait pu penser au Covid en décembre, il a impacté mon planning par les reports de sommes versées, les annulations, les relevés de compte à certaines dates, beaucoup de bidouillage avec des requêtes invraisemblables pour garder la cohérence des résultats, mais ça marche.

Bien cordialement

**tee_grandbois** · 17/07/2020, 23h29

bonsoir,

En gros le matin ma base fonctionne, dans la journée je développe des procédures pour l'améliorer, et là ça plante souvent grave et le soir je remets le plus possible en état de fonctionnement.

Alors les copie et recopies de données dans les tables ça y va, et par malheur si une colonne des 2 tables ne correspond pas, direct les données dans la table des erreurs avec en prime les numéros auto incrémentés, si bien que si j'oublie de compacter la base, à la deuxième tentative de copie, tous les numéros autos sont décalés du nombre d'enregistrements de la table.

Après 15 ans de bidouillage, j'en ai marre de ces manips qui elle m'ont fait perdre plus de temps que celui que j'ai mis pour faire cette procédure de compactage automatique.

pas vraiment convaincu par ton explication et ça n'explique toujours pas pourquoi les trous sont si indispensables à combler.
Si tu as autant de plantages, il faudrait d'abord savoir pourquoi.

Il est tout à fait normal de faire des sauvegardes avant de commencer une journée de travail puisqu'il ne faut jamais développer directement sur les bases de production. J'espère au moins que tu travailles avec des bases en frontale (les programmes) et en dorsale (les données) ce qui permet de limiter les prises de tête lors de pertes de données.
Car si ça plante dans la version de développement, il suffit de remplacer la dorsale de développement par la dorsale de production, moyennant le report au préalable des modifications de structure qui auraient été faites. La frontale n'a pas ce souci puisqu'elle ne contiendra que les objets "programme" a savoir les formulaires, états, requêtes et code ...

si bien que si j'oublie de compacter la base, à la deuxième tentative de copie, tous les numéros autos sont décalés du nombre d'enregistrements de la table.

il existe pourtant depuis quelques versions déjà l'option "compacter lors de la fermeture", mais bien sûr, elle ne concerne pas les bases qui sont en frontale et dorsale car lorsqu'on ferme la frontale, elle ne compactera jamais la dorsale, mais sur le net il suffit de chercher comment compacter une base distante, je crois même que le code doit exister sur ce site

**Tortille** · 20/07/2020, 12h37

Bonjour,
Frontale et dorsale, je ne pratique pas pour le moment, par contre je sauvegardes plusieurs fois par jour, avantages :
toutes mes sauvegardes fonctionnent, les données marchent avec le code, je veux dire que si j'ai ajouté une colonne dans une table ou j'ai changé le nom d'un champ, le code tiens compte de ces changements donc il fonctionne.

Avec frontale et dorsale, il faudrait sauvegarder les 2 ensembles pour garder la cohérence.

Quand mon projet sera abouti, je dis pas non, mais sera-t-il abouti un jour.

J'arrête de parler de l'arlésienne, je vous mets la base qui compacte les tables en pièce jointe.

SAUVEGARDEZ vos données avant de faire des essais.

Le compactage ne fonctionne que sur les numéros Auto, pour les autre, je n'ai pas essayé, ni fait de test dans le code pour stopper la procédure.

Il n'y a pas de tables pour tester dans la base, vous devez copier les vôtres puis renseigner la table "CompactTrie" avec le nom de vos tables, des champs Id_Pere et si table liées, Id_Fille ainsi que les champs de tri sur ces tables si vous voulez un tri, le tri est actuellement uniquement croissant.

J'ai laissé un exemple renseigné dans la table "CompactTrieExemple".

Cette base se sauvegarde automatiquement avant de lancer la procédure dans le dossier "SAV" au niveau du dossier "PROJET"
Chaque sauvegarde est elle même dans un sous-dossier de "SAV" du nom de la base plus la date et l'heure de la sauvegarde et pour indication le nom de la table parente plus celui des tables filles s'il y en a.

Un compte rendu détaillé est crée en même temps que la procédure et affiché à la fin.
J'utilise un web Browser pour l'affichage, la msgbox n'affiche pas la totalité du compte rendu.

Le code de cette base provient de plein de codes grappillés sur ce site que je remercie et d'autre que j'ai assemblé à ma façon, vous pouvez le mettre à votre guise, le simplifier, l'améliorer, le compléter, nous en faire part.

Je vous en souhaite un bon usage.

**tee_grandbois** · 20/07/2020, 15h46

bonjour,
merci pour le travail qui en intéressera certains vu la demande ...

je veux dire que si j'ai ajouté une colonne dans une table ou j'ai changé le nom d'un champ, le code tiens compte de ces changements donc il fonctionne.

Avec frontale et dorsale, il faudrait sauvegarder les 2 ensembles pour garder la cohérence.

on n'a pas accès à la modification de structure des tables de la dorsale depuis la frontale, donc la cohérence n'importe que dans la dorsale.

Compacter et ordonner les tables de la Base

Access

Discussions similaires

Partager

Partager