Migrer une base de données Access vers SQL Server

S’applique à

Access pour Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Obtenir de l’aide sur Copilot

Nous avons tous des limites, et une base de données Access ne fait pas exception. Par exemple, une base de données Access a une taille limite de 2 Go et ne peut pas prendre en charge plus de 255 utilisateurs simultanés. Ainsi, lorsqu’il est temps pour votre base de données Access de passer au niveau supérieur, vous pouvez migrer vers SQL Server. SQL Server (qu’il soit sur site ou dans le cloud Azure) prend en charge de plus grandes quantités de données, un plus grand nombre d’utilisateurs simultanés et une plus grande capacité que le moteur de base de données JET/ACE. Ce guide vous permet de démarrer sans problème votre parcours SQL Server, aide à préserver les solutions Access frontales que vous avez créées et, nous l’espérons, vous motivera à utiliser Access pour de futures solutions de base de données. Utilisez l’Assistant Migration Microsoft SQL Server (SSMA) pour réussir la migration, suivez ces étapes.

Étapes de migration de base de données vers SQL Server

Avant de commencer

Les sections suivantes fournissent des informations générales et d’autres pour vous aider à démarrer.

À propos des bases de données fractionnées

Tous les objets de base de données Access peuvent se trouver dans un seul fichier de base de données ou être stockés dans deux fichiers de base de données : une base de données frontale et une base de données principale. Cette opération est appelée fractionnement de la base de données . Elle est conçue pour faciliter le partage dans un environnement réseau. Le fichier de base de données principal doit uniquement contenir des tables et des relations. Le fichier frontal doit contenir uniquement tous les autres objets, y compris les formulaires, états, requêtes, macros, modules VBA et tables liées à la base de données principale. Lorsque vous migrez une base de données Access, cela est similaire à une base de données fractionnée en ce sens que SQL Server agit comme un nouveau serveur principal pour les données qui se trouvent désormais sur un serveur.

Par conséquent, vous pouvez toujours gérer la base de données Access frontale avec des tables liées aux tables SQL Server. Vous pouvez ainsi bénéficier du développement rapide d’applications d’une base de données Access, ainsi que de l’évolutivité de SQL Server.

Avantages de SQL Server

Encore besoin d’être convaincu pour migrer vers SQL Server ? Voici quelques avantages supplémentaires à prendre en compte :

Plus d’utilisateurs simultanés SQL Server peut gérer beaucoup plus d’utilisateurs simultanés qu’Access et réduit les besoins en mémoire lorsque d’autres utilisateurs sont ajoutés.
Disponibilité accrue Avec SQL Server, vous pouvez sauvegarder de manière dynamique, incrémentielle ou complète, la base de données pendant son utilisation. Ainsi, vous n’êtes pas obligé de forcer les utilisateurs à quitter la base de données pour sauvegarder les données.
Performances élevées et évolutivité En règle générale, les performances d’une base de données SQL SQL Server sont meilleures que celles d’une base de données Access, en particulier avec une base de données volumineuse de plusieurs téraoctets. En outre, SQL Server traite les requêtes beaucoup plus rapidement et efficacement en traitant les requêtes en parallèle, à l’aide de plusieurs threads natifs au sein d’un même processus pour gérer les demandes des utilisateurs.
Sécurité renforcée À l’aide d’une connexion approuvée, SQL Server s’intègre à la sécurité du système Windows pour fournir un accès unique et intégré au réseau et à la base de données, en utilisant le meilleur des deux systèmes de sécurité. Cela facilite considérablement la gestion des systèmes de sécurité complexes. SQL Server constitue le stockage idéal pour les informations sensibles telles que les numéros de sécurité sociale, les données de carte de crédit et les adresses confidentielles.
Récupérabilité immédiate En cas de panne du système d’exploitation ou de panne de courant, SQL Server peut automatiquement restaurer la base de données à un état cohérent en quelques minutes et sans aucune intervention de l’administrateur de base de données.
Utilisation du VPN Access et les réseaux privés virtuels (VPN) ne s’entendent pas. Mais avec SQL Server, les utilisateurs distants peuvent toujours utiliser la base de données frontale Access sur un ordinateur de bureau et le serveur principal SQL Server situé derrière le pare-feu VPN.
Azure SQL Server Outre les avantages de SQL Server, il offre une évolutivité dynamique sans temps d’arrêt, une optimisation intelligente, une évolutivité et une disponibilité mondiales, l’élimination des coûts matériels et une administration réduite.

Choisissez la meilleure option de serveur Azure SQL

Si vous migrez vers Azure SQL Server, vous avez le choix entre trois options, chacune présentant des avantages différents :

Base de données unique/pools élastiques Cette option possède son propre ensemble de ressources gérées via un serveur SQL Database. Une base de données unique est comme une base de données contenue dans SQL Server. Vous pouvez également ajouter un pool élastique, qui est une collection de bases de données avec un ensemble partagé de ressources gérées via le serveur SQL Database. Les fonctionnalités de SQL Server les plus couramment utilisées sont disponibles avec des sauvegardes, des correctifs et des récupérations intégrés. Toutefois, il n’existe aucune garantie de temps de maintenance exact et la migration à partir de SQL Server peut être difficile.
Managed instance Cette option est un regroupement de bases de données système et utilisateur avec un ensemble partagé de ressources. Un instance managé s’apparente à une instance de la base de données SQL Server qui présente une compatibilité élevée avec SQL Server en local. Un instance géré intègre les sauvegardes, les correctifs et la récupération. Il est facile à migrer à partir de SQL Server. Toutefois, un petit nombre de fonctionnalités de SQL Server ne sont pas disponibles et aucune durée de maintenance exacte n’est garantie.
Machine virtuelle Azure Cette option vous permet d’exécuter SQL Server à l’intérieur d’une machine virtuelle dans le cloud Azure. Vous disposez d’un contrôle total sur le moteur SQL Server et d’un chemin de migration facile. Mais vous devez gérer vos sauvegardes, correctifs et récupération.

Pour plus d’informations, voir Choix du chemin de migration de votre base de données vers Azure et Qu’est-ce qu’Azure SQL ?.

Premiers pas

Il existe quelques problèmes que vous pouvez résoudre d’emblée et qui peuvent simplifier le processus de migration avant d’exécuter SSMA :

Ajouter des index de table et des clés primaires Vérifiez que chaque table Access possède un index et une clé primaire. SQL Server exige que toutes les tables aient au moins un index et qu’une table liée ait une clé primaire si la table peut être mise à jour.
Vérifier les relations entre clé primaire et étrangère Vérifiez que ces relations sont basées sur des champs dont les types et tailles de données sont cohérents. SQL Server ne prend pas en charge les colonnes jointes avec des types de données et des tailles différents dans les contraintes de clé étrangère.
Supprimer la colonne Pièce jointe SSMA ne migre pas les tables qui contiennent la colonne Pièces jointes.

Avant d’exécuter SSMA, procédez comme suit.

Fermez la base de données Access.
Assurez-vous que les utilisateurs actuels connectés à la base de données ferment également la base de données.
Si le format de fichier de la base de données est .mdb, supprimez la sécurité de niveau utilisateur.
Sauvegardez votre base de données. Pour plus d’informations, voir Protéger vos données avec des processus de sauvegarde et de restauration.

Conseil Envisagez d’installer l’édition de Microsoft SQL Server Express sur votre bureau, qui prend en charge jusqu’à 10 Go et constitue un moyen gratuit et plus facile d’exécuter et de case activée votre migration. Lorsque vous vous connectez, utilisez LocalDB comme instance de base de données.

Conseil Si possible, utilisez une version autonome d’Access.

Exécutez SSMA

Microsoft fournit l’Assistant Migration Microsoft SQL Server (SSMA) pour faciliter la migration. SSMA migre principalement les tables et sélectionne les requêtes sans paramètres. Les formulaires, états, macros et modules VBA ne sont pas convertis. Le Explorer Métadonnées SQL Server affiche vos objets de base de données Access et SQL Server objets, ce qui vous permet d’examiner le contenu actuel des deux bases de données. Ces deux connexions sont enregistrées dans votre fichier de migration si vous décidez de transférer d’autres objets à l’avenir.

Remarque Le processus de migration peut prendre un certain temps en fonction de la taille de vos objets de base de données et de la quantité de données à transférer.

Pour migrer une base de données à l’aide de SSMA, commencez par télécharger et installer le logiciel en double-cliquant sur le fichier MSI téléchargé. Assurez-vous d’installer la version 32 ou 64 bits adaptée à votre ordinateur.
Après avoir installé SSMA, ouvrez-le sur votre bureau, de préférence à partir de l’ordinateur contenant le fichier de base de données Access.
Vous pouvez également l’ouvrir sur un ordinateur qui a accès à la base de données Access à partir du réseau dans un dossier partagé.
Suivez les instructions de début dans SSMA pour fournir des informations de base telles que l’emplacement de SQL Server, la base de données Access et les objets à migrer, les informations de connexion et si vous souhaitez créer des tables liées.
Si vous migrez vers SQL Server 2016 ou version ultérieure et souhaitez mettre à jour une table liée, ajoutez une colonne rowversion en sélectionnant Outils> de révisionParamètres>du projet Général.
Le champ rowversion permet d’éviter les conflits d’enregistrement. Access utilise ce champ rowversion dans une SQL Server table liée pour déterminer la date de la dernière mise à jour de l’enregistrement. De plus, si vous ajoutez le champ rowversion à une requête, Access s’en sert pour re-sélectionner la ligne après une opération de mise à jour. Cela améliore l’efficacité en évitant les erreurs de conflit d’écriture et les scénarios de suppression d’enregistrements qui peuvent se produire lorsqu’Access détecte des résultats différents de la soumission d’origine, comme cela peut se produire avec les types de données numériques à virgule flottante et les déclencheurs qui modifient les colonnes. Toutefois, évitez d’utiliser le champ rowversion dans les formulaires, les états ou le code VBA. Pour plus d’informations, consultez rowversion.
Remarque Évitez de confondre rowversion avec les horodatages. Bien que le mot clé timestamp soit synonyme de rowversion dans SQL Server, vous ne pouvez pas utiliser rowversion comme moyen d’horodatage d’une entrée de données.
Pour définir des types de données précis, sélectionnez Outils >de révisionMappage de type>de paramètres de projet. Par exemple, si vous stockez uniquement du texte anglais, vous pouvez utiliser le type de données varchar plutôt que nvarchar .

Convertir des objets

SSMA convertit les objets Access en objets SQL Server, mais ne copie pas immédiatement les objets. SSMA fournit une liste des objets suivants à migrer afin que vous puissiez décider si vous souhaitez les déplacer vers SQL Server base de données :

Tables et colonnes
Sélectionner Requêtes sans paramètres.
Clés primaires et étrangères
Index et valeurs par défaut
Contraintes de vérification (autoriser une propriété de colonne de longueur nulle, règle de validation de colonne, validation de table)

Il est recommandé d’utiliser le rapport d’évaluation SSMA, qui affiche les résultats de la conversion, y compris les erreurs, les avertissements, les messages d’information, les estimations de temps pour effectuer la migration et les étapes de correction des erreurs individuelles à effectuer avant de déplacer les objets.

La conversion d’objets de base de données prend les définitions d’objet des métadonnées Access, les convertit en syntaxe Transact-SQL (T-SQL) équivalente, puis charge ces informations dans le projet. Vous pouvez ensuite afficher les objets SQL Server ou SQL Azure et leurs propriétés à l’aide de SQL Server ou de l’Explorateur de métadonnées SQL Azure.

Pour convertir, charger et migrer des objets vers SQL Server, suivez ce guide.

Conseil Une fois que vous avez correctement migré votre base de données Access, enregistrez le fichier projet pour une utilisation ultérieure afin de pouvoir migrer à nouveau vos données à des fins de test ou de migration finale.

Attacher les tables

Envisagez d’installer la dernière version des pilotes SQL Server OLE DB et ODBC au lieu d’utiliser les pilotes SQL Server natifs fournis avec Windows. Non seulement les nouveaux pilotes sont plus rapides, mais ils prennent en charge de nouvelles fonctionnalités dans Azure SQL que les pilotes précédents ne prennent pas. Vous pouvez installer les pilotes sur chaque ordinateur sur lequel la base de données convertie est utilisée. Pour plus d’informations, consultez Pilote Microsoft OLE DB 18 pour SQL Server et Pilote Microsoft ODBC 17 pour SQL Server.

Après avoir migré les tables Access, vous pouvez lier les tables dans SQL Server, qui héberge désormais vos données. La liaison directe à partir d’Access vous offre également un moyen plus simple d’afficher vos données plutôt que d’utiliser les outils de gestion plus complexes de SQL Server. Vous pouvez interroger et modifier des données liées en fonction des autorisations définies par votre administrateur de base de données SQL Server.

Remarque Si vous créez un DSN ODBC lorsque vous liez votre base de données SQL Server pendant le processus de liaison, créez le même DSN sur toutes les machines qui utilisent la nouvelle application ou utilisez par programme le chaîne de connexion stocké dans le fichier DSN.

Pour plus d’informations, voir Lier ou importer des données à partir d’une base de données Azure SQL Server et Importer ou lier des données dans une base de données SQL Server.

Conseil N’oubliez pas d’utiliser le Gestionnaire de tables liées dans Access pour actualiser et lier à nouveau les tables. Pour plus d’informations, voir Gérer les tables liées.

Tester et réviser

Les sections suivantes décrivent les problèmes courants que vous pouvez rencontrer lors de la migration et la façon de les gérer.

Requêtes

Seules les requêtes Select sont converties ; les autres requêtes ne le sont pas, notamment les requêtes de sélection qui acceptent des paramètres. Certaines requêtes peuvent ne pas être entièrement converties et SSMA signale des erreurs de requête pendant le processus de conversion. Vous pouvez modifier manuellement les objets qui ne sont pas convertis à l’aide de la syntaxe T-SQL. Les erreurs de syntaxe peuvent également nécessiter la conversion manuelle de fonctions et de types de données spécifiques à Access en fonctions et types de données spécifiques à SQL Server. Si vous souhaitez avoir plus d’informations à ce sujet, consultez Comparaison d’Access SQL avec SQL Server TSQL.

Types de données

Access et SQL Server ont des types de données similaires, mais gardez à l’esprit les problèmes potentiels suivants.

Grand nombre Le type de données Grand nombre stocke une valeur numérique non monétaire et est compatible avec le type de données SQL bigint. Vous pouvez utiliser ce type de données pour calculer efficacement de grands nombres, mais il nécessite l’utilisation du format de fichier de base de données .accdb Access 16 (16.0.7812 ou version ultérieure) et fonctionne mieux avec la version 64 bits d’Access. Pour plus d’informations, voir Utilisation du type de données Grand nombre et Choisir entre les versions 64 bits et 32 bits d’Office.

Oui/Non Par défaut, une colonne Access Oui/Non est convertie en champ de bits SQL Server. Pour éviter le verrouillage d’enregistrement, vérifiez que le champ de bits est défini de manière à interdire les valeurs NULL. DANS SSMA, vous pouvez sélectionner la colonne bit pour définir la propriété Allow Nulls (Autoriser les valeurs NULL) sur NO. Dans TSQL, utilisez les instructions CREATE TABLE ou ALTER TABLE .

Date et heure Plusieurs considérations de date et d’heure sont à prendre en compte :

Si le niveau de compatibilité de la base de données est 130 (SQL Server 2016) ou supérieur et qu’une table liée contient une ou plusieurs colonnes DateTime ou DateTime2, la table peut renvoyer le message #deleted dans les résultats. Pour plus d’informations, voir La table liée Access pour SQL-Server base de données renvoie #deleted.
Utilisez le type de données Date/Heure d’Access pour mapper au type de données Date/Heure. Utilisez le type de données étendues Date/heure Access pour mapper le type de données datetime2 qui a une plage de dates et d’heures plus grande. Pour plus d’informations, consultez Utilisation du type de données étendues date/heure.
Lorsque vous interrogez des dates dans SQL Server, tenez compte de l’heure et de la date. Par exemple :
- DateOrdered Between 1/1/19 and 1/31/19 may not include all orders.
- DateOrdered between 1/1/19 00:00:00 AM And 1/31/19 11:59:59 PM comprend toutes les commandes.

Pièce jointe Le type de données Pièce jointe stocke un fichier dans la base de données Access. Dans SQL Server, vous avez plusieurs options à considérer. Vous pouvez extraire les fichiers de la base de données Access, puis envisager de stocker les liaisons vers les fichiers dans votre base de données SQL Server. Vous pouvez également utiliser FILESTREAM, FileTables ou un magasin d’objets blob distant (RBS) pour conserver les pièces jointes stockées dans la base de données de SQL Server.

Lien hypertexte Les tables Access comportent des colonnes de liens hypertexte que SQL Server ne prend pas en charge. Par défaut, ces colonnes sont converties en colonnes nvarchar(max) dans SQL Server, mais vous pouvez personnaliser le mappage pour choisir un type de données plus petit. Dans votre solution Access, vous pouvez toujours utiliser le comportement des liens hypertexte dans les formulaires et les états si vous attribuez la valeur true à la propriété Lien hypertexte du contrôle.

champ à plusieurs valeurs Le champ à plusieurs valeurs Access est converti en champ SQL Server sous la forme d’un champ ntext contenant l’ensemble délimité des valeurs. Étant donné que SQL Server ne prend pas en charge un type de données à plusieurs valeurs qui modélise une relation plusieurs-à-plusieurs, supplémentaires de conception et le travail de conversion peuvent être nécessaire.

Pour plus d’informations sur le mappage des types de données Access et SQL Server, voir Comparer les types de données.

Remarque Les champs à plusieurs valeurs ne sont pas convertis.

Pour plus d’informations, consultez Types de date et d’heure, Types de chaînes et binaires et Types numériques.

Visual Basic

Bien que VBA ne soit pas pris en charge par SQL Server, notez les problèmes possibles suivants :

Fonctions VBA dans les requêtes Les requêtes Access prennent en charge les fonctions VBA sur les données d’une colonne de requête. Toutefois, les requêtes Access qui utilisent des fonctions VBA ne peuvent pas être exécutées sur SQL Server, de sorte que toutes les données demandées sont transmises à Microsoft Access pour traitement. Dans la plupart des cas, ces requêtes doivent être converties en requêtes directes.

Fonctions définies par l’utilisateur dans les requêtes Les requêtes Microsoft Access prennent en charge l’utilisation de fonctions définies dans les modules VBA pour traiter les données qui leur sont transmises. Les requêtes peuvent être des requêtes autonomes, des instructions SQL dans des sources d’enregistrement de formulaire/état, des sources de données de zones de liste déroulante et de zones de liste sur des formulaires, des états et des champs de table, et des expressions de règle par défaut ou de validation. SQL Server ne peut pas exécuter ces fonctions définies par l’utilisateur. Vous devrez peut-être reconcevoir manuellement ces fonctions et les convertir en procédures stockées sur SQL Server.

Optimiser les performances

Le moyen le plus important d’optimiser les performances avec votre nouveau serveur SQL Server principal consiste de loin à déterminer quand utiliser des requêtes locales ou distantes. Lorsque vous migrez vos données vers SQL Server, vous passez également d’un serveur de fichiers à un modèle informatique de base de données client-serveur. Suivez ces instructions générales :

Exécutez de petites requêtes en lecture seule sur le client pour un accès plus rapide.
Exécutez de longues requêtes en lecture/écriture sur le serveur pour tirer parti de la plus grande puissance de traitement.
Minimisez le trafic réseau avec des filtres et l’agrégation pour transférer uniquement les données dont vous avez besoin.

Optimiser les performances du modèle de base de données client-serveur Pour plus d’informations, consultez Créer une requête directe.

Voici des instructions supplémentaires recommandées.

Put logic on the server Votre application peut également utiliser des vues, des fonctions définies par l’utilisateur, des procédures stockées, des champs calculés et des déclencheurs pour centraliser et partager la logique d’application, les règles et stratégies métier, les requêtes complexes, la validation des données et le code d’intégrité référentielle sur le serveur plutôt que sur le client. Demandez-vous si cette requête ou cette tâche peut être effectuée mieux et plus rapidement sur le serveur. Enfin, testez chaque requête pour garantir des performances optimales.

Utiliser des vues dans les formulaires et les états Dans Access, procédez comme suit :

Pour les formulaires, utilisez une vue SQL pour un formulaire en lecture seule et une vue indexée SQL pour un formulaire en lecture/écriture comme source d’enregistrement.
Pour les états, utilisez un mode SQL comme source d’enregistrement. Créez toutefois une vue distincte pour chaque rapport afin de pouvoir mettre à jour plus facilement un rapport spécifique, sans affecter les autres rapports.

Réduire le chargement de données dans un formulaire ou un état N’affichez pas les données tant que l’utilisateur ne les a pas demandées. Par exemple, laissez la propriété recordsource vide, faites en sorte que les utilisateurs sélectionnent un filtre sur votre formulaire, puis remplissez la propriété recordsource avec votre filtre. Vous pouvez également utiliser la clause where de DoCmd.OpenForm et DoCmd.OpenReport pour afficher le ou les enregistrements exacts dont l’utilisateur a besoin. Envisagez de désactiver la navigation dans les enregistrements.

Soyez prudent avec les requêtes hétérogènes Évitez d’exécuter une requête qui combine une table Access locale et SQL Server table liée, parfois appelée requête hybride. Ce type de requête nécessite toujours Access pour télécharger toutes les données de SQL Server sur l’ordinateur local, puis exécuter la requête, il ne l’exécute pas dans SQL Server.

Quand utiliser les tables locales Envisagez d’utiliser des tables locales pour les données qui changent rarement, comme la liste des états ou des provinces d’un pays ou d’une région. Les tables statiques sont souvent utilisées pour le filtrage et peuvent être plus performantes sur le serveur frontal Access.

Pour plus d’informations, voir l’Assistant Paramétrage du moteur de base de données, Utiliser l’Analyseur de performances pour optimiser une base de données Access et Optimiser les applications Microsoft Office Access liées à SQL Server.