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

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.

• 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…

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…

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é.

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

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.

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

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.

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

- Inversement, les boutons et 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 et

Dans mon exemple, je choisis de migrer toutes les tables…

Cliquez alors sur Suivant…

À 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.

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

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.

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.

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.

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.

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

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.

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 ».

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

Vous cliquez sur Terminer et le cycle de migration commence.

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

=> Pour les requêtes

=> Pour les formulaires

=> Pour les états

=> Pour les modules

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…

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…

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.

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.

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▲

VIII-B-4-b. Les erreurs les plus fréquemment rencontrées durant la phase de migration▲

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.

• La base de données elle-même directement avec la méthode CurrentDB :

CurrentDb.Execute SQLUpdate, dbSeeChanges

Où 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 :

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 :

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")
...

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 :

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

oRst.Open SQL, oCNX

où 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…

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.

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 :

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 :

If not CurrentProject.IsConnected Then ConnectSQLDatabase

vous provoquerez une nouvelle instanciation de l'objet CurrentProject.

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é.

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.

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

"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.

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.

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

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

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.

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 :

    '[...]
    ''' 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
    '[...]

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 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 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.

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.

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

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.

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

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.

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

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.

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

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 <> 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

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 :

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 :

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

Ou avec la chaîne complète :

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.

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

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

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 <> 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

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

Pour obtenir ce message, exécutez ce code :

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 <> 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.

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.

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 <> 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 :

Sub testChangeSPCode()
Dim SQLProc As String
  SQLProc = "DROP VIEW Liste_Des_Infusions"
  ChangeSPCode SQLProc
End Sub

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

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

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 :

''' 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)

''' 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

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

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

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

Exemple de code pour supprimer un produit dans la table :

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

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

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

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

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