3/8	[ Sommaire ]
1. Tutoriaux Self-Access : Sommaire 2. Le jargon Access décrypté 3. Migrer une application MDB/DAO vers ADP/ADO 4. Imprimer un état au format PDF 5. Un formulaire "Mot de passe" générique 6. Maîtriser OpenForm et OpenReport 7. Exécuter une action à une heure précise 8. Installer Access en réseau Voir aussi :  Le "Grenier" : Questions/Réponses sur Access

3/8

Migration MDB/DAO vers ADP/ADO

Ce document a pour objectifs de vous permettre une migration en phases :

1. Apprendre quelques différences entre une base de données Access et une base SQLServer
2. Apprendre quelques différences entre un programme Access MDB et un projet Access ADP
3. Apprendre quelques différences entre DAO et ADO
4. Comment préparer votre application afin qu'elle continue à fonctionner en DAO/MDB
5. Quelques fonctions ADO intéressantes
6. Comment la migrer (pour tests puis définitivement) vers ADP/ADO/SQLServer

Avant de consulter ce document, il est conseillé d’être habitué au développement sous Access/DAO et à l’utilisation de VBA (Visual Basic pour Applications).
Vous pouvez également consulter la page des définitions.

Quelques différences entre une base de données Access et une base SQLServer

Une application Access classique comporte 2 bases de données : la base "frontale" (qui contient les requêtes, formulaires, états, macros et modules et qui est visible par l’utilisateur), et la base "dorsale" (qui contient les tables et les relations et qui peut être stockée sur le serveur). Les termes "frontale" et "dorsale" distingueront ci-dessous les 2 bases de données.

C'est là qu'est l'astuce : on peut installer SQLServer ou MSDE et utiliser un projet Access (ADP) pour gérer la base assez facilement.

Notons certaines différences importantes entre ces bases:

Quelques différences entre une base frontale Access MDB et un projet Access ADP

Cette différence n'existe qu'à partir d'Access 2000.

Quelques différences entre DAO et ADO

DAO et ADO n'ont pas de vrai lien avec le passage entre MDB et ADP. Cependant, ADO est le système que Microsoft se propose de développer dans l'avenir. Il est donc judicieux de profiter de la migration MDB->ADP et Access->SQLServer pour passer également DAO->ADO

Certains disent que ADO est lent. Mes tests le montre plus ou moins égal à DAO.

Notons les différences essentielles :

Préparer votre application afin qu'elle continue à fonctionner en DAO/MDB

Préparation dans la manipulation des données

Nous allons modifier le programme MDB/DAO/MDB afin de lui permettre la migration en douceur vers un projet ADP/ADO/SQLServer. L’idée est de conserver à la fois les fonctionnalités DAO et ADO dans la même base de données, en n’activant que celle nécessaire.

ADO ou DAO

Tout d’abord, si votre application doit faire fonctionner les 2 systèmes ADO ou DAO, il faut vérifier que les 2 références ont été définies.Mon doc ne propose que le passage d’une application "mono-DAO" vers une "mono-ADO". On active donc une fois une lib, (MDB), une fois l’autre (ADP)

Comme les instructions DAO ne se compilent pas sans la librairie DAO et les ADO sans la librairie ADO, il est important d'isoler les deux types de commandes.

Dans le programme MDB, définition d'une variable de compilation indiquant si on est en ADO

#Const IS_ADO = False

Comme ce type de constante n'est pas public, cette ligne doit être déclarée dans chaque module, chaque formulaire et chaque état.

Il sera simple, après le passage de faire un remplacement global "#Const IS_ADO = False" en "#Const IS_ADO = True" 

Types de champs en base de données

Comme nous utilisons des types de variables DAO (p.ex. DBText) lors de modification, création de tables, et que ces types n'existent pas dans ADO, on indique une table de conversion dans le cas ADO:

#If IS_ADO Then
Public Const DBDouble = adDouble
Public Const DBText = adLongVarWChar
Public Const DBLong = adInteger
Public Const DBBoolean = adBoolean
Public Const DBDate = adDate
' et les autres si vous en utilisez
#End If

Connexion à la base 

On a l'habitude de travailler avec CurrentDb. Fini avec ADO. On prépare donc l'utilisation de la "connexion":

#If IS_ADO Then
Public cnnLocal As ADODB.Connection
#End If

et une routine de connexion à appeler une fois au démarrage de l'application:

Public Sub DBConnect()
#If IS_ADO Then
 If cnnLocal Is Nothing Then Set cnnLocal = _
	CurrentProject.Connection
#End If
End Sub

Exécution de commandes SQL

L'exécution de commandes SQL est faite différemment, préparons donc une commande qui répond aux deux modes.

Elle sera appelée par :  DBExecute "ma commande"

Public Sub DBExecute(ByVal Cmd As String)
On Error Resume Next
#If IS_ADO Then
 If cnnLocal Is Nothing Then DBConnect
 cnnLocal.Execute Cmd
#Else 
 CurrentDb.Execute Cmd
#End If
' Add index; index existe déjà
If Err.Number = -2147217900 Then Exit Sub 
If Not Err.Number = 0 Then
MsgBox "Error " & Str$(Err.Number) & " ( " & Err.Description & ") _
	encountered at" & vbCrLf & Cmd
End If
End Sub

Edition de recordset 

Lorsqu'on veut modifier un élément dans  un recordset, en DAO, on l'"Edit". Plus en ADO. Donc, à chaque Edit:

#If Not IS_ADO Then
MonRS.edit
#End If

Ouverture de recordset

Les fonctions suivantes permettent d'ouvrir des recordsets dans les deux modes (à valider encore mais cela semble fonctionner):

Public Function OpenRO(ByVal SelectSentence TableAndCond As String) _
	As Recordset
 #If IS_ADO Then ' ADO (SQLServer)
  Set OpenRO = New ADODB.Recordset
  If cnnLocal Is Nothing Then DBConnect
  OpenRO.Open SelectSentence , cnnLocal
 #Else ' DAO (Access)
  Set OpenRO = CurrentDb.OpenRecordset(SelectSentence , , dbReadOnly)
 #End If
End Function

Public Function OpenScan(ByVal SelectSentence As String ) As Recordset
 #If IS_ADO Then ' ADO (SQLServer)
 Set OpenScan = New ADODB.Recordset
 If cnnLocal Is Nothing Then DBConnect
 OpenScan.Open SelectSentence , cnnLocal
 #Else ' DAO (Access)
 Set OpenScan = CurrentDb.OpenRecordset(SelectSentence , _
	dbOpenForwardOnly, dbReadOnly)
 #End If
End Function

Public Function OpenRW(ByVal SelectSentence As String ) As Recordset
 #If IS_ADO Then
 Set OpenRW = New ADODB.Recordset
 If cnnLocal Is Nothing Then DBConnect
 OpenRW.Open SelectSentence , cnnLocal, adOpenDynamic, adLockOptimistic
 #Else
 Set OpenRW = CurrentDb.OpenRecordset(SelectSentence )
 #End If
End Function

Public Function OpenA(ByVal SelectSentence As String) As Recordset
 #If IS_ADO Then ' ADO (SQLServer). Pour l'instant, mode update normal
 Set OpenA = New ADODB.Recordset
 If cnnLocal Is Nothing Then DBConnect
 OpenA.Open SelectSentence, cnnLocal, adOpenDynamic, adLockOptimistic
 #Else
 Set OpenA = CurrentDb.OpenRecordset(SelectSentence, , dbAppendOnly)
 #End If
end function

Récupération d'un NuméroAuto

Il peut arriver par exemple. que lors de la création d'une commande et de lignes de commandes, on désire:

• créer la commande (addnew... update)
• récupérer son N° (p.ex. Commande_no)
• créer les lignes de commande en y insérant le N° de commande (ligne_commande=commande_no)

Dans ce cas, en DAO/Access, on récupère le numéro Autonum avant le .update. Dans le cas ADO/SQLServer, on le fait après !:

#If IS_ADO Then
 RS.Update
 NouveauNo = RS!LeNumauto
#Else
 NouveauNo = RS!LeNumauto
 RS.Update
#End If

Conversion de types

On sait que les variables de type date et string doivent respecter un certain format. Avec Access et Sqlserver, le format est différent.

Voici trois fonctions qui règlent le problème :

Un string délimité par des ' ne doit pas avoir de ' simple dans son contenu

Public Function SqlString(ByVal S As String) As String
 If InStr(S, "'") = 0 Then SqlString = "'" & S & "'": Exit Function
 Dim S2 As String, I As Integer
 S2 = ""
 For I = InStr(S, "'") To Len(S)
  If Mid$(S, I, 1) = "'" Then S2 = S2 & "'"
  S2 = S2 & Mid$(S, I, 1)
 Next I
 SqlString = "'" & S2 & "'"
End Function

Une date est délimitée par des # dans Access et ' dans SQLServer (+ ordre des JJ/MM):

Public Function SqlDate(ByVal DT As Date) As String
 If Not IsDate(DT) Then SqlDate = "": Exit Function
 Dim S As String: S = Format(DT, "dd/mm/yyyy")
 ' Doit forcer le / car Format(dt...) utilise le separateur Windows
 #If IS_ADO Then
 SqlDate = "'" + S + "'"
 #Else
 SqlDate = "#" + Mid$(S, 4, 2) + "/" + Mid$(S, 1, 2) + "/" + _
	Right$(S, 4) + "#"
 #End If
End Function

Public Function SqlDateTime(ByVal DT As Date) As String
 If Not IsDate(DT) Then SqlDateTime = "": Exit Function
 Dim S As String: S = Format(DT, "dd/mm/yyyy")
 Dim S2 As String: S2 = Format(DT, "hh:mm:ss")
 #If IS_ADO Then
 SqlDateTime = "'" + S + " " + S2 + "'"
 #Else
 SqlDateTime = "#" + Mid$(S, 4, 2) + "/" + Mid$(S, 1, 2) + "/" + _
	Right$(S, 4) + " " + Mid$(S2, 1, 2) + ":" + Mid$(S2, 4, 2) + _
	":" + Right$(S2, 2) + "#"
 #End If
End Function

Et Like ?

Like aussi change, une * pour un %:

#If IS_ADO Then
 S = "SELECT * FROM …. where xxx like 'E%';" 
#Else
S = "SELECT * FROM …. where xxx like 'E*';" 
#End If

Parcours des tables

Afin de parcourir les tables, la routine suivante permet de passer sur les deux modes (utilisation de DMO):

#If IS_ADO Then
 Dim Tbl As ADOX.Table
 DBConnect
 Dim Cat As New ADOX.Catalog
 Set Cat.ActiveConnection = cnnLocal
 For Each Tbl In Cat.Tables
 ' Eventuellement, filtrer le Tbl.type = "TABLE"
#Else
 Dim Tbl As TableDef
 For Each Tbl In CurrentDb.TableDefs
#End If
 ' utilisation de Tbl.Name, etc.
 #If IS_ADO Then
  Dim Fld As ADOX.Column
  For Each Fld In Tbl.Columns
 #Else
  Dim Fld As Field
  For Each Fld In Tbl.Fields
 #End If
    ' utilisation de Fld.Name, etc
  Next Fld
Next Tbl

Parcours des formulaires dans ADP

Attention, en ADP, on peut parcourir tous les formulaires, ouverts ou fermés de manière simple:

#If IS_ADO Then
Dim Frm As Object
Dim Ob As Object: Set Ob = CurrentProject
For Each Frm In CurrentProject.AllForms
 '  utilisation de Frm.Name, etc.
Next Frm
#Else
Dim Con As Container, Doc As Document
Dim DBa As Database: Set DBa = CurrentDb()
Set Con = DBa.Containers("Forms")
For Each Doc In Con.Documents
' utilisation de Doc.Name, etc.
Next Doc
#End If

Fonctions dans les vues

Les vues ne doivent comporter aucune fonction, nz(), iif(), fonctions Access, etc.

Ordre de tri dans les vues

Si vous désirez transformer les requêtes en vues (et non en procédures stockées), il faut noter que les vues SQLServer ne sont pas triées !

Donc, faites des requêtes non triées et ouvrez-les avec "order by..." dans vos Recordsets, Formulaires, listes déroulantes (combos), états, etc.

Fonctions ADP/ADO intéressantes

Déclaration de SQLDmo

SQLDmo permet l'exploitation des catalogues :

#If IS_ADO Then
 Dim Cmd As New ADODB.Command, catCurr As New ADOX.Catalog
 Dim PRs As Views
#End If

Sauvegarde (Backup) d'une base SQLServer/MSDE

Attention de bien déclarer en référence SQLServer DMO

#If IS_ADO Then
Echo True, "Backup running"
On Error GoTo ErrorBackup
DoCmd.Hourglass True

Dim oServer As Object
Dim oBackup As New SQLDMO.Backup

Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ".", "sa" ' login to the local server

oBackup.Action = SQLDMOBackup_Database ' or SQLDMOBackup_Differential
oBackup.Database = CurrentProject.Connection.Properties("Initial Catalog")
oBackup.Files = "c:\temp\MonBackup.EXP" ' For files
oBackup.BackupSetName = "Backup_Full"
oBackup.BackupSetDescription = "Full backup of MyApplic."
oBackup.Initialize = True
oBackup.SQLBackup oServer
BackupEnd:
DoCmd.Hourglass False
MsgBox "Backup of " & oBackup.Database & " to " & oBackup.Files & _
	" ended", vbInformation
Exit Function

ErrorBackup:
MsgBox "Backup error" & vbCrLf & Err.Description
Resume BackupEnd
#End If

Migrer (pour tests puis définitivement) vers ADP/ADO/SQLServer

Vous avez donc

1. corrigé toutes les vues afin qu'elles ne contiennent aucune fonction, aucun ordre de tri
2. modifié le code pour isoler les ouvertures de recordset
3. modifié le code pour isoler les .edit
4. modifié le code pour isoler les prises de NuméroAuto
5. modifié le code pour le parcours des tables, queries, formes, etc.
6. compilé votre programme
7. longuement tout testé

En résumé, votre programme a subi de nombreuses modifications, vous avez sué et ragé et l'utilisateur n'y voit pas de différence. Félicitations !

Vous êtes prêt pour le grand saut.

Relevez les librairies que vous utilisez; par exemple. avec la procédure suivante:

Open "c:\References.LOG" For Output As #1
Print #1, "Executed with"
For Each Ref In References
If Ref.IsBroken Then
 Err = True
 MsgBox "Référence manquante"
Else
Dim S1 As String, S2 As String
S1 = UCase$(Ref.Name)
S2 = "???\" & S1
On Error Resume Next
S2 = UCase$(Ref.FullPath)
Dim FPath As String: FPath = S2
While InStr(S2, "\")  0: S2 = Mid$(S2, InStr(S2, "\") + 1): Wend
 Print #1, S1 & " " & Ref.Major & "." & Ref.Minor & ": " & FPath
Next Ref
Close #1

Dernière vérification avant migration

1. Vérifiez que les contraintes d'intégrité sont respectées. Lancez des requêtes afin de supprimer les orphelins, Access en crée par accident, SQLServer ne les acceptera pas (heureusement)
2. Vérifiez que chaque table a une clef primaire unique et avec Null interdit

Le nouveau projet

1. Installez SQLServer ou MSDE
2. Créez un nouveau projet Access 2000 et créez une nouvelle base
3. Créez un module
4. Activez vos références en remplaçant DAO par ADO (Microsoft ActiveX Data Object Library x.y et Microsoft ADO Ext. x.y for DDL and Security) et SQLDMO (Microsoft SQLDMO Library) 
5. Fermez et sauvez le module
6. Supprimez le module

Sauvez précieusement ce projet en version "0". Il servira de base de travail pour la suite car vous risquez de faire quelques cycles de migration, alors, autant partir d'une base saine.

Migrer

1. Copiez votre projet vide vers une version de travail
2. Ouvrez-le et Fichier-Importer-données externes
3. Importez les tables, les vues, les états, les modules et (option) les menus et barres d'outils
4. Editez un module
5. Ramplacer "#Const IS_ADO = False" par "#Const IS_ADO = True" dans tout le projet
6. Compilez
7. Tout doit passer, sinon, modifiez dans le MDB et recommencez au pt 1

Premiers tests

1. Ouvrez chaque table
2. Ouvrez chaque requête

Contraintes et clefs secondaires

Vous devez créer un schéma et y insérer les liens et clefs étrangères. 

Ceci est fastidieux mais vous ne devrez le faire qu'une fois, ensuite, tout est dans la base et non dans le programme. Le cas échéant, à une prochaine migration, ne prenez pas les tables et tout sera OK 

Il peut être intéressant de développer un petit module qui pose automatiquement les contraintes afin de pouvoir les imposer à de nouvelles base gräce à la sub suivante:

Private Sub InsConstraint(SonTable As String, ByVal SonField as string, _
	FatherTable As String, byval FatherField as string)
DBConnect
DBExecute "create index " & SonTable & "_" & SonField & " on " & _
	SonTable & " (" & SonField & ")" 
DBExecute  "alter table " & SonTable & " with check add constraint " & _
        SonField & "_" & FatherTable & _
        " foreign key (" & SonField & ") references " & FatherTable & _
	" (" & FatherField  & ")"
End Sub

L'appel est alors simplement

InsConstraint "LigneCommande","LigneCommande_Commande_no", _
	"Commande","Commande_no"

Si cette procédure est bien faite, vous pourrez l'utiliser après chaque migration de données, le schéma peut alors être construit automatiquement !

C'est le moment !

Testez votre application.

Lorsqu'il y a une erreur, corrigez-la dans le projet, tirez-en une leçon et corrigez tout ce qui doit l'être dans le MDB pour le prochain cycle de migration

Cycles

Si vous le permettez, je vous conseille de répéter le cycle jusqu'à ce que vous ayez pu réaliser un passage complet sans erreur

Enfin...

Définissez les paramètres de démarrage de votre projet (1er écran, barre d'outils par défaut, etc.) comme dans votre MDB et applaudissez-vous !

Cette section vous intéresse ? Abonnez-vous !