IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Apprenez comment migrer une base Microsoft Access en un projet ADP Microsoft SQL Server

Ce document a pour but de vous montrer comment migrer votre application Access en projet SQL Server. Dans ces paragraphes, vous apprendrez à constater les différences entre une base de données Access et une base SQLServer ce qui va vous amener à comprendre celles existant entre une application MDB et un projet ADP tous deux Access. Je vous montrerai également comment modifier votre code VBA DAO vers ADO. Pour exploiter ce tutoriel, vous devez maîtriser parfaitement VBA et plus particulièrement la manipulation des objets d'accès aux données, mais aussi, la gestion et l'administration des serveurs et des bases de données sous SQL Server.

33 commentaires Donner une note à l´article (5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Avant-propos

Ce document a pour but de vous montrer comment migrer votre application Access en projet SQL Server. Dans ces paragraphes, vous apprendrez à constater les différences entre une base de données Access et une base SQLServer ce qui va vous amener à comprendre celles existant entre une application MDB et un projet ADP tous deux Access. Je vous montrerai également comment modifier votre code VBA DAO vers ADO. Pour exploiter ce tutoriel, vous devez maîtriser parfaitement VBA et plus particulièrement la manipulation des objets d'accès aux données, mais aussi, la gestion et l'administration des serveurs et des bases de données sous SQL Server.

I-A. Remerciements

Je tiens à remercier tout particulièrement toutes celles et tous ceux qui ont participé à la relecture de ce document en y incluant leurs remarques.

I-B. Contact

Pour tout renseignement complémentaire, veuillez me contacter directement (Argyronet) par MP.

I-C. Extraits

Certains paragraphes de ce tutoriel ont été rédigés et adaptés à partir d'extraits des pages du site de Microsoft.

II. Présentation du projet

Dans ce tutoriel, je vais exposer de façon succincte un fait de réalité où j'ai été amené à migrer une grosse application Microsoft Access. Je vais essayer à travers les différentes rubriques de vous préparer à une migration réussie ; le petit plus pour vous est que ce qui va être exposé ici est basé sur une situation professionnelle.

Dans le contexte actuel, mon projet est composé de deux bases Access 2000 avec un fichier MDE qui représente l'application frontale et une base de données MDB qui est implantée sur un Serveur de fichiers. Les applications MDE sont installées par un processus d'installation supervisé par un SETUP créé avec Microsoft Office Developer 2000 1.5.

Le SETUP inclut le Runtime pour le cas où les postes utilisateur seraient dépourvus d'une licence Access idoine.
Pour plus d'informations à propos de l'empaquetage et le déploiement d'applications Access 2000, veuillez vous rendre à cette page pour consulter le tutoriel. Pour les utilisateurs d'Access 2003, c'est cette page qu'il faudra consulter.

III. Quand et pourquoi migrer ?

En ce qui me concerne et pour vous donner un exemple concret, le besoin de migration s'est fait ressentir à partir du moment où l'application, exploitée par une dizaine d'utilisateurs au départ, envoyait quelques signes de faiblesse avec un laps de temps d'attente de l'affichage équivalent à celui d'une page WEB soit entre 5 et 15 secondes, mais parfois plus, selon l'encombrement du réseau.
Dans la situation exposée, nous avons une bande passante comprise entre 1 et 2 Mo.

Quand bien même, la situation était tout à fait acceptable.

Cette application, composée d'un MDE et d'un MDB sur un serveur dédié Windows Server 2003, est un projet relativement complexe du fait qu'il génère dynamiquement un grand lot de requêtes durant la navigation à travers les différents écrans, d'où une surcharge réseau importante.

Dès l'instant où la décision a été prise de rendre la base accessible à un autre groupe d'utilisateurs implanté dans un autre bâtiment situé à environ un kilomètre à vol d'oiseau du nôtre, j'ai déplacé une copie de la base sur un serveur local de ce même bâtiment puis j'ai reconfiguré une copie de l'application MDE avec les nouvelles liaisons. J'ai pu alors effectuer des tests de performances. Celles-ci s'avérèrent déplorables bien que le serveur était un poste bien musclé.

Afin de ne pas perturber le cycle de fonctionnement, j'ai laissé le projet MDE/MDB en place pour les utilisateurs de mon étage en attendant de tenter la mise en place d'un projet ADP. Nous disposons tous d'un client SQL Server sur nos postes…

Si vous l'ignorez, un projet ADP est ni plus ni moins une base de données Access qui devient une véritable application client-serveur où les données sont stockées sur un serveur SQL Server (ou via MSDE selon le cas).

Le projet est alors pourvu d'une partie de la collection d'objets présents dans une base SQL Server à savoir des procédures stockées et des vues à la place des requêtes et des schémas. Les autres objets déjà connus restent présents…

Image non disponible


Un projet Access finalisé reste une base Access à part entière où vous devez vous familiariser avec les nouveaux objets.

IV. Quel est l'avantage de SQL Server par rapport à une base Access ?

De nombreux avantages sont convaincants pour faire le pas de la migration… Bien que cela nécessite une réétude et une réécriture d'une grande partie de code VBA présent au sein de votre futur projet Access, le jeu en vaut largement la chandelle.

IV-A. Des performances accrues

Comme tout projet SQL Server, les performances des bases de données SQL Server sont accrues puisque le trafic réseau est réduit du fait que les requêtes sont traitées et exécutées sur le serveur avant de renvoyer les résultats au client. Comme vous le savez sans doute, SQL Server est voué à prendre en charge des bases de données très volumineuses avec des tailles se chiffrant en Tera-octets.
Une base de données Access se limite quant à elle à deux Gigaoctets.

Le mode d'exécution des requêtes sur SQL Server est de tout autre envergure puisque qu'il les traite avec plusieurs threads natifs dans un processus ce qui permet de prendre en charge un maximum d'utilisateurs simultanément tout en sollicitant que peu de mémoire supplémentaire si d'autres utilisateurs sont ajoutés.

IV-B. Le mode de sauvegarde plus efficace

Dans mes projets Access, je mets systématiquement en place un module autonome de sauvegarde de la base de données provoqué par la déconnexion de l'administrateur ou d'un membre utilisateur ayant des droits d'administration et bien sûr, moi-même lorsque je l'utilise.
Par exemple, avec l'API apiSHFileOperation, je force la duplication de la base après avoir vérifié qu'elle n'est pas ouverte exclusivement.

Bien qu'un jeu de fonctions appropriées contrôle le bon déroulement des opérations, cela fonctionne très bien, mais reste assez rudimentaire dans le sens où le risque de duplication ne soit pas effectué si par exemple, le dernier utilisateur voit son poste figé par un plantage ou tout autre cas similaire.

Du côté de SQL Server, c'est tout autre chose.

Il permet de sauvegarder la base de données selon trois modes (ceux généralement rencontrés dans les logiciels de sauvegarde) à savoir :

  • Dynamique
  • Incrémentielle
  • Complète

même si celle-ci est en cours d'utilisation.
Il n'est donc pas nécessaire de mettre en place un système d'avertissement pour les utilisateurs.
De plus, la base reste disponible en permanence.

IV-C. Fragilité minimisée

En cas de panne réseau, qu'elle soit électrique ou informatique, SQL Server met en route un système de restauration automatique de la base de données dans son dernier état de fonctionnement sans intervention d'un administrateur. Ainsi, l'exploitation de la base de données peut reprendre son cours immédiatement.

Du côté d'une base de données Access, dans une situation similaire, il faut avoir recours aux outils de récupération et de réparation de la base. Fort heureusement, dans la majorité des cas, les bases de données Access n'en pâtissent pas trop lorsque ce genre d'incident survient et le module de réparation reste assez efficace même si la méthode pour l'utiliser reste manuelle.

IV-D. Licence SQL Server et licence MSDE

Si vous prenez la décision de migrer votre projet vers une solution ADP, je me permettrais de supposer que l'entreprise dans laquelle vous travaillez dispose de licences SQL Server avec tout ce qu'il faut autour pour que les utilisateurs puissent accéder au serveur que vous allez choisir…

Si vous ne disposez pas de licence SQL Server, vous avez la possibilité d'avoir recours à MSDE.

Il est gratuit ; vous le trouverez dans le pack Office 2000 ; il reste 100 % compatible SQL Server et sa licence d'exploitation peut être diffusée librement sous forme de Runtime autoinstallable.
En contrepartie de sa gratuité, MSDE est dépourvu d'outils pour la gestion de la base de données comme le propose SQL Server avec l'Analyseur de requête. Il y aurait également des échos comme quoi il ne supporterait qu'un nombre limité d'utilisateurs simultanés et que ses performances diminueraient plus rapidement qu'avec SQL Server.

IV-E. Pour résumer

Une base de données Access MDB contient des tables (avec les données si la base n'est pas scindée), des requêtes, des formulaires, des états, des pages, des macros et des modules.
Un projet de données Access ADP se limite à établir la connexion à une base de données Microsoft SQL Server ; il contient des tables (sans données), des vues, des schémas, des procédures stockées des formulaires, des états, des pages, des macros et des modules.

V. Le processus de migration de votre projet

Pour effectuer une migration correcte de votre projet, il est impératif de vérifier un certain nombre de choses de manière à ce que ce dernier s'effectue sans contrainte et sans échec.

V-A. Qu'est-ce que la migration ?

Lorsque vous avez pris la décision de migrer votre projet, les objets qui constituent votre base de données seront insérés dans le nouveau projet ADP.

Pour ce faire, le moyen le plus simple et le plus efficace consiste à utiliser l'Assistant Migration SQL Server qui exécute la plus grande partie du travail pour vous à savoir :

  • vérifier la présence d'un client SQL sur le poste où sera effectuée la migration ;
  • assurer la concordance entre le serveur et la base SQL devant recevoir les objets de votre projet ;
  • vous proposer les différents modes de migration qui sont au nombre de trois.

Utiliser cet assistant est d'autant plus intéressant du fait qu'il facilite grandement la tâche face à la conversion manuelle des tables Access en table SQL Server. Il transforme le type des champs de la base Access en type de champ correspondant pour SQL Server tout en conservant les éventuelles règles de validation (que je ne conseille pas de mettre en place même pour une base de données Access) ainsi que les valeurs par défaut.

Notez au passage que les propriétés de masque de saisie et assimilées sont inexistantes dans une table SQL Server du fait que ce soit un automatisme non normalisé.

Durant cette conversion, les index autant que les relations et les règles d'intégrité référentielle sont préservés à l'identique.

Cet Assistant se charge de migrer votre base de données Access vers une base de données SQL Server existante (ou à créer durant la phase de migration) et migre également les données. Il se charge aussi de déplacer les objets de base de données Access vers le nouveau projet ADP.
Cette migration peut s'effectuer vers des bases SQL Server à partir de la version 6.5 (quelques précautions particulières sont à prendre pour cette version).

V-B. Comment utiliser l'Assistant de migration SQL Server ?

Pour utiliser l'Assistant de migration SQL Server dans Access 2000, rendez-vous dans le menu Outils où vous sélectionnez la sous-rubrique Utilitaires de base de données et où vous cliquez sur Assistant de migration SQL Server.

Cette rubrique est détaillée juste après.

Pour Access version 97, vous devez préalablement télécharger et installer les outils indispensables à la migration, ceux-ci n'étant pas implémentés par défaut. Les outils de migration pour Access 97 se trouvent ici.

VI. Avant d'utiliser l'Assistant Migration

Avant d'utiliser l'Assistant Migration, un certain nombre de précautions sont à prendre.

  1. La première des choses est bien entendu de faire une copie de sauvegarde de votre base de données application…
    Le mieux est de copier le dossier de votre projet complet que vous nommez par exemple du même nom précédé ou suivi de « ADP » ou « SQL »…
  2. Il vous faut par ailleurs désactiver le mot de passe VBA de votre projet à migrer s'il y en a un…
  3. Il faut également que vous disposiez d'un espace disque suffisant surtout si vous disposez de la version 6.5 de SQL Server.
  4. Il vous faut ouvrir votre base de données contenant les données et donc les tables en mode exclusif afin de vérifier que ces dernières possèdent bien un index unique et les ajouter sinon…
  5. Il vous faut vous assurer que vous avez les droits suffisants pour créer une base de données SQL Server et à ce titre munissez-vous du nom du serveur cible, du nom de connexion et du mot de passe requis : l'idéal étant d'être un administrateur système.

Notez au passage qu'une imprimante locale ou réseau doit être installée sur votre poste, car à la fin du processus de migration, l'assistant génère un « snapshot » sous forme d'état qui contient le résumé de la migration en détail. Si vous ne disposez pas d'imprimante, une erreur se produit.

Ce « snapshot » est en réalité un fichier snp qui porte le nom du projet créé et que vous pouvez visualiser avec « Snapshot Viewer ».
Vous pouvez le télécharger ici si vous ne l'avez pas sur votre poste.

VI-A. Que faire si ma base de données est scindée ?

Une base de données scindée en deux (ce qui est fortement recommandé pour les applications multiutilisateurs), est composée d'un fichier application implanté sur chaque poste utilisateur (en général avec une extension MDE) où aucune donnée excepté peut-être celles de configuration (données non partagées) n'est disponible et d'un fichier de base de données ne contenant que les tables sur un serveur de fichiers dédié…

Si votre base de données est scindée, alors, il vous faut lancer l'Assistant depuis le fichier MDB qui représente l'application (celle qui contient les requêtes, les formulaires, les états et les modules) où les tables sont liées et non pas celle qui est implantée sur le serveur et encore moins le MDE.

L'assistant se chargera d'identifier les tables et vous proposera la liste intégrale des tables qu'elles soient locales (tables de configuration par exemple) ou liées.
Et si les tables ne le sont pas, eh bien le listage se fait à l'identique.

Les tables masquées ne sont pas listées dans le processus de migration donc si elles doivent être migrées, veillez à changer leur propriété Masquée à Non.

VII. Lancer l'Assistant Migration

Une fois que vous avez effectué toutes les tâches prérequises pour mener à bien la migration de votre projet, vous pouvez lancer l'Assistant Migration

Avant toute chose

Avant toute chose, je considère que vous maîtrisez SQL Server tant du côté administratif que du côté technique en ce qui concerne la gestion des serveurs et la création de bases de données SQL Server.

VII-A. Les différents modes de migration

Bien qu'il existe différents modes de migration, celui que je vais aborder dans ce tutoriel est de loin le plus complexe puisqu'il consiste à transformer votre base Access en Projet Access ce qui signifie qu'il y aura un nombre important de changements à prévoir notamment en ce qui concerne le code VBA et les requêtes définies comme complexes.

VII-B. Lancement de l'Assistant

Pour lancer l'Assistant Migration SQL Server, cliquez sur le menu Outils/Utilitaires de base de données puis choisissez la rubrique susnommée.

Image non disponible

Le processus lancé, un temps d'attente se met en route avant de vous proposer la première étape du cycle de migration…

VII-B-1. Sélection de la base de données

Une fois cela terminé, la fenêtre suivante apparaît et vous demande de sélectionner l'option appropriée, à savoir créer une nouvelle base ou utiliser une base existante.

Image non disponible
  • En choisissant la première option, vous allez devoir sélectionner la source de données afin de créer la connexion ODBC appropriée de manière à ce que vous puissiez accéder à la base de données SQL Server visée…
  • En choisissant la seconde, il vous suffira de suivre les instructions du fait que l'Assistant se chargera de générer la base de données en se reflétant sur la base de données Access.

Dans mon cas évoqué, je choisis de créer une nouvelle base de données…

Image non disponible


L'étape suivante vous invite à sélectionner le serveur dans lequel l'Assistant Migration SQL Server doit créer la base de données.
Vous pouvez indifféremment saisir le nom de ce serveur ou bien le choisir dans la liste.
Il faut bien entendu que ce dernier soit disponible et que son alias pointe correctement sur le bon serveur.

Cliquez alors sur Suivant

Les zones de Nom d'accès et Mot de passe peuvent à cet instant rester vides ou être renseignées comme il se doit.
De toute façon, vous ne pourrez continuer que lorsque les informations de connexion seront approuvées…
En cas se saisie incorrecte des paramètres de connexion, ce message apparaît…

Image non disponible

Vous appuyez alors sur OK et l'invite de saisie des informations requises s'affiche en conservant le nom du serveur que vous avez sélectionné.

Image non disponible


Entrez de nouveau votre ID de connexion et votre mot de passe

VII-B-2. Annulation de la procédure

Si vous annulez à cette étape, l'Assistant se ferme.
Le fait de le relancer entraîne le même cycle d'affichage des boîtes de dialogue, mais l'Assistant Migration SQL Server prend l'initiative de modifier le nom de votre projet base de données SQL pour éviter tout conflit du fait qu'il ne sache pas vous proposer d'écraser la base qu'il vient de créer.

Ce sera donc à vous de procéder à la suppression de la base fraîchement créée (et peut-être incomplète) depuis Entreprise Manager si vous voulez conserver le nom initial de votre projet sans suffixe numérique.

Image non disponible


Il n'est en effet pas possible de remplacer la version existante de la base de données.

VII-B-3. Sélection des tables à migrer

L'étape suivante vous invite à choisir les tables à migrer.
Cette liste comprend l'ensemble des tables (visibles) de la base.

RAPPEL : dans la mesure où vous avez des tables masquées par vous-même, ces dernières ne seront pas incluses dans la liste.

Image non disponible


- Cliquez sur le bouton Image non disponible pour migrer l'ensemble des tables listées ou sur le bouton Image non disponible pour migrer la table sélectionnée.

- Inversement, les boutons Image non disponible et Image non disponible retirent respectivement toutes les tables ou la table sélectionnée.

- Le double-clic dans les listes possède le même effet que les boutons Image non disponible et Image non disponible

Dans mon exemple, je choisis de migrer toutes les tables

Image non disponible

Cliquez alors sur Suivant

VII-B-4. Les attributs de votre base de données

À l'étape suivante, il est question des attributs de champ.

Dans cette étape, vous pouvez choisir d'inclure les attributs de votre base de données à savoir les index, les règles de validation, les valeurs par défaut et les relations entre tables.

En considérant ces attributs, notez que l'Assistant Migration SQL Server prend la liberté de renommer les champs qui ne sont pas conformes à la norme SQL (champs avec espaces notamment) et convertit les types de champ Access en types de champ SQL Server.

VII-B-5. Comment l'Assistant Migration SQL Server convertit les champs ?

Les types de champ des bases de données sont quasiment tous identiques, mais ne sont pas nommés de façon identique entre deux bases de données distinctes.

Un nombre Entier reste un nombre Entier dans n'importe quelle base de données, mais son appellation de type et la valeur de ce dernier peuvent varier selon le cas : Entier pour SmallInt et Entier Long pour Int par exemple.

Liste des types de champ Access et leur correspondance SQL Server

Types de champ ACCESS

Types de champ SQL Server

Nombre

Int / TinyInt / SmallInt

Oui/Non

Bit

Texte

Char / Nchar / Varchar

Monétaire

Money

Date/Time

Datetime / SmallDatetime

Mémo

Text / Ntext

Objet OLE

Binary / Image

Numéro Auto

Identity


Les attributs à migrer

Les attributs sont les informations que vous affectez à vos champs lorsque vous concevez une table.

Normalement, vous pouvez laisser ces cases cochées par défaut.

Image non disponible

Données à inclure

L'option Utiliser DRI (Declarative Referential Integrity) qui signifie Intégrité Référentielle Déclarative réagit un peu comme sous Access lorsque vous tentez de supprimer une table liée à une autre avec des règles d'intégrité référentielle…

Donc cochez Utiliser DRI et la case à cocher Relation entre les tables pour faire en sorte que les règles d'intégrité référentielle soient appliquées telles qu'elles le sont dans votre base de données Access actuelle.

VII-B-6. Migration des données

Dans la mesure où vous disposez d'un script pour le remplissage des données ou bien si vous souhaitez ne disposer que de la structure des tables et pas des données, cochez la case appropriée.

La liste déroulante Oui, laisser l'Assistant Migration SQL Server décider permet à l'Assistant Migration SQL Server d’ajouter des champs d'horodatage aux tables. Le champ d'horodatage est un champ utilisé par Microsoft SQL Server pour identifier un enregistrement de façon unique un peu comme le fait Microsoft Access lorsque vous cherchez à enregistrer une table fraîchement créée sans clé primaire.
Ce champ est nommé upsize_ts et se voit octroyer le type timestamp qui est un type de données qui présente des nombres binaires automatiquement générés comme un GUID

Dans mon exemple, seule la table TBLPostit s'est vue greffer de ce champ du fait que cette dernière n'avait pas de clé primaire définie (volontairement) dans mon projet Microsoft Access initial, car cela ne présentait pas d'intérêt.

Image non disponible


Il est possible de supprimer ce champ si vous n'en voyez pas l'utilité…
Dans ce cas précis évoqué ci-dessus, j'ai supprimé le champ tout simplement parce que cette table n'est exploitée qu'occasionnellement par l'application et n'aura pour ainsi dire aucun impact sur les performances de la base.

Donc, selon le cas, c'est à vous de déterminer si vous souhaitez laisser l'Assistant Migration SQL Server ajouter ou non ces champs automatiquement.
Une analyse de votre base de données et de vos tables s'impose donc en conséquence.
Par acquit de conscience, laissez-le et voyez ce que vous devez entreprendre après.

VII-B-7. Mode de migration de votre base de données

Comme vous pouvez le constater dans l'image ci-dessous, il existe trois modes de migration de votre base de données.

Image non disponible


Pas de modification de l'application

Ce choix consiste simplement à migrer vos données dans une base de données SQL Server sans modifier quoi que ce soit dans votre application Microsoft Access. Ce choix est en général effectué lorsque vous avez décidé par exemple de ne plus utiliser Access comme interface d'application, mais plutôt un autre outil de développement tel que Visual Basic ou assimilé…

Attacher des tables SQL Server à l'application existante

Ce choix permet de modifier votre application en conséquence des tables migrées qui étaient initialement liées en tant que tables d'une base de données Microsoft Access.
Les tables liées seront alors liées à la nouvelle base de données SQL Server en procédant au changement de nom aussi bien dans les requêtes que dans les formulaires ou les états de manière à ce que la base de données Microsoft Access tourne de façon identique.

Créer une nouvelle application client-serveur Access

C'est le choix que j'ai sélectionné, car mon objectif est de refondre l'application actuelle en un nouveau projet ADP qui va me générer une véritable application client-serveur. Par défaut, l'Assistant Migration SQL Server nomme le projet avec un suffixe CS. Dans ce cas présent, mon application se nommait PSDocDatabase.mdb et son nom de projet a été PSDocDatabaseCS.adp.
Ce nouveau projet est un fichier à part entière qui est stocké là où la base de données l'est également. Le processus de migration va alors se charger de migrer tous les objets qui composent ma base de données…

Ici, cochez la case qui permet d'enregistrer l'ID et le mot de passe afin d'éviter l'affichage de la boîte de dialogue d'identification lors de l'ouverture du projet.

L'opération de renommage des tables est conséquente au fait que vous avez adopté ou non une convention de nommage correcte.

Trop souvent, je constate que des développeurs nomment leurs objets n'importe comment (espaces, accents…) ce qui fait qu'ici, ils seront pénalisés par une surcharge de travail en ce qui concerne plus particulièrement le code VBA résidant dans l'application où l'Assistant Migration SQL Server n'intervient en aucun cas.

Pour plus d'informations en ce qui concerne les conventions de nommage des objets, cliquez ici.

Interruption du processus à cause du mot de passe

Si votre base de données Microsoft Access est verrouillée par un mot de passe, ce message apparaît et vous ne pouvez rien faire d'autre que d'annuler ; c'est un peu rageant qu'il ne vous avertisse qu'à cette certaine dernière étape et c'est pour cela que dès que vous relancez l'Assistant une seconde fois, vous obtenez un message comme je vous l'ai expliqué au paragraphe « Annulation de la procédure ».

Image non disponible


La dernière étape de l'Assistant Migration SQL Server s'achève ici :

Image non disponible


Vous cliquez sur Terminer et le cycle de migration commence.

Image non disponible


Pour chaque catégorie d'objets qui composent votre base de données, une fenêtre s'affiche en récapitulant les objets en cours de migration…

=> Pour les tables

Image non disponible


=> Pour les requêtes

Image non disponible


=> Pour les formulaires

Image non disponible


=> Pour les états

Image non disponible


=> Pour les modules

Image non disponible

VII-B-8. Compte rendu de migration

Le compte rendu de réunion se traduit par l'affichage d'un état issu d'un fichier « snapshot » qui porte le nom de votre projet avec une extension snp ce qui vous permet de le consulter ultérieurement…

Image non disponible

VIII. Problèmes rencontrés lors du lancement de l'Assistant

Lorsque vous lancerez l'Assistant Migration SQL Server, il est probable que vous rencontriez des problèmes de fonctionnement inexpliqués.
Le plus courant d'entre eux est le message d'erreur invoquant un dépassement de capacité.

VIII-A. Dépassement de capacité avec l'Assistant Migration SQL Server

Si au moment où vous vous apprêtez à lancer l'Assistant Migration SQL Server vous recevez un message d'erreur :

Image non disponible


C'est que votre version de Microsoft Office n'a pas été mise à jour avec le dernier Service Pack.

Image non disponible


En effet, Access 2000 et toutes les autres applications de la suite Office 2000 ont subi un certain nombre de correctifs, dont celui-ci.

Vous trouverez sur cette page, les problèmes relatifs à Access 2000 résolus dans le Service Pack 3 Microsoft Office 2000.

Pour les autres correctifs, vous pouvez consulter cette page

VIII-B. Comment installer Microsoft Office 2000 Service Pack 3 ?

Avant d'installer Microsoft Office 2000 Service Pack 3, vous devez vous assurer que vous possédez le Microsoft Office 2000 Service Pack 1a.

VIII-B-1. Installation du Service Pack 1a Microsoft Office 2000

Pour installer Microsoft Office 2000 Service Pack 1a, reportez-vous à cette page pour obtenir des informations sur ce Service Pack et sur cette page pour le télécharger…

La mise à jour Microsoft Office 2000 SR-1/SR-1a requiert les CD-ROM d'Office 2000 (explications ici).

VIII-B-2. Installation du Service Pack 3 Microsoft Office 2000

Pour installer Microsoft Office 2000 Service Pack 3, cliquez ici pour le télécharger…

Une fois que vous avez installé l'ensemble des services packs requis, relancez de nouveau l'Assistant Migration SQL Server en vous reportant au début du document.

VIII-B-3. Problème relatif au non-démarrage de l'Assistant Migration SQL Server

Ce paragraphe concerne Microsoft Access 2003
Si l'Assistant Migration SQL Server ne démarre pas, il est possible que votre logiciel Microsoft Access 2003 tourne en mode sandbox alors que Microsoft Jet 4.0 SP8 ou ultérieur n'a pas été pas installé sur votre ordinateur. Ce service pack Jet 4.0 SP8 ou ultérieur doit être installé sur votre PC pour que Microsoft Access 2003 puisse être opérationnel en mode sandbox.

Le mode sandbox est un module complémentaire à Microsoft Access 2003 pour permettre d'améliorer la sécurité de vos données. Lorsque ce module est installé, tous les expressions et codes qui ne sont pas sécurisés ne sont pas exécutés, ce qui permet de prévenir l'exécution d'éventuelles procédures considérées comme malveillantes… Un certain nombre de fonctions et propriétés sont listées et considérées comme telles.
Vous pouvez en consulter la liste ici.

En ce qui concerne le code VBA en général, si une de ces fonctions est utilisée et que le projet est signé numériquement, elles seront exécutées sans message d'erreur et ne seront pas bloquées par le mode sandbox.

Vous devez savoir que le mode sandbox ne se met pas en place une fois le Service Pack Jet 4.0 installé sur votre poste…
Vous devez activer ce mode au chargement de votre application pour qu'il soit pris en considération en tant que tel. Notez alors que certaines expressions dans votre code risquent de ne pas être fonctionnelles et le message indiquant que cette option n'est pas installée ou a été désactivée sera alors affiché.

Pour plus d'informations à propos du mode sandbox, veuillez vous rendre à cette page.

Pour télécharger le Service Pack Jet 4.0, veuillez vous rendre à celle-ci.

VIII-B-4. Problèmes rencontrés au sein du projet postmigration ou prémigration

Dans le rapport « snapshot » en fin de migration, le détail spécifiant l'ensemble des objets migrés y est lisible. Dans ces différentes descriptions, objet par objet, vous pourrez consulter si tel objet a été correctement exporté ou non, le cas échéant, l'erreur d'exportation y est spécifiée.

VIII-B-4-a. Les erreurs les plus fréquemment rencontrées dans un projet ADP
  • Si vous avez conçu un menu général dans votre application à l'aide de l'Assistant Gestionnaire de Menu Général, ce dernier ne sera pas migré dans votre projet.
    En effet, cet objet exploite une table locale pour s'initialiser à chaque chargement et les tables locales de ce type tel que Microsoft Access les conçoit ne sont pas compatibles en mode Client-Serveur. De ce fait, vous devez envisager de reconstruire ce formulaire.

  • Si vous avez exploité du code DDE (Dynamic Data Exchange), code servant à l'échange dynamique de données à travers des applications Windows, ce dernier ne fonctionnera pas, car il est incompatible avec un environnement Client-Serveur.
    Pour pouvoir recouvrer des fonctionnalités équivalentes, vous devez réécrire le code avec des appels ADO. Pour plus d'informations sur la rédaction de procédures ADO, reportez-vous au chapitre qui traite de ce sujet plus loin dans ce document.

  • Les tables que vous avez migrées qui sont censées contenir des données seront dépourvues de données si la table possède la propriété Indexée définie à Oui et si plus d'un enregistrement de cette table contient des valeurs NULL alors que la propriété Null interdit des champs concernés est définie à Oui. Pour pouvoir faire en sorte que les données de cette table soient migrées, ouvrez la table dans l'application Access (le fichier MDB) et supprimez ou corriger les enregistrements qui possèdent ces valeurs NULL.
    Ensuite, relancez l'Assistant Migration SQL Server et ne migrer que la table concernée.

  • Si vous ne pouvez pas accéder aux objets de votre nouveau projet, c'est parce que vous ne disposez certainement pas des autorisations nécessaires. Pour pouvoir accéder totalement aux objets de votre base de données, vous devez disposer d'une autorisation en lecture/création sur l'ensemble des objets de la base de données Access, l'idéal étant d'être administrateur système.

  • En ce qui concerne les éventuelles pages d'accès aux données qui seraient situées sur un site Web, vous ne pourrez y avoir accès du fait que l'Assistant Migration SQL Server ne sait pas copier ces objets. Pour pouvoir migrer ces objets, vous devez les copier en tant que fichier en local depuis un navigateur. Une fois cela fait, vous devez avant de migrer votre projet ouvrir chaque page afin d'en modifier l'emplacement physique puis relancer de nouveau l'Assistant Migration SQL Server.

VIII-B-4-b. Les erreurs les plus fréquemment rencontrées durant la phase de migration
  • La tentative de migration d'une base de données Microsoft Access MDE n'est pas possible. En effet, dans une base de données Microsoft Access compilée en MDE, les objets comme les formulaires, les états, les macros et les modules sont inaccessibles.
    De plus, le code VBA présent dans ces objets est compilé et impossible à interpréter.
    L'Assistant Migration SQL Server doit pouvoir accéder librement à ces objets pour pouvoir les migrer dans le nouveau projet ADP.

  • Avant de migrer votre base de données, vous devez vous assurer que vous disposez de suffisamment d'espace disque… Si le processus de migration a débuté et qu'au cours de celui-ci, un message d'erreur invoquant un espace disque insuffisant, vous devrez tout recommencer, car une partie du projet aura certainement été déjà migrée. Il vous faudra tout d'abord supprimer la base de données créée dans SQL Server depuis Entreprise Manager ou depuis SQL Server 2000 Desktop Engine selon le cas (car vous ne pouvez pas écraser une base de données existante depuis l'Assistant Migration SQL Server) et faire en sorte de disposer d'un espace disque important.
    En général, il faut compter le double de la taille de votre base de données actuelle au minimum pour pouvoir migrer sans trop de difficultés et davantage pour obtenir une migration plus rapide.

IX. ADP et MDB : Les différences

Je vais aborder les phases les plus importantes de votre projet migré…

IX-A. Une base de données Microsoft Access MDB

Comme vous n'êtes pas censé l'ignorer, le développement d'une application de base de données avec Microsoft Access peut être sous deux formes :

  • un seul fichier MDB contenant Données et Objet ;
  • deux fichiers distincts : Une application frontale (MDE) contenant les Objets et une base de données sur Serveur (MDB) ne contenant que les Données.

Dans le dernier cas, il n'y a pas de notion de client-serveur.
En effet, dans Microsoft Access, les actions de requêtes sont traitées sur le poste client même si la base de données est située sur un serveur…

IX-B. Un Projet Microsoft Access ADP (Access Data Project)

C'est un type de fichier né avec la version 2000 de Microsoft Access et qui est dédié à la conception de projet Client-Serveur afin de substituer le moteur de base de données Jet au moteur SQL Server.

Il implique obligatoirement la mise en place d'une application frontale (ADP ou ADE) et la mise à disposition d'une base de données (élaborée avec le projet ou non) sur MSDE ou sur SQL Server.

IX-C. Différences notoires à propos des requêtes

La génération des requêtes dans une base de données Microsoft Access et dans une base de données Microsoft SQL Server s'établit de façon quasi identique. Vous disposez pour Microsoft Access d'un générateur de requête (QBE) où vous pouvez créer vos requêtes en mode graphique ou en mode SQL au choix…
Vous disposez au sein de Microsoft SQL Server d'un outil puissant (Analyseur de requête) pour générer vos requêtes uniquement en mode texte.

Il existe au sein d'Entreprise Manager un QBE similaire à celui de Microsoft Access, mais il est limité et ne sait pas interpréter les requêtes complexes.

Quoi qu'il en soit, vous pouvez aussi créer vos requêtes avec le langage Visual Basic for Application, mais dans les deux cas, vous devrez dès lors dans votre projet ADP, rédiger les requêtes en vous référant aux conditions ci-dessous et à ADO, vu plus loin dans ce document.

___________________________________________________________________________________________

Les tris

           - Microsoft Access

            Il est possible d'enregistrer une requête avec une clause ORDER BY pour trier les données.

           - Microsoft SQL Server

            Une requête est appelée VUE et une vue ne peut pas recevoir de clause ORDER BY.
Pour trier les données, il vous faut utiliser des Procédures Stockées.

___________________________________________________________________________________________

Usage de Fonctions

           - Microsoft Access

            Une requête peut inclure des fonctions internes ou codées en Visual Basic.

           - Microsoft SQL Server

            Il n'est pas possible d'exploiter des fonctions autres que des fonctions au sein des requêtes SQL Server…

___________________________________________________________________________________________

Numéro Automatique

           - Microsoft Access

            Il est possible d'obtenir la valeur d'un NuméroAuto après une méthode AddNew opérée via DAO.
Cela peut être pratique par exemple pour ouvrir la fiche d'un enregistrement dans un formulaire en mode modification juste après l'avoir créé…

           - Microsoft SQL Server

            Une valeur Identity n'est connue qu'après avoir mis à jour l'enregistrement avec une méthode Update via ADO ce qui peut être un inconvénient par rapport à ce que j'ai évoqué ci-avant.

___________________________________________________________________________________________

Délimiteur de chaîne

           - Microsoft Access

'' apostrophes ou simple quotes (en anglais) et "" guillemets ou double quotes (en anglais)

 
Sélectionnez
SELECT * FROM MYTABLE WHERE NAME = "VALUE"

ou

 
Sélectionnez
SELECT * FROM MYTABLE WHERE NAME = 'VALUE'

           - Microsoft SQL Server

'' uniquement : apostrophes ou quotes (en anglais)

 
Sélectionnez
SELECT * FROM MYTABLEWHERE NAME = 'VALUE'

___________________________________________________________________________________________

Délimiteur de date

           - Microsoft Access

# uniquement : dièse ou sharp (en anglais)

 
Sélectionnez
SELECT * FROM MYTABLE WHERE DATE >= #MM/DD/YYYY#

           - Microsoft SQL Server

'' uniquement : apostrophe ou quote (en anglais)

 
Sélectionnez
SELECT * FROM MYTABLE WHERE DATE >= 'YYYY-MM-DD'

___________________________________________________________________________________________

Usage de quelques opérateurs et fonctions internes SQL au sein des requêtes Access et des vues SQL Server

Opération

ACCESS

SQL

Concaténation

&

+

Conversion en Integer

CInt

CAST

Sélection distincte

DISTINCTROW

DISTINCT

Formatage de valeurs

Format()

CONVERT()

SI imbriqué

IIf(Exp, True, False)

CASE…WHEN

Minuscules

LCase

LOWER

Maintenant (Date)

Now() ou Date()

GETDATE()

Tri des données

ORDER BY

Non supporté

Suppression d'espaces

Trim

LTRIM/RTRIM

Booléen (VRAI/FAUX)

True / False

1 / 0

Majuscules

UCase

UPPER

IX-D. Différences notoires entre DAO et ADO

Ainsi que cela a été défini aux notes de bas de page, il est recommandé d'utiliser DAO pour vos Bases de données Access et ADO lorsqu'il s'agit d'attaquer des tables issues d'autres plateformes comme SQL Server, Oracle, MySQL via ODBC…

ADO a été adopté par Microsoft Access depuis la version 2000 (9.0) de Microsoft Office.

IX-D-1. Pour la petite histoire

DAO est un modèle objet d'accès aux données apparu avec la version 4.0 de Visual Basic.
Conçu et développé pour travailler avec des bases Jet, ce modèle a été remplacé par un autre plus complet lors de l'arrivée de la version 6.0 de Visual Basic (version introduite dans VBA depuis Office 2000). Même si Microsoft assure encore le support de DAO (notamment dans les évolutions futures), il faut tout de même avouer que ADO simplifie la tâche du développeur d'applications Office et avant tout, son interopérabilité avec d'autres SGBD est un avantage indéniable comparé à DAO.

Il existe des avantages et des inconvénients pour l'un comme pour l'autre composant.
DAO est peut-être plus souple et plus facile d'utilisation du fait qu'il est impacté en une seule et même bibliothèque où sont incluses notamment les méthodes d'accès aux données (DML), les méthodes gérant la structure de la base de données (DDL) et une autre destinée à tout ce qui concerne réplication et compactage des fichiers de bases de données. Par ailleurs, DAO est riche en propriétés et méthodes, mais il ne sait toutefois pas manipuler les procédures stockées et n'est de toute façon il pas recommandé pour manipuler des données SQL Server.

Il exploite des données typées particulières qui ont été remplacées par d'autres dans ADO.
Enfin, les collections d'objets Tables et Requêtes sont effectuées par des objets TableDefs et QueryDefs qui n'existent pas au sein d'ADO qui lui exploite avec un objet Catalog. ADO est très riche également et plus puissant que DAO ; il s'exploite, peut-être avec un peu moins de facilité que son confrère la manipulation des données, mais ce petit inconvénient est très vite apprivoisé.
Avec ce composant, les différents traitements applicables ont été séparés dans différentes bibliothèques pour plus de commodité et surtout pour une interopérabilité multibase.

X. Accès à la structure (DDL : Data Definition Language)

Tout comme pour votre application Microsoft Access, l'accès à la structure de votre nouveau projet se traduit par la modification des objets qui constituent votre base de données que celle du code Visual Basic for Application associé. On parle alors de DDL, abréviation de Description de métadonnées en SQL.

X-A. Le code Visual Basic for Application pour DAO et pour ADO

Il existe différentes façons de procéder pour mettre à niveau le code Visual Basic que vous avez rédigé où ce dernier exploite DAO. Bien que les deux composants DAO et ADO possèdent beaucoup de points communs sur une grande partie du code, il n'en reste pas moins évident que la migration automatique de votre projet n'inclut pas la mise à jour de code Visual Basic et de ce fait, vous allez devoir examiner et réécrire un lot important de votre code.

C'est autant de difficulté que la méconnaissance du modèle est importante.

X-B. Quel code dois-je modifier ?

En globalité, vos procédures et fonctions exploitant DAO font référence aux déclarations d'objets comme Workspace, DBEngine, Database, CurrentDb.

Si vous avez la double référence DAO/ADO au sein de votre projet, vous avez dû préfixer vos déclarations avec le mot clé DAO par exemple :

 
Sélectionnez
Dim oRS As DAO.Recordset

Voici une liste de mots clés typique s

Quelques mots clés de référence DAO

BeginTrans

CommitTrans

CompactDatabase

Connection

Container

CreateDatabase

CreateWorkspace

Database

DBEngine

Errors

Field

Index

OpenDatabase

Properties

QueryDef

Recordset

Relation

TableDef

Workspaces

X-C. Comment repérer ces mots clés ?

1 re méthode

Vous disposez d'un marqueur de paragraphes très pratique pour marquer pages de code au sein des modules avec des signets :

Image non disponible

Ce marqueur est situé dans la barre d'outils Debogage. Vous utilisez alors l'outil de recherche (Ctrl+F) pour localiser ces mots clés après quoi, vous marquez la ligne.
Une fois cela fait, vous pouvez naviguer aisément dans votre code afin de repérer ces signets avec les boutons de navigation de la barre d'outils.

2e méthode

Toujours avec l'outil de recherche, vous pouvez très bien remplacer les occurrences par exemple DAO.Recordset :

 
Sélectionnez
As DAO.Recordset

par exemple par la portion de texte

 
Sélectionnez
As Code à Remplacer :  DAO.Recordset

Comme ce code va provoquer une erreur de syntaxe, en générale visible en rouge, il sera aisé de les repérer.
De plus, ceci peut être enregistré au sein du code, ce qui vous permet de continuer le lendemain où, contrairement aux signets qui, eux sont perdus à la fermeture projet.

Ensuite, vous recherchez toutes les occurrences « Code à Remplacer » pour travailler et mettre à jour votre projet…

Point important

   Je vous conseille fortement de noter en parallèle dans un classeur Excel ou dans un document Word toutes les opérations à effectuer avec le degré de progression dans l'exécution des différentes tâches…

X-D. Comment corriger mon code ?

- DAO fait appel a un objet nommé Microsoft Data Access Object x.x Library dans différentes versions de 2.5 à 3.6.

- ADO fait appel a un objet nommé Microsoft ActiveX Data Object 2.x Library dans différentes versions de 2.0 à 2.7.

Dans un premier temps, il vous faut activer la référence à ADO, mais attention, si vous laissez persister la référence à DAO dans le même temps, vous allez être confronté à des difficultés au niveau de l'IntelliSense® pour la complétion des instructions. Du fait que vous êtes censé maîtriser DAO, je vous préconise de décocher sa référence pour travailler plus confortablement.

Image non disponible


Voyez par vous-même qu'avec la double référence et selon la priorité accordée au composant, l'IntelliSence réagit plutôt bien :

Déclarations

DAO

ADODB

Image non disponible

Image non disponible


En revanche, si la priorité est affectée à ADO, l'IntelliSence prime sur les méthodes et propriétés de l'objet ADO…
NoMatch appartient à DAO

Image non disponible

X-E. Mise à jour du code pour l'ouverture de la base de données

Lorsque vous souhaitiez solliciter une base de données Microsoft Access avec Visual Basic for Application, vous aviez le choix entre attaquer la base de données elle-même ou une autre base de données…

X-E-1. DAO et Access

  • La base de données elle-même directement avec la méthode CurrentDB :
 
Sélectionnez
CurrentDb.Execute SQLUpdate, dbSeeChanges

SQLUpdate représente une chaîne SQL.

CurrentDB est tout de même plus simple à employer que sa propriété réelle qui est hiérarchisée sous la forme Application.DBEngine.Workspaces(0).Databases(0)

  • La base de données elle-même en déclarant un objet de type Database que vous initialisiez avec CurrentDB :
 
Sélectionnez
Dim oDB As Database
Dim oRS As DAO.Recordset

    Set oDB = CurrentDb()
    Set oRS = oDB.OpenRecordset(SQL, dbOpenDynaset)
    Do While Not oRS.EOF
        ………
    Loop
  • Une autre base de données en déclarant un objet de type WorkSpace couplé à un objet Database que vous initialisiez pour ouvrir une base de données externe :
 
Sélectionnez
Dim oWKS As DAO.Workspace
Dim oDB As DAO.Database
Dim oRS As DAO.Recordset

    Set oWKS = DBEngine.Workspaces(0)
    Set oDB = oWKS.OpenDatabase(strDBName, , True)
    Set oDB = oWKS.OpenDatabase(strDBName, dbDriverNoPrompt, True, _
    "ODBC;DATABASE=MyDatabase;UID=myUID;PWD=MyPassword;DSN=MyDSN")
...

X-E-2. ADO et Access

Lorsque vous souhaiterez solliciter la base de données Microsoft SQL Server avec Visual Basic for Application, vous devrez utiliser ADO et procéder en utilisant une chaîne de connexion :

 
Sélectionnez
Dim oCNX As ADODB.Connection
Dim oRS As New ADODB.Recordset
    Set oCNX = CurrentProject.Connection

oRst.Open SQL, oCNX

SQL représente une chaîne SQL valide !

La propriété CurrentProject.Connection de type ADODB.Connection est une propriété Connection de l'objet Application.CurrentProject

Au même titre que l'on utilisait CurrentDB pour définir la base de données en cours ou celle actuellement ouverte dans l'application, vous pouvez exploiter vos objets Recordset avec une variable déclarée comme il se doit et exploiter directement ses méthodes en affectant au paramètre ActiveConnection la connexion courante…

Image non disponible

Mais tout comme l'usage de la méthode CurrentDB, CurrentProject crée une nouvelle instance de l'objet à chaque appel ce qui est fort peu recommandé.

Ainsi, il est préférable d'éviter les instanciations multiples et de privilégier une instanciation globale en début de code et de solliciter les objets avec le mot clé Set sans oublier toutefois de libérer ces derniers avec Nothing.

X-E-3. Comment procéder ?

Vous pouvez privilégier une seule et même instanciation en exploitant par exemple une variable de type Connection qui sera instanciée dans une procédure unique et appelée si nécessaire :

 
Sélectionnez
Option Explicit

Public m_adocnActiveConnexion As ADODB.Connection

Public Sub ConnectSQLDatabase()
    If m_adocnActiveConnexion Is Nothing Then Set m_adocnActiveConnexion = CurrentProject.Connection
End Sub

Private Function IsValueExisting(ByVal TableName As String, ByVal FieldName As String, _
ByVal FieldValue As Long) As Boolean

Dim SQLSelect As String
Dim blnExist As Boolean 
Dim oRS As ADODB.Recordset
    Set oRS = New ADODB.Recordset
    SQLSelect = "SELECT " & FieldName & 
    " FROM " & TableName & 
    " WHERE " & FieldName & " = " & FieldValue
    If m_adocnActiveConnexion Is Nothing Then ConnectSQLDatabase
    oRS.Open SQLSelect, m_adocnActiveConnexion, adOpenDynamic, adLockOptimistic
    With oRS
       blnExist = Not .EOF
      .Close
    End With
    Set oRS = Nothing
    IsValueExisting = blnExist
End Function

Dans cet exemple, j'ai déclaré une variable publique m_adocnActiveConnexion et une procédure nommée ConnectSQLDatabase qui initialise la connexion. Dans ma fonction IsValueExisting, j'appelle ConnectSQLDatabase si toutefois m_adocnActiveConnexion est affectée à Nothing ce qui rejoint à dire qu'il n'y a plus de connexion courante…

Cela reste valable tant que vous souhaitez travailler avec la base de données du projet lui-même…
Si vous souhaitez travailler avec une autre base de données, il vous faudra coder l'ouverture de la connexion avec le fournisseur idoine que ce soit une base de données Microsoft Access ou non… Je vous préconise par ailleurs de créer une variable différente et conventionnellement nommée pour chaque connexion afin de vous y retrouver vous-même.

Si vous utilisez une option qui consiste aussi à solliciter la propriété IsConnected de la façon suivante :

 
Sélectionnez
If not CurrentProject.IsConnected Then ConnectSQLDatabase

vous provoquerez une nouvelle instanciation de l'objet CurrentProject.

X-F. Mise à jour du code pour solliciter la structure de la base de données

ADO et DAO ne sont pas identiques en ce qui concerne l'accès à la structure de la base de données.

En effet :

  • DAO ouvre cet accès directement. Vous pouvez consulter l'ensemble des classes couvertes par cet objet en appuyant sur la touche F2 depuis VBE qui vous donnera accès à l'Explorateur d'Objets de Visual Basic ;

  • ADO ne donne aucun accès à une quelconque collection et encore moins à celle de la structure du fichier. Ainsi, il ne vous sera possible d'intervenir sur la structure de la base de données qu'à travers la méthode Exécute de la connexion ou bien exploiter l'objet ADOX fourni par la bibliothèque d'extensions du modèle ADO, à savoir, l'objet Catalog.

X-F-1. Qu'est-ce que l'objet Catalog ?

L'objet Catalog est dédié à vous fournir toutes les méthodes nécessaires à la modification de la structure de la base de données à savoir, les tables, les champs, les index, les requêtes et les relations.
Il doit être instancié et affecté à une connexion avant d'être manipulé.

Exemple d'usage de l'objet Catalog
Sélectionnez
Private Function IsTable(ByVal TableName As String) As Boolean
Dim oTBL As ADOX.Table
Dim oCAT As New ADOX.Catalog
  
  IsTable = False
  
  oCAT.ActiveConnection = CurrentProject.Connection
  For Each oTBL In oCAT.Tables
    If oTBL.Name = TableName Then
      IsTable = True
      Exit For
    End If
  Next
End Function

Avec ADO, les objets possèdent une propriété ParentCatalog ; ainsi, vous pouvez à tout moment connaître à quel Catalog cet objet appartient. Il est alors recommandé d'affecter cette propriété à chaque fois que vous créez un nouvel objet. En procédant ainsi, vous aurez la possibilité d'accéder aux propriétés dédiées de l'objet (Collection Properties).

Contrairement à DAO qui vous offrait la possibilité de gérer vos propres propriétés (Property) que vous pouviez ajouter/supprimer/modifier dans la collection des propriétés (Properties), ADO vous donne accès aux propriétés en lecture et écriture, mais ne vous autorise pas à ajouter de nouvelles propriétés à la collection Properties pour un objet donné…
D'ailleurs, vous pourrez constater que la collection Properties ne possède pas de méthode Append.

X-F-2. Mise à jour du code pour la création de tables et de champs

La création de tables avec ADO est quasi similaire ce qui vous permet de vous adapter facilement à la façon de procéder.

Pour certains exemples ADO, j'exploite une fonction GetConnectionString() qui me renvoie la chaîne de connexion sous cette forme :

 
Sélectionnez
"PROVIDER=SQLOLEDB;DATA SOURCE=<?>;USER ID=<?>;PASSWORD=<?>;INITIAL CATALOG=<?>"

où le ? fait office de valeur correspondant à vos paramètres de connexion.

Avec DAO, vous sollicitiez au moins trois objets pour procéder à savoir, un objet Database, un objet TableDefs et un objet Field et éventuellement un quatrième, Index pour créer vos index.

 
Sélectionnez
Private Sub CreateTableProducts()
Dim oDB As DAO.Database
Dim oTBL As DAO.TableDef
Dim oFLD As DAO.Field
Dim oIDX As DAO.Index

  Set oDB = CurrentDb
  ''' Création de la table
  Set oTBL = oDB.CreateTableDef("TBLProducts")
  ''' Création des champs
  With oTBL
      Set oFLD = .CreateField("IDProduct", dbLong)
      oFLD.Attributes = dbAutoIncrField
      .Fields.Append oFLD
      .Fields.Append .CreateField("ProductName", dbText, 128)
      .Fields.Append .CreateField("IDfkCategory", dbLong)
      .Fields.Append .CreateField("IDfkSupplier", dbLong)
      .Fields.Append .CreateField("Price", dbDouble)
      .Fields.Append .CreateField("QtyAvailable", dbLong)
      ''' Création de la clé primaire et de l'index
      Set oIDX = .CreateIndex("PKIDProduct")
      With oIDX
        .Fields.Append .CreateField("IDProduct")
        .Primary = True
      End With
      ''' Ajout de l'index à la table
      oTBL.Indexes.Append oIDX
  End With
  ''' Ajout de la table à la base de données
  oDB.TableDefs.Append oTBL
  ''' Libération des objets
  Set oIDX = Nothing
  Set oDB = Nothing
  Set oTBL = Nothing

End Sub

Dans ce code, je crée une table Produits avec un champ NuméroAuto. La méthode employée est relativement simple à comprendre. Comme vous pouvez le remarquer, la création de la clé primaire et des index n'est pas possible avec la méthode CreateField. Cette opération se fait avec un objet Index une fois que les champs ont été créés. La propriété Primary de l'objet Index permet de définir la clé primaire.

Pour obtenir l'équivalent avec ADO, ce n'est pas aussi simple, car ADO étant orienté multiplateforme, on est obligé d'avoir recours à des propriétés spécifiques pour chaque cas…

Il n'existe pas par exemple de propriété NuméroAuto dans la méthode Append et même s'il y est nécessaire de passer par un objet Column à travers la bibliothèque d'extensions du modèle ADO, à savoir ADOX pour créer des champs, la propriété en question n'y est pas plus disponible. Pour ce faire et afin de bénéficier de l'accès à la collection de l'ensemble des propriétés de cet objet, vous devrez initialiser la propriété ParentCatalog auparavant…

Pour créer une clé primaire auto-incrémentée avec ADO, on utilise un objet Key dédié. L'usage d'un tel objet ouvre des perspectives plus grandes qu'avec DAO, les clés de la table pouvant être définies comme clé primaire ou clé étrangère selon le cas.

Il sera donc plus souple et plus facile de créer le champ en question en moins de lignes de code que pour DAO avec la méthode Append.

 
Sélectionnez
Sub CreateTableProducts()
Dim strTableName As String

Dim oCNX As ADODB.Connection
Dim oCAT As ADOX.Catalog
Dim oTBL As ADOX.Table
Dim oCLN As ADOX.Column

   strTableName = "TBLProducts"
  ''' Ouverture de la connexion
   Set oCNX = CreateObject("ADODB.Connection")
   With oCNX
      .ConnectionString = GetConnectionString()
      .Open
   End With

  ''' Initilalisation du catalogue
  Set oCAT = New ADOX.Catalog
  Set oCAT.ActiveConnection = oCNX
  ''' Création de la table
  Set oTBL = New ADOX.Table
  With oTBL
     .Name = strTableName
     ''' Création des champs
     .Columns.Append "IDProduct", adInteger
     .Columns.Append "ProductName", adVarWChar, 128
     .Columns.Append "IDfkCategory", adInteger
     .Columns.Append "IDfkSupplier", adInteger
     .Columns.Append "Price", adDouble
     .Columns.Append "QtyAvailable", adInteger
      Set oTBL.ParentCatalog = oCAT
      ''' Création du champ IDProduct auto-incrémenté
     .Columns("IDProduct").Properties("AutoIncrement").Value = True
      ''' Création de la clé primaire
     .Keys.Append "PKIDProduct", adKeyPrimary, "IDProduct"
     
     With oCAT
      ''' Ajout de la table à la base de données
      .Tables.Append oTBL
     End With
  End With
  ''' Fermeture de la connexion
  If oCNX.State <> adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCNX = Nothing
  Set oCAT = Nothing
  Set oCLN = Nothing
  Set oTBL = Nothing
End Sub

Vous pouvez remarquer que j'ai intentionnellement préfixé les champs IDCategory et IDSupplier respectivement IDfkCategory et IDfkSupplier ; vous verrez pourquoi un peu plus loin.


Sous ADO, la méthode Append offre cinq paramètres

Image non disponible
  1. Nom de la clé ;
  2. Type de clé (par défaut, adKeyPrimary) ;
  3. Nom du champ ;
  4. Le nom de la table en relation ;
  5. Le nom de la colonne en relation.

X-F-3. Affectation de la clé primaire à plusieurs champs

Il est possible d'affecter la clé primaire à plusieurs champs, mais la collection Keys n'autorise la spécification de plusieurs champs simultanés pour la définir.

Pour procéder à l'affectation d'une clé primaire sur un lot donné de champs, vous devez spécifier la propriété Type de l'objet Key, par exemple :

 
Sélectionnez
    '[...]
    ''' Affectation de la clé primaire sur plusieurs champs
    Set oKEY = New ADOX.Key
    With oKEY
        .Name = "PKIDCatIDSup"
        .Type = adKeyPrimary
        .Columns.Append "IDfkCategory"
        .Columns.Append "IDfkSupplier"
    End With
    '[...]

X-F-4. Affectation des index

La procédure pour la création des Index rejoint de près celle de la clé primaire et du côté des noms d'objets, il n'y a pas de différence entre ADO et DAO, il porte toujours le nom Index et appartient toujours à la collection Indexes.

Remarquez que la syntaxe entre les deux méthodes est similaire :

Création d'un index avec DAO
Sélectionnez
    ''' Création de l'index sur le champ ProductName avec doublon
    Set oIDX = .CreateIndex("IDXProductName")
    With oIDX    
    .Fields.Append .CreateField("ProductName")    
    .Unique = False
    End With
    ''' Ajout de l'index à la table
    .Indexes.Append oIDX
Création d'un index avec ADO
Sélectionnez
    ''' Création de l'index sur le champ ProductName avec doublon
    Set oIDX = New ADOX.Index
    With oIDX    
    .Name = "IDXProductName"    
    .Columns.Append "ProductName"    
    .Unique = FalseEnd With
    ''' Ajout de l'index à la table
    .Indexes.Append oIDX

Au même titre que vous affectez la clé primaire à un lot de champs, vous procédez de façon identique pour affecter les index à plusieurs champs.

X-G. Code pour les constantes de types de champ

Dans cette rubrique, les types de champ de table y sont spécifiés.
Ces types de champ sont valorisés par des constantes dans le langage Visual Basic for Application et vous devrez changer votre code en conséquence de l'emploi des constantes DAO au profit des constantes ADO.

Type de champ

Constantes DAO

Constantes ADO

 

Nom

Valeur

Nom

Valeur

Texte

DbText

10

adVarWChar

202

Mémo

DbMemo

12

adLongVarWChar

203

Date

DbDate

8

adDate

7

Entier

DbInteger

3

adSmallInt

2

Entier Long

DbLong

4

adInteger

3

Réel Simple

DbSingle

6

adSingle

4

Réel Double

DbDouble

7

adDouble

5

Monétaire

DbCurrency

5

adCurrency

6

Booléen (Oui/Non)

DbBoolean

1

adBoolean

11


Comme vous pouvez le constater, les valeurs des constantes pour DAO et ADO sont totalement différentes. Il est donc judicieux et primordial de vérifier votre code à ce niveau, car beaucoup de développeurs emploient les valeurs plutôt que les constantes elles-mêmes ce qui ne facilite pas du tout la tâche, en matière de révision du code.

X-H. Les relations entre tables

Avec ADO, il n'existe plus de collection Relations appartenant à l'objet Database comme pour DAO. Pour pouvoir mettre des tables en relation avec d'autres, on parle de clé primaire et de clé étrangère toutes deux affectées comme je vous l'ai montré ci-avant, avec un objet Key.
Vous avez peut-être remarqué plus haut que la méthode Append servant à la création d'un champ (Column) possède cinq paramètres.

Pour rappel, les voici :

  1. Nom de la clé ;
  2. Type de clé ;
  3. Nom du champ ;
  4. Le nom de la table en relation ;
  5. Le nom de la colonne en relation.

Pour la table dont je vous ai écrit le code juste avant, je vous propose d'imaginer que les champs IDfkCategory et IDfkSupplier soient mis en relation avec les tables correspondantes, soit respectivement TBLCategories (Catégories de produits) et TBLSuppliers (Fournisseurs des produits) et respectivement avec les champs du même nom (sans préfixe) dans ces tables.

Le préfixe fk est recommandé dans le nommage des champs la table en relation dans le sens où il désigne que le champ est une clé étrangère d'une table en relation fk signifie ForeignKey : clé étrangère.

Il est évident que la notion de clé étrangère se nomme conventionnellement pour vos champs si vous avez l'esprit rigoureux en matière de convention de nommage que la base de données soit développée sur Microsoft Access ou autres.

Je ne sais pas si vous l'utilisiez dans votre application Access, mais la mise à jour ou l'effacement d'enregistrements en cascade défini dans les relations existe aussi avec ADO, mais cela se définit toujours avec l'enrichissement des propriétés du fameux objet Key.

Cela se traduit par ce code :

 
Sélectionnez
      Set oKEY = New ADOX.key
      With oKEY
          .Columns.Append "IDfkCategory"
          .Name = "FKProductCategoryProduct"
          .Type = adKeyForeign
          .RelatedTable = "TBLProductCategories"
          .Columns("IDfkCategory").RelatedColumn = "IDCategory"
          .DeleteRule = adRICascade
          .UpdateRule = adRICascade
      End With

Il s'avère que l'usage de ce code est susceptible de renvoyer une erreur automation - 2147217792(80040e80) traduite par ce message :

Image non disponible


Cette erreur est caractéristique d'une fonctionnalité non supportée par le fournisseur.

Toutes erreurs du même acabit levées dans vos procédures sont pour la plupart non documentées. Vous verrez par vous-même que les pages du site de Microsoft qui relatent du sujet, spécifient que ce phénomène est inhérent à la conception.

Pour contourner le problème et créer vos clés étrangères par code, il faut alors procéder ainsi (code complet de création de la table) :

 
Sélectionnez
Sub CreateTableProducts()
Dim strTableName As String

Dim oCNX As ADODB.Connection
Dim oCAT As ADOX.Catalog
Dim oTBL As ADOX.Table
Dim oCLN As ADOX.Column

   strTableName = "TBLProducts"
  ''' Ouverture de la connexion
   Set oCNX = CreateObject("ADODB.Connection")
   With oCNX
      .ConnectionString = GetConnectionString()
      .Open
   End With

  ''' Initilalisation du catalogue
  Set oCAT = New ADOX.Catalog
  Set oCAT.ActiveConnection = oCNX
  ''' Création de la table
  Set oTBL = New ADOX.Table
  With oTBL
     .Name = strTableName
     ''' Création des champs
     .Columns.Append "IDProduct", adInteger
     .Columns.Append "ProductName", adVarWChar, 128
     .Columns.Append "IDfkCategory", adInteger
     .Columns.Append "IDfkSupplier", adInteger
     .Columns.Append "Price", adDouble
     .Columns.Append "QtyAvailable", adInteger
      Set oTBL.ParentCatalog = oCAT
      ''' Création du champ IDProduct auto-incrémenté
     .Columns("IDProduct").Properties("AutoIncrement").Value = True
      ''' Création de la clé primaire
     .Keys.Append "PKIDProduct", adKeyPrimary, "IDProduct"
     
     With oCAT
      ''' Ajout de la table à la base de données
      .Tables.Append oTBL
      ''' Ajout de la contrainte à la table
      .ActiveConnection.Execute "ALTER TABLE " & strTableName & _
                                " ADD CONSTRAINT FKProductCategoryProduct" & _
                                " FOREIGN KEY(IDfkCategory) REFERENCES TBLProductCategories(IDCategory)"
     End With
  End With
  ''' Fermeture de la connexion
  If oCNX.State <> adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCNX = Nothing
  Set oCAT = Nothing
  Set oCLN = Nothing
  Set oTBL = Nothing
End Sub

Ce problème étant intrinsèque à l'objet, la solution de contournement par le SQL ALTER TABLE, est certes moins souple, mais radicale.

X-I. Mise à jour du code pour la modification ou la suppression de tables

Comme vous avez pu le constater, le principe de manipulation des tables en DAO et ADO reste relativement à portée de main. Pour cette section qui traite de la modification ou la création de tables, c'est du pareil au même.

X-I-1. Suppression de tables

Exemples de procédure de suppression de tables dépourvues de relations

Ces deux procédures sont opérationnelles si vos tables sont dépourvues de relations ou de contrainte d'intégrité référentielle.

Suppression de tables avec DAO
Sélectionnez
Private Sub DeleteThisTable(ByVal TableName As String)
Dim oDB As DAO.Database  
    Set oDB = CurrentDb  
    oDB.TableDefs.Delete TableName  
    oDB.Close  
    Set oDB = Nothing
End Sub
Suppression de tables avec ADO
Sélectionnez
Private Sub DeleteThisTable(ByVal TableName As String)
Dim oCAT As ADOX.Catalog  
    Set oCAT = New ADOX.Catalog  
    With oCAT    
    .ActiveConnection = GetConnectionString()    
    .Tables.Delete TableName  
    End With  
    Set oCAT = Nothing
End Sub

Exemples de procédure de suppression de relations

Pour pouvoir supprimer des tables pourvues de relations, il faut d'abord vérifier l'existence potentielle de ces relations, les supprimer puis supprimer la table. Simplement, cette notion de relation existe avec DAO, mais pas avec ADO.
Pour procéder de façon équivalente, on supprimera les clés externes puis la table.

 
Sélectionnez
Private Function DeleteThisTable(ByVal TableName As String) As Boolean
Dim oCAT As ADOX.Catalog
Dim oTBL As ADOX.Table
Dim oKEY As ADOX.key
  
  On Error GoTo L_Catch
  Set oCAT = New ADOX.Catalog
  With oCAT
    .ActiveConnection = GetConnectionString()
      For Each oTBL In oCAT.Tables
          ''' Parcours de la collection de tables
          With oTBL
              If .Name <> TableName Then
                  ''' Parcours de la collection de clés étrangères
                  For Each oKEY In .Keys
                      With oKEY
                          ''' Vérification du type de clé
                          If .Type = adKeyForeign Then
                              ''' Suppression de la clé
                              If .RelatedTable = TableName Then oTBL.Keys.Delete .Name
                          End If
                      End With
                  Next oKEY
              End If
          End With
      Next oTBL
    .Tables.Delete TableName
  End With
  DeleteThisTable = True

L_EndTry:
  ''' Fermeture de la connexion
  If oCNX.State <> adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCAT = Nothing
  Set oKEY = Nothing
  Set oTBL = Nothing
  Exit Function
  GoTo L_Finally
L_Catch:
  DeleteThisTable = False
  Resume L_EndTry
L_Finally:
End Function

X-I-2. Modification de tables

Pour modifier des tables avec ADO, cela rejoint de près la même méthode d'écriture du code qu'avec la suppression de celles-ci.

Il s'avère qu'en fonction des cas, ce que j'ai évoqué plus haut concernant les fonctionnalités non supportées par le fournisseur, vous serez tenu de passer par une méthode Execute plutôt que par un objet Key.

Modification d'une table
Sélectionnez
Sub ChangeFieldDataType(ByVal TableName As String, ByVal FieldName As String, ByVal DataType As String)
Dim oCNX As ADODB.Connection
Dim strSQLAlter As String

   Set oCNX = CreateObject("ADODB.Connection")
  ''' Ouverture de la connexion
   With oCNX
      .ConnectionString = GetConnectionString()
      .Open
   End With
  strSQLAlter = "ALTER TABLE [" & TableName & "] ALTER COLUMN " & FieldName & " " & DataType
  oCNX.Execute strSQLAlter
  ''' Fermeture de la connexion
  If oCNX.State <> adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCNX = Nothing
    
End Sub

X-I-3. Mise à jour du code pour les requêtes

Dans un projet ADP, il n'y a plus vraiment la notion de requêtes en tant que telle même si l'appellation persiste… Elles sont remplacées par des Vues (Views) et de Procédures Stockées (Stored Procedures).

Ces deux dénominations couvrent deux catégories d'objets bien distincts qui appartiennent à deux collections tout aussi distinctes :

  • les Vues sont l'équivalent des requêtes sélection, mais sans clause ORDER BY et ne doivent pas comporter de fonctions comme on pouvait en mettre dans Access (IIF(), Nz(), Format() et toutes autres fonctions).
    Les vues appartiennent à la collection Views d'un objet Catalog ;
  • les Procédures Stockées sont l'équivalent des requêtes paramétrées et appartiennent à la collection Procedures.

La création d'une requête de façon dynamique, quelle qu'elle soit, est un peu plus complexe qu'avec DAO et nécessite l'usage d'un objet complémentaire nommé Command.

Avec DAO, on utilisait un objet QueryDef de la collection QueryDefs et on injectait à travers une chaîne SQL la création, la mise à jour, voire la suppression de la requête avec ce même objet.

Dans Microsoft Access 2000, les vues nouvellement créées en utilisant ADOX ne sont pas visibles… Malheureusement, ce problème est sans solution pour cette version si ce n'est d'appuyer sur F5 pour rafraîchir.

X-I-4. Création d'une vue

Une vue est assimilée à une table qui serait virtuelle où les données ne sont pas stockées et que l'on peut interroger à tout moment. Ces données peuvent provenir d'autres vues ou de tables et leur but se limite à une exploitation visuelle. On utilisera des vues pour par exemple, alimenter un ComboBox ou un ListBox dans un formulaire où les données restent relativement statiques.

Elle s'exploite uniquement avec une clause SELECT

 
Sélectionnez
Private Sub CreateView(ByVal SqlString As String, ByVal ViewName As String)
Dim oCMD As ADODB.Command
Dim oCNX As ADODB.Connection

  ''' Ouverture de la connexion
   Set oCNX = CreateObject("ADODB.Connection")
   With oCNX
      .ConnectionString = GetConnectionString()
      .Open
   End With
  ''' Instancie l'objet Command
  Set oCMD = New ADODB.Command
  With oCMD
    .ActiveConnection = oCNX
    .CommandText = SqlString
    .Execute
  End With
  ''' Fermeture de la connexion
  If oCNX.State &lt;&gt; adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCMD = Nothing
  Set oCNX = Nothing
End Sub

Sub Try_CreateView()
  CreateView "CREATE VIEW dbo. vwAllProducts AS SELECT IDProduct, ProductName FROM dbo.TBLProducts"
End Sub

X-I-5. Création d'une procédure stockée

Une procédure stockée est une requête paramétrée ou une requête action qui peut être assimilée à un miniprogramme d'exécution sous forme de langage SQL et sert notamment à effectuer des traitements sur le serveur et non sur le poste client comme on le fait avec Microsoft Access. Une procédure stockée est physiquement enregistrée dans la base de données et peut être exécutée à tout moment par le serveur.

Dans Microsoft Access, on crée des requêtes qui effectuent aussi bien des UPDATE que des INSERT ou des DELETE et qui sont exécutées sur le poste client avec tous les inconvénients que cela impose.

Pour obtenir de telles requêtes dans votre projet ADP, il vous faut les transformer en procédure stockée. Vous obtiendrez alors des exécutions plus rapides et des affichages tout à fait spectaculaires par rapport à une structure MDE/MDB scindée du fait que la charge du réseau est minimisée puisque ces requêtes sont exécutées sur le serveur.

Dans Entreprise Manager, pour créer une procédure stockée, vous écrivez quelque chose comme :

 
Sélectionnez
CREATE PROCEDURE [dbo].[sp_ListOfAvailableTea]
  @Category int,
  @Qty int
 AS

SELECT IDProduct, ProductName
FROM TBLProducts
WHERE IDfkCategory = @Category AND QtyAvailable > @Qty
GO

Ici, on souhaite à travers cette procédure stockée, obtenir la liste des produits répondant à une certaine catégorie pour une disponibilité donnée.
Par le fait, cela pourrait se résumer à une procédure toute simple :

 
Sélectionnez
Private Sub CreateStoredProcedure(ByVal SPName As String)
  CurrentProject.Connection.Execute SPName
End Sub

Ou avec la chaîne complète :

 
Sélectionnez
Private Sub ListOfAvailableTea()
Dim SQLSp As String
  SQLSp = "CREATE PROCEDURE [dbo].[sp_ListOfAvailableTea] @Category int, @Qty int AS"
  SQLSp = SQLSp & vbCrLf & "SELECT ProductName"
  SQLSp = SQLSp & vbCrLf & "FROM TBLProducts"
  SQLSp = SQLSp & vbCrLf & "WHERE IDfkCategory = @Category AND QtyAvailable > @Qty"
  CurrentProject.Connection.Execute SQLSp
End Sub

Bien entendu, les procédures stockées peuvent être autrement plus complexes que ce petit exemple tout simple.

X-I-6. Modification d'une procédure stockée

Il existe plusieurs façons de modifier une procédure stockée, en voici deux :

  • la modification des paramètres en valeur puis l'exécution de celle-ci. On peut alors récupérer le résultat dans un Recordset
Modification des paramètres
Sélectionnez
Sub RefreshListOfProducts(ByVal Qty As Integer, ByVal Category As Integer)
Dim oCMD As ADODB.Command
Dim oParam1 As ADODB.Parameter
Dim oParam2 As ADODB.Parameter
Dim SQLExec As String

    Set oCMD = New ADODB.Command
    With oCMD
      .ActiveConnection = CurrentProject.Connection
      .CommandType = adCmdStoredProc
      .CommandText = "sp_ListOfAvailableTea"
       Set oParam1 = .CreateParameter("@Category", adInteger, adParamInput)
       Set oParam2 = .CreateParameter("@Qty", adInteger, adParamInput)
      .Parameters.Append oParam1
       oParam1.Value = Category
      .Parameters.Append oParam2
       oParam2.Value = Qty
    End With
     
    oCMD.Execute
    
    Set oCMD = Nothing
    Set oParam1 = Nothing
    Set oParam2 = Nothing

End Sub
  • la modification du contenu en renouvelant ou en complétant le code SQL
Modification du code
Sélectionnez
Sub ChangeSPCode(ByVal SQLCode As String)
Dim oCNX As ADODB.Connection
Dim oCAT As ADOX.Catalog

  Set oCNX = CreateObject("ADODB.Connection")
  Set oCAT = CreateObject("ADOX.Catalog")
  ''' Ouverture de la connexion
  With oCNX
     .ConnectionString = GetConnectionString()
     .Open
  End With
  With oCAT
    .ActiveConnection = oCNX
    .ActiveConnection.Execute SQLCode
  End With
  ''' Fermeture de la connexion
  If oCNX.State &lt;&gt; adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCNX = Nothing
  Set oCAT = Nothing
End Sub

Sub TestChangeSPCode()
Dim SQLProc As String
  SQLProc = "ALTER PROCEDURE [dbo].[sp_ListOfAvailableTea] @Category int, @Qty int AS"
  SQLProc = SQLProc & vbCrLf & "SELECT IDProduct, ProductName, QtyAvailable"
  SQLProc = SQLProc & vbCrLf & "FROM TBLProducts"
  SQLProc = SQLProc & vbCrLf & "WHERE IDfkCategory = @Category AND QtyAvailable > @Qty"
  
  ChangeSPCode SQLProc
End Sub

Attention

Pour la création comme pour la modification des procédures stockées, vous êtes tenu de définir la taille des paramètres passés en Texte pour éviter que ces derniers soient considérés comme des champs Memo.

X-I-7. Problème d'utilisation de ADOX et du fournisseur

Comme je l'ai évoqué plus haut, très souvent et selon le système sur lequel est exécuté le code des différents exemples que je vous ai montrés ici, un message fort peu exploitable en tant que tel fait son apparition lorsque vous manipulez des objets ADOX

Image non disponible

Pour obtenir ce message, exécutez ce code :

 
Sélectionnez
Private Sub ChangeSPCode(ByVal SPName As String)
Dim oCMD As ADODB.Command
Dim oCNX As ADODB.Connection
Dim oCAT As ADOX.Catalog
Dim oPROC As ADOX.Procedure

  ''' Ouverture de la connexion
  Set oCNX = CreateObject("ADODB.Connection")
  With oCNX
    .ConnectionString = CurrentProject.Connection
    .Open
  End With
  
  Set oCAT = CreateObject("ADOX.Catalog")
  With oCAT
    .ActiveConnection = oCNX
     Set oPROC = .Procedures(SPName)
  End With
  ''' Construit le code à modifier et met à jour la procédure
  Set oCMD = oPROC.Command
  oCMD.CommandText = "PARAMETERS PRD TEXT(128);" & _
                    "SELECT * FROM TBLProducts WHERE ProductName=PRD"
  Set oPROC.Command = oCMD
  
  ''' Fermeture de la connexion
  If oCNX.State &lt;&gt; adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCNX = Nothing
  Set oCAT = Nothing
  Set oPROC = Nothing
  Set oCMD = Nothing
End Sub

Ici on cherche à modifier le code de la même procédure stockée en passant par un objet Command et l'erreur est levée. Une pseudo explication est évoquée ici.

X-I-8. Suppression de requêtes

Pour supprimer une Vue ou une Procédure Stockée, le principe reste identique à celui de la suppression d'une table.

Avec DAO, on utilisait un objet QueryDefs associé au nom de la requête, puis on appelait la méthode Delete.
Avec ADO, on dispose aussi d'une méthode Delete.

La procédure que j'ai écrite ci-avant pourrait servir de procédure générique pour l'exécution.

Procédure générique ADO
Sélectionnez
Sub ChangeSPCode(ByVal SQLCode As String)
Dim oCNX As ADODB.Connection
Dim oCAT As ADOX.Catalog

  Set oCNX = CreateObject("ADODB.Connection")
  Set oCAT = CreateObject("ADOX.Catalog")
  ''' Ouverture de la connexion
  With oCNX
     .ConnectionString = GetConnectionString()
     .Open
  End With
  With oCAT
    .ActiveConnection = oCNX
    .ActiveConnection.Execute SQLCode
  End With
  ''' Fermeture de la connexion
  If oCNX.State &lt;&gt; adStateOpen Then oCNX.Close
  ''' Libération des objets
  Set oCNX = Nothing
  Set oCAT = Nothing
End Sub

Il vous resterait plus qu'à changer le contenu de l'appel :

Utilisation de la procédure générique
Sélectionnez
Sub testChangeSPCode()
Dim SQLProc As String
  SQLProc = "DROP VIEW Liste_Des_Infusions"
  ChangeSPCode SQLProc
End Sub

X-J. Pour résumer

Donc, pour résumer, la mise à jour de votre code pour votre projet ADO serait beaucoup plus facilitée si vous optiez pour l'usage de procédures génériques.
L'avantage est que vous n'avez que peu de code à écrire ou à réécrire pour modifier l'ensemble de votre code.

XI. Accès aux données (DML : Data Manipulation Language)

Tout comme pour votre application Microsoft Access, l'accès aux données de votre nouveau projet se traduit par la modification du code Visual Basic for Application associé.
On parle alors de DML, abréviation de Langage de Manipulation de Données SQL.

Vous avez pu lire au cours des paragraphes précédents les différents changements nécessaires à la mise à jour de votre code Visual Basic pour tout ce qui est structure de votre base de données.

Cette section va aborder les différents cas de façon plus succincte en évoquant les principaux cas liés à l'accès aux données.

XI-A. Comment lire, modifier ou supprimer des données

Dans votre application Access et dans votre nouveau projet, l'accès aux données se traduit par des clauses SELECT, INSERT, UPDATE et DELETE au sein de chaînes SQL inscrites selon différents modes.

Après la phase de migration, la section nommée « Différences notoires à propos des requêtes » vous précise quelles différences existent pour la rédaction de votre code SQL.

Avec DAO, la manipulation des données était relativement facile à assimiler et à mettre en place. Avec ADO, le côté délicat réside au fait que le traitement des informations dans un Recordset dépend de son mode d'ouverture et peut parfois renvoyer des informations incorrectes par exemple lorsque l'on souhaite compter les enregistrements.

XI-A-1. Sélection d'enregistrements

Deux cas simples de SELECT pour affecter le RecordSource d'un f ormulaire

Affectation d'un RecordSource à un formulaire avec DAO
Sélectionnez
Sub InitializingRecordSource()
Dim oDB As DAO.Database
Dim oRS As DAO.Recordset
Dim SQL As String

    Set oDB = CurrentDb
    SQL = "SELECT * FROM TBLProducts WHERE QtyAvailable > 10"
    Set oRS = oDB.OpenRecordset(SQL, dbOpenDynaset)
    With oRS
        If .EOF Then
            SQL = Trim(Left(SQL, InStr(1, SQL, "WHERE") - 1))
        End If
        .Close
    End With
    Me.RecordSource = SQL
End Sub
Affectation d'un RecordSource à un formulaire avec ADO
Sélectionnez
Sub InitializingRecordSource()
Dim oCNX As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim SQL As String
  
    Set oCNX = CurrentProject.Connection  
    Set oRS = New ADODB.Recordset  
    SQL = "SELECT * FROM TBLProducts WHERE QtyAvailable > 10"  
    oRS.Open SQL, oCNX, adOpenDynamic, adLockPessimistic  
    With oRS    
        If .EOF Then      
            SQL = Trim(Left(SQL, InStr(1, SQL, "WHERE") - 1))    
        End If    .Close  
    End With  
    Me.RecordSource = SQL
End Sub

D'ores et déjà, vous pouvez remarquer qu'avec ADO, il y a un peu plus de code et ici, je sollicite la connexion en cours sans authentification. Dès lors que le phénomène d'authentification est requis, le code se complique en conséquence.


En DAO comme en ADO il existe quatre modes d'ouverture d'un Recordset :

DAO
Sélectionnez
''' En lecture seule
Set oRS = CurrentDb.OpenRecordset(SQL , , dbReadOnly)
''' En lecture seule et en avance seulement
Set oRS = CurrentDb.OpenRecordset(SQL , dbOpenForwardOnly, dbReadOnly)
''' En lecture / écriture
Set oRS = CurrentDb.OpenRecordset(SQL , dbOpenDynaset)
''' En ajout seulement
Set oRS = CurrentDb.OpenRecordset(SQL , , dbAppendOnly)
ADO
Sélectionnez
''' En lecture seule
oRS.Open SQL, oCNX
''' En lecture seule et en avance seulement
oRS.Open SQL, oCNX, adOpenForwardOnly, adLockReadOnly
''' En lecture / écriture
oRS.Open SQL, oCNX, adOpenDynamic, adLockOptimistic
''' En ajout seulement
oRS.Open SQL, oCNX, adOpenDynamic, adLockPessimistic

XI-A-2. Ajout d'enregistrements

Exemple de code pour ajouter un nouveau produit dans la table :

Ajout d'enregistrements avec DAO
Sélectionnez
Dim oRS As DAO.Recordset
  Set oRS = CurrentDb.OpenRecordset("TBLProducts", dbOpenDynaset)
  With oRS
    .AddNew
    .Fields("ProductName") = "Piri Piri"
    .Fields("IDfkCategory") = 8
    .Fields("IDfkSupplier") = 2
    .Fields("Price") = 3.25
    .Fields("QtyAvailable") = 17
    .Update
    .Close
  End With
  Set oRS = Nothing
End Sub
Ajout d'enregistrements avec ADO
Sélectionnez
Sub AddNewProduct()
Dim oRS As ADODB.Recordset
  Set oRS = New ADODB.Recordset
  oRS.Open "TBLProducts", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  With oRS
    .AddNew
    .Fields("ProductName") = "Piri Piri"
    .Fields("IDfkCategory") = 8
    .Fields("IDfkSupplier") = 2
    .Fields("Price") = 3.25
    .Fields("QtyAvailable") = 17
    .Update
    .Close
  End With
End Sub

XI-A-3. Suppression d'enregistrements

Exemple de code pour supprimer un produit dans la table :

Suppression d'enregistrements avec DAO
Sélectionnez
Sub DeleteProduct(ByVal ProductID As Long)
Dim oRS As DAO.Recordset
Dim SQL As String
  SQL = "SELECT * FROM TBLProducts WHERE IDProduct = " & ProductID
  Set oRS = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
  With oRS
   If Not .EOF Or .BOF Then
     .Delete
   End If
  .MoveFirst
  End With
  Set oRS = Nothing
End Sub
Suppression d'enregistrements avec ADO
Sélectionnez
Sub DeleteProduct(ByVal ProductID As Long)
Dim oRS As ADODB.Recordset
Dim SQL As String
  SQL = "SELECT * FROM TBLProducts WHERE IDProduct = " & ProductID
  Set oRS = New ADODB.Recordset
  oRS.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
  With oRS
   If Not .EOF Or .BOF Then
     .Delete adAffectCurrent
   End If
  .MoveFirst
  End With
  Set oRS = Nothing
End Sub

XI-A-4. Mise à jour d'enregistrements

Exemple de code pour mettre à jour un produit dans la table :

Mise à jour d'enregistrements avec DAO
Sélectionnez
Sub UpdateProduct(ByVal FieldName As String, ByVal FieldValue As String, ByVal ProductID As Long)
Dim oRS As DAO.Recordset
Dim SQL As String
  SQL = "SELECT * FROM TBLProducts WHERE IDProduct = " & ProductID
  Set oRS = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
  With oRS
    .Fields(FieldName) = FieldValue
    .Update
    .Close
  End With
  Set oRS = Nothing
End Sub
Mise à jour d'enregistrements avec ADO
Sélectionnez
Sub UpdateProduct(ByVal FieldName As String, ByVal FieldValue As String, ByVal ProductID As Long)
Dim oRS As ADODB.Recordset
Dim SQL As String
  SQL = "SELECT * FROM TBLProducts WHERE IDProduct = " & ProductID
  Set oRS = New ADODB.Recordset
  oRS.Open SQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
  With oRS
    .Fields(FieldName) = FieldValue
    .Update
    .Close
  End With
End Sub

XII. Les formulaires et les états

Ce sont ces objets qui constituent l'aspect visuel et papier de votre application. Bien que la façon de les initialiser soit relativement libre et laissée à la fantaisie des développeurs, il vous sera inéluctable de recourir à l'ouverture en mode Création de chacun d'eux et de vérifier la fameuse propriété RecordSource afin de contrôler que vous n'y avez pas par exemple logé une chaîne SQL Directe mal écrite, par exemple :

 
Sélectionnez
SELECT * FROM TBLCommandes WHERE DateCommande Between #01/01/2005# AND #01/31/2005#

Les caractères # sont proscrits en SQL server et remplacés par des ''

Ou encore

 
Sélectionnez
SELECT * FROM Clients WHERE (((Clients.Pays)="Brésil"));

Les caractères "" sont proscrits en SQL server et remplacés par des ''


Examen du code des formulaires et des états

Il en est de même au sein du code de la classe du formulaire ou de l'état où il vous faudra, comme cela est expliqué dans la deuxième partie du document, chercher les mots clés et remplacer le code correspondant.

Tri dans les requêtes

Un examen approfondi des ComboBoxes ou des ListBoxes doit subir le même sort. Souvent, la propriété RowSource de ces contrôles est alimentée par une requête statique. Si tel est le cas, vous seriez tenté de la remplacer par une Vue où la clause ORDER BY est interdite.

XII-A. Les fonctions DAO et les fonctions d'opération

Dans mes projets de base de données Microsoft Access, j'utilise des fonctions VBA-SQL génériques dynamiques à profusion. Ces dernières ont un rôle bien spécifique et plus particulièrement dédiées à générer des requêtes de façon dynamique ce qui rejoint l'équivalent de ce que ferait une procédure stockée. Dans mon cas, j'ai dû transformer la plupart de ces fonctions VBA en Procédures Stockées de manière à ce que les opérations sur les données soient exécutées sur le serveur et non sur le poste client.

La distinction des fonctions à convertir et celles à adapter pour les laisser en local dans le projet nécessite une analyse.

En ce qui concerne les fonctions d'opérations telles que les fonctions de conversion de chaînes de caractères, de calculs mathématiques, etc. qui sont censées agir sur des données locales, je vous préconise tout comme je l'ai fait pour mon projet, de les conserver dans votre module VBA afin de ne pas trop surcharger le serveur même s'il peut se le permettre.

Pour cela, vous avez la possibilité d'avoir recours aux outils de performances dans l'Analyseur de Requêtes afin de vous rendre compte qu'est-ce qui, de la fonction VBA ou de la fonction SQL monopolise peu le serveur tout en restant performante.

XIII. Migration en douceur

Vous pouvez avoir recours à la migration en douceur de manière à ce que votre projet continue de tourner avec DAO en mode MDE/MDB et que de votre côté, vous fassiez évoluer le code au fur et à mesure vers ADO/SQL Server.

Notez que ce choix est fort délicat, car il laisse supposer que tout est dynamique dans vos formulaires et vos états et il n'est pas question ici d'adapter les séquences DAO afin de pouvoir mêler de l'ADO dans ce mode de migration.

Par exemple :

 
Sélectionnez
'''-----------------------------------
'Mettre True pour basculer en mode ADO
#Const MODE_ADO = False
'''----------------------------------- 
Public Sub AddNewProduct()

  #If MODE_ADO Then
    Dim oRS As ADODB.Recordset
    Set oRS = New ADODB.Recordset
    oRS.Open "TBLProducts", CurrentProject.Connection, adOpenStatic, adLockOptimistic
  #Else
    Dim oRS As DAO.Recordset
    Set oRS = CurrentDb.OpenRecordset("TBLProducts", dbOpenDynaset)
  #End If

  With oRS
    .AddNew
    .Fields("ProductName") = "Piri Piri"
    .Fields("IDfkCategory") = 8
    .Fields("IDfkSupplier") = 2
    .Fields("Price") = 3.25
    .Fields("QtyAvailable") = 17
    .Update
    .Close
  End With
  Se oRS =  Nothing
End Sub

Ici, j'utilise la compilation conditionnelle à l'aide de la constante conditionnelle MODE_ADO qui prend la valeur True ou False.

De ce fait, le bloc #If / #End If vérifie dans quel mode nous sommes et initialise le Recordset oRS en conséquence. J'ai pris un exemple simple, car du fait que la méthode AddNew est quasi identique en DAO et en ADO, il a été facile à mettre en œuvre pour qu'il soit le plus parlant possible.

XIV. Conclusion

Ainsi que vous avez pu le lire, la migration d'une base de données Microsoft Access vers un projet Microsoft ADP SQL Server n'est pas une mince affaire.

Mais il a été évoqué qu'en compensation, le choix de la plateforme SQL Server ne renvoyait que des avantages.

Une préétude des différentes phases de migration est fortement conseillée.

Bien entendu vous ne serez pas à l'abri d'erreurs potentielles et de nombreux tests sont à réaliser afin de juger que tout est opérationnel. Je vous préconiserais alors d'utiliser toujours votre classeur Excel évoqué au départ et sur lequel vous notifierez les actions faites, celles à faire et celles pas encore testées.

Je tiens à remercier Tofalu pour son support qui m'a permis d'enrichir une partie de ce document.

Définitions

- MSDE
Microsoft Data Engine nommé également Microsoft SQL Server Desktop Engine.
MSDE est le moteur de base de données né avec Access 2000 exploitable avec les projets Access (ADP) et est 100 % compatible avec SQL Server.

- GUID
Le GUID (Globally Unique IDentifier) est un nombre unique de 128 bits affecté à un objet ou à un enregistrement de table par exemple lors de sa création. Cette séquence alphanumérique est utilisée pour identifier chaque composant O.L.E (Object Linking and Embedding) dans les HKEY_CLASSES_ROOT de la Base de Registre.
Par exemple, Word.Application possède son CLSID sous forme de GUID : {000209FF-0000-0000-C000-000000000046}.

- ADP/ADE
La dénomination ADP/ADE est appliquée aux projets Access comme la dénomination MDB/MDE l'est pour les bases de données Access.
Un fichier ADE tout comme un fichier MDE n'autorise plus de modifier les formulaires, états ou les modules.

- Entreprise Manager
SQL Server Enterprise Manager est le principal outil administratif de Microsoft® SQL Server™ 2000. Il fournit une interface utilisateur compatible Microsoft Management Console (MMC)- qui permet aux utilisateurs d'effectuer les tâches relatives à la gestion et l'administration des serveurs et des bases de données.

- DAO
Data Access Objects : composant exploité au sein d'Access pour manipuler des données de base de données Access avec le langage VBA. Bien que l'on puisse utiliser ADO avec des Bases de données Microsoft Access, DAO s'y avère plus approprié.

- ADO
ActiveX Data Objects : composant exploité au sein d'Access pour manipuler tous types de données issues de bases de données hétérogènes avec le langage VBA.

- T-SQL
Transac SQL : c'est un langage procédural qui permet de coder entre autres, les procédures stockées et les triggers pour la base de données SQL Server.
Pour plus d'informations, cliquez ici.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Copyright © 2019 Jean-Philippe AMBROSINO. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.