Discussion :

[Depite]

Bonjour,

Je suis confronté à un problème assez surprenant.

Développement en VBA dans une application frontale utilisant les données d'une base "dorsale" dont les tables sont attachées.
Dans le cas présent j'exécute une suite d'instructions SQL à partir des commandes VBA "DataBase.execute()". L'ensemble des commandes est encadré par une transaction déclarée au niveau du WorkSpace.

Pour résumer (je vous fournirai ultérieurement un code plus précis si nécessaire) voici le schéma d'exécution des actions sous forme d'un exemple.

Soit ma table Lignes(IdLigne numéroAuto, Intitulé string(50), Valeur long)
Cette table comporte des lignes dont la dernière à la valeur IdLigne = 50.

-1-
Je copie dans une table temporaire (TMP) les lignes IdLigne = 10,11 et 12 .
-2-
j'ouvre la transaction
-3-
Je supprime les lignes que j'ai copiées dans ma table temporaire
-4-
J'insère les lignes de la table temporaire qui ont conservé leur identifiant IdLigne initial avec une instruction de la forme :

Code :

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

1
2
 
insert into Lignes(IdLigne, Intitulé , valeur) select IdLigne, Intitulé, valeur from TMP where IdLigne is not null;

-5-
J'insère de nouvelles lignes qui ont été saisie sur la table temporaire et dont la colonne IdLigne n'est pas définie

Code :

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

1
2
 
insert into Lignes(Intitulé , valeur) select Intitulé, valeur from TMP where IdLigne is null;

Cette instruction Insert provoque une erreur de doublons.

Plus étonnant, je constate que si j'essaie de saisir directement dans la table Lignes une nouvelle ligne, le numéro Idligne prend la valeur 13, soit la prochaine valeur suivant l'insertion -4- des lignes qui avaient un numéro forcé!

Bon, bien sûr, je n'ai qu'à faire un update pour - à priori - résoudre le problème; mais quand-même il ne me semble pas normal que le numéro auto de la table soit déterminé par la valeur de la dernière ligne introduite comportant une valeur pour cette colonne…

Je précise que j'ai compacté plusieurs fois ma base avant de tirer mes conclusions.

Si vous avez une explication sur ce problème, voir mieux une solution, je suis preneur car il n'est pas impossible que j'ai utilisé une méthode similaire dans plusieurs de mes applications.

Merci.

[marot_r]

Bonjour.

Les numéros automatiques sont là pour fournir un identifiant unique À CHAQUE ajout donc la valeur que tu essayes de forcer est simplement ignorée par le système et remplacée par celle que lui calcule.
Note aussi qu'ils ne sont pas là pour te garantir une numérotation séquentielle sans trou, juste te fournir un numéro unique dans la table.

Pour ton problème, il faut que tu remplaces le champ numéro automatique par un champ entier long et que tu gères toi-même la séquence de numéro.

A+

[Depite]

Bonjour Marot_r,

Je ne me suis peut-être pas bien exprimé car ce n'est pas l'insertion des lignes comportant une valeur pour les numéros automatiques qui pose problème, les numéros sont correctement réinsérés et c'est un des avantages de la numérotation automatique d'Access qui, contrairement à SQL, permet l'insertion de valeurs dans une colonne AutoNumber sans activation d'option particulière du moment que les valeurs introduites ne figurent pas déjà dans la table.
Ce qui pose vraiment problème c'est que le prochain numéro automatique soit calculé à partir de la dernière instruction INSERT et non pas sur l'ensemble des valeurs de la table ou à partir d'un indicateur indépendant.
D'après la documentation de Microsoft, le compteur autonumber ne devrait se redéfinir que lorsqu'on effectue un compactage de la base de données. En réalité force est de constater qu'il est aussi redéfini lors d'une insertion de ligne mais en considérant que le dernier numéro inséré (qu'il soit calculé automatiquement ou imposé) sera augmenté de 1 pour constituer le prochain.

Ceci dit, il s'agit essentiellement de prose de ma part qui éclairera peut-être un autre membre confronté à la surprise de voir un risque de doublons dans le champ d'une table autonumber.

La meilleure parade - si on a une bonne raison de réintroduire des valeurs qui ont été précédemment supprimées - consiste sans doute à insérer une ligne fictive dans la table en lui attribuant la valeur maximum actuelle de la colonne +1 puis de la supprimer avant de reprendre des insertions avec calcul automatique de la valeur; cette méthode présente toutefois l'inconvénient de constituer autant de "trous" dans la numérotation.

Attention aussi à l'usage multi-utilisateurs, la valeur du dernier numéro automatique doit impérativement être acquise dans une transaction incluant l'insertion de la ligne fictive.

Bonne journée.

[tee_grandbois]

bonsoir Depite,

Ce qui pose vraiment problème c'est que le prochain numéro automatique soit calculé à partir de la dernière instruction INSERT et non pas sur l'ensemble des valeurs de la table ou à partir d'un indicateur indépendant.

ce "bug" est connu depuis la nuit des temps du moins le temps d'existence d'Access.
Mais peut-on vraiment parler de bug quand cette "anomalie" n'a toujours pas été corrigée par Microsoft ?
L'avantage est que cela permet de combler un "trou" dans une numérotation, même si cela n'a pas forcément un sens de vouloir récupérer un numéro auto perdu ...

Oui, il est effectivement possible lors d'une requête INSERT de forcer le numéro auto pour autant qu'il n'existe pas déjà.
MAIS, comme tu l'as constaté, cela aura pour conséquence de faire redémarrer le numéro auto à cette dernière valeur, d'où le problème de doublon dans l'index lors de l'insertion suivante.
Pour revenir à une situation "normale" il suffit de faire un insert avec la plus grande valeur du numéro auto +1, puis de supprimer cet enregistrement et de compacter la base. J'ai souvent utilisé cette manipulation dans le passé pour des situations bien précises.

Cependant, il est plus que recommandé de ne JAMAIS attribuer de valeur à un numéro auto lors de la création d'une requête INSERT.

[marot_r]

Merci tee_grandbois de cette très claire explication.

A+