Modifier la structure d’une table liée en VBA
Comment modifier la structure d’une table liée en VBA ?
Dans l’article Modifier la taille d’un champ en VBA, il était question de modifier la structure d’une table de la base de données, en exécutant une instruction SQL ALTER TABLE
à partir de VBA. Mais ça ne marche pas si la table est liée (elle appartient à une base dorsale, en réseau par exemple). Comment faire dans ce cas ?
Principe
En fait, on va faire comme dans l’article précédent, mais en ouvrant la base de données dorsale (ou distante) via une instruction OpenDatabase
.
Se pose un nouveau problème : comment, dans la base frontale (celle qui contient les formulaires, notamment), savoir où se situe la base dorsale ? C’est tout simple : une table X de la base en cours est donnée par CurrentDb.TableDefs("X")
. Et les objets TableDef
ont une propriété Connect
qui donne le chemin de la base d’origine (ou ""
si la table n’est pas liée).
Le code
Pour simplifier les choses, voici une procédure toute faite (à recopier dans un module standard, comme d’habitude :)) :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
' --- ' EXECUTION D'UNE INSTRUCTION SQL DDL SUR UNE TABLE LIEE ' --- ' Sub ModifierTableLiee( _ ByVal strTable As String, _ ByVal strSQL As String) ' Lire le chemin de la table liée Dim strChemin As String strChemin = CurrentDb.TableDefs(strTable).Connect If strChemin = "" Then MsgBox "Cette table n'est pas liée." Exit Sub End If ' Extraire le ";DATABASE=" de départ strChemin = Mid(strChemin, Len(";DATABASE=") + 1) ' Ouvrir la base liée ' On suppose que la base n'est pas protégée, ' et qu'elle n'est pas en cours d'utilisation. Dim DistantDB As DAO.Database Set DistantDB = OpenDatabase(strChemin) ' Exécuter la commande SQL DistantDB.Execute strSQL ' On libère les objets DistantDB.Close Set DistantDB = Nothing End Sub |
Cette procédure reçoit 2 paramètres :
- le nom de la table liée, tel que vous le voyez dans la base frontale (ce nom peut être différent de celui de la table d’origine, située dans la base dorsale).
- l’instruction SQL (normalement :
ALTER TABLE ...
) à exécuter sur la table d’origine.
Pour plus de détails sur les références, consultez cette page.
Tester !
- Je dispose d’une base frontale A et d’une base dorsale B.
- Dans la base dorsale B, une table s’appelle
tblClients
. - Cette table est utilisée comme liaison dans la base A. Pour compliquer les choses, on suppose que le nom de la liaison est
tblClients2
.
Pour tester :
- Placez-vous dans la base frontale (où vous avez recopié le code donné plus haut).
- Faites apparaître la fenêtre Exécution (
CTRL
+G
). - Tapez quelque chose comme :
1 |
ModifierTableLiee "tblClients", "ALTER TABLE [tblClients2] ADD COLUMN [Email] TEXT(100)" |
Cette instruction ajoute un champ à la table tblClients2
. Ce champ s’appelle Email
, de type Texte, de taille 100.
tblClients
, mais qu’elle doit être appelée par son nom d’origine – tblClients2
– pour l’exécution de la chaîne SQL.
Merci à ce forum qui m’aide bien sur Access.
J’ai quand même galéré 2 heures pour obtenir ce que je voulais:
– j’ai une base Access séparée en 2: APPLI (front) et BASE (back)
– je voulais un bouton sur la base APPLI pour aller chercher la base BACK à l’endroit indiqué (clé USB, disque local, autre, …) et relier automatiquement toutes les tables
Je partage le code pour ceux que ça intéresse:
**************************************************************************
Public Sub ModifSourceDB()
Dim CheminBD As String
Dim ConnectBD As String
Dim ListeTables() As String
Dim ListeTemp As String
Dim i As Integer
Dim BDappli As DAO.Database
Dim BDbase As DAO.Database
Dim TableAppli As DAO.TableDef
Dim TableBase As DAO.TableDef
Dim Fd As Office.FileDialog
‘On demande le chemin de la base source
Set Fd = Application.FileDialog(msoFileDialogOpen)
With Fd
.AllowMultiSelect = False
.Title = « Chemin de la base BASE_ACCESS_PROD »
.ButtonName = « Sélectionner »
.InitialFileName = CurrentProject.Path
.InitialView = msoFileDialogViewDetails
.Filters.Clear
.Filters.Add « Bases de données Microsoft Access », « *.accdb »
End With
If Fd.Show Then
CheminBD = Fd.SelectedItems(1)
Else
Exit Sub
End If
Set Fd = Nothing
‘MsgBox CheminBD
If Not CheminBD Like « *BASE_ACCESS_PROD.accdb » Then
MsgBox « mauvais fichier!! »
Exit Sub
End If
‘ Définit la chaîne de connexion permettant la liaison des tables
ConnectBD = « MS Access;DATABASE= » & CheminBD
‘ Instancie l’objet Database de la base courante
Set BDappli = CurrentDb
‘ Instancie l’objet Database de la base protégée
Set BDbase = DBEngine.OpenDatabase(CheminBD, True, True, ConnectBD)
‘ Parcourt l’ensemble des tables de la base de données protégée
‘ et stocke leur nom
For Each TableBase In BDbase.TableDefs
‘ ne prend que les vraies tables
If TableBase.Attributes = 0 Then
‘MsgBox « nom: » & TableBase.Name & » attributs: » & TableBase.Attributes
ListeTemp = ListeTemp & TableBase.Name & « | »
End If
Next
‘ Ferme la base de données source (impératif pour la liaison)
BDbase.Close: Set BDbase = Nothing
‘ Parcourt le tableau de noms de tables
ListeTables = Split(Left(ListeTemp, Len(ListeTemp) – 1), « | »)
‘ Supprime et recrée chaque table liée
For i = 0 To UBound(ListeTables)
MsgBox « mise à jour de la table » & ListeTables(i)
BDappli.TableDefs.Delete ListeTables(i)
Set TableAppli = BDappli.CreateTableDef(ListeTables(i))
TableAppli.Connect = ConnectBD
TableAppli.SourceTableName = ListeTables(i)
BDappli.TableDefs.Append TableAppli
Next i
‘ Rafraichit la liste des tables
BDappli.TableDefs.Refresh
End Sub
*************************************************************************
Bruno-
Merci pour le code, ça pourra donner des idées à d’autres personnes !
ZoomBox > Bien reçu ! 🙂
Hervé> Ouf, je t’ai retrouvé, j’ai du attendre l’indexation Google pour chercher mon pseudo sur ce site car j’avais perdu la page. Bref.
C’est vrai que ça me semble compromi, tous ceux à qui j’ai demandé m’on dit de dupliquer la table en local… tant pis, je garderais cette option !
Et si, mon champs mémo contient des infos important qui sont des données de regroupement… (je ne suis pas du tout à l’origine de la table) c’est évidemment la seule donnée qui me permet de faire ce regroupement donc impossible de faire autrement. Un collègue m’a dit que ça ne l’étonné pas que j’ai à bidouiller pour modifier le type de donnée et importer la table en local…
Quoi qu’il en soit, un grand merci pour tes réponses, j’ai eu peur de ne pas te retrouver de ne pas avoir le temps de te le dire mais maintenant c’est fait! Merci encore, bonne journée !
ZoomBox > Dans ce cas, c’est effectivement plus compliqué. 🙂 Cet article ne s’applique qu’à des tables gérées sous Access, en serveur de fichiers (il faudrait que les tables sur le serveur soient stockées dans un MDB, ce qui n’est pas le cas). Si tu as une connexion ODBC en lecture seule, tu ne peux pas modifier la table source (j’imagine que c’est d’ailleurs pour des raisons de sécurité que l’accès est « read only »).
La seule manière, à froid comme ça, est effectivement de dupliquer la table pour la modifier localement. Ce que tu fais déjà…
Maintenant, on peut peut-être se poser la question autrement : pourquoi faut-il faire une jointure sur un champ Mémo ? A priori, si c’est un mémo, il ne contient pas d’informations structurées. Il n’y a donc pas d’intérêt d’y faire une jointure. (?)
Mais je n’ai pas assez d’infos sur le projet pour juger… 😉
Hervé > Je suis plutôt débutant en réseau et SQL donc je me suis peut être mal exprimé. Sous Accès j’ai une tablé liée qui vient de ODBC (donc la table est quelque part dans l’entreprise, sur un serveur). Je n’ai pas le(s) droit(s) de modifier cette table mais je l’exploite pour afficher des trucs via des requêtes dans mes formulaire Access. J’aimerais, via la liaison, recevoir la table, mais avec des champs d’un autre type. Je ne sais pas si c’est ce que tu as compris. (précision: Ma table n’est pas libre d’accès, j’ai un mot de passe « reader ».). Est bien celà que fait ton morceau de code ?
Merci.
ZoomBox > Qu’est-ce que tu entends par : « sans modifier sa source » ?
Si j’ai suivi, la procédure
ModifierTableLiee
a l’air de correspondre à ce que tu souhaites faire. Il devrait suffire de transmettre le bon «ALTER TABLE...
« . A tester sur une copie bien sûr avant ! 🙂Hervé > Ok, merci pour ta réponse rapide!
Sais-tu comment, dans Access, on peut changer le type de donnée d’un champ d’une table liée (sans modifier sa source)?
Comme si la liaison de la table contenait un « ALTER … Text ». Ca m’ennuie vraiment, pour l’instant, je me contente d’importer cette table dans une table locale puis de faire le changement de type de colonne, mais la table est immense et cette copie met beaucoup de temps à se faire. Encore merci d’avance !
ZoomBox > Dans l’exemple de cet article, c’est bien la base dorsale (sur le serveur) qui est modifiée. A supposer que l’application Access ait été scindée en 2 (une partie application/frontale, une partie données/dorsale). Voir la page Access en réseau pour les détails. On a donc 2 fichiers MDB, dont la partie tables + relations sur le réseau. C’est cette dernière qui va être modifiée (si elle n’est pas verrouillée sur le réseau).
Ouala !
Bonjour,
3 ans après, j’ai une petite question:
La table de base (celle herbergé sur un serveur) est-elle modifiée? Ou alors c’est uniquement la table reçue par Access qui l’est? Je suis en stage et j’ai un soucis de jointure sur un champ mémo, j’aimerais changer le format de ce champs en type Text pour pouvoir travailler convenablement. J’ai pensé à me faire une table temporaire (alimentée par un SELECT * from TableMere) mais c’est excessivement long (inutilisable). Merci d’avance, en esperant de pas jeter un pavé dans la marre mais que je sois entendu. BOnne journée.
Christian > La suite de la conversation ici 🙂
Au lieu de modifier » Text (100) » comment passer de text à hypertext ou numérique ou autre attribut ?
Merci pour votre réponse Christian