Discussion :

[Rémy.A]

Bonjour à tous,

Je viens vers vous car je suis débutant en VBA SQL et une aide serait la bienvenue.

Je souhaiterai ajouter, en VBA via Excel, un champs calculé dans ma base de donné ACCESS

En cherchant sur le forum j'ai trouvé ce code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CurrentProject.Connection.Execute "ALTER TABLE TABLE1 ADD COLUMN ESSAI3 DECIMAL(18,0)"

Puis je l'ai adapté dans mon code VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
33
34
35
36
37
 
 
Sub Ajouter_Champ()
 
'Déclaration de la variable de connexion
Dim cnx As ADODB.Connection
Dim rst As ADODB.Recordset
 
Dim CheminAccess As String
Dim ChoixTABLE As String
 
'Chemin de la base de données
CheminAccess = ActiveWorkbook.Path & "\EquipmentV4R3.accdb"
 
 
ChoixTABLE = "PLC_FDP"
 
'Instanciation des variables
Set cnx = New ADODB.Connection
Set rst = New ADODB.Recordset
 
'Définition du pilote de connexion
cnx.Provider = "Microsoft.ACE.OLEDB.12.0"
'Définition de la chaîne de connexion
cnx.ConnectionString = CheminAccess
'Ouverture de la base de données
cnx.Open
 
'Objet Recordset ajout d'un champs dans la table
rst.Open "ALTER TABLE PLC_FDP ADD COLUMN ESSAI4 DECIMAL(18,0)", cnx
 
'Fermeture recordset et connection
'rst.Close
Set rst = Nothing
cnx.Close
Set cnx = Nothing
End Sub

Le code fonctionne et ajoute bien le champ dans la BDD!!

Cependant, que veut dire "DECIMAL(18,0)" dans l'expression ci-dessous?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CurrentProject.Connection.Execute "ALTER TABLE TABLE1 ADD COLUMN ESSAI3 DECIMAL(18,0)"

Et comment faut-il faire pour que le champs que j'ajoute soit un champs calculé dont la formule serait par exemple Champs B divisé par Champs A en utilisant le code suivant?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
'Objet Recordset ajout d'un champs dans la table
rst.Open "ALTER TABLE PLC_FDP ADD COLUMN ESSAI4 DECIMAL(18,0)", cnx

Merci d'avance pour votre aide!!

[EricDgn]

Bonjour,

DECIMAL indique le type de champ qu'il faut ajouter. Googler un peu pour en apprendre plus.
Sur cette page, les différents types selon le logiciel, dont Access.
LONG = entier long, SINGLE = réel simple, etc. Un exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

rst.Open "ALTER TABLE PLC_FDP ADD COLUMN chSingle SINGLE, chLong LONG", cnx

Pour ce qui est d'ajouter un champ calculé, il faut commencer par l'ajouter et ensuite faire une requête de mise à jour qui fait le calcul, du genre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

rst.Open "UPDATE maTable SET champC = champA/champB;", cnx

mais à priori ce n'est pas une bonne idée d'ajouter un champ "calculé" vu que les données pour le calcul sont disponibles. C'est s'encombrer inutilement.
Bonne continuation.

[marot_r]

Bonjour.

Les champs calculés ne sont pas des calculs dans les champs (ce qui est fortement déconseillé).
C'est un nouveau type de données (Access 2007 et suivante de mémoire) où tu peux mettre une formule et Access va faire le calcul.
Attention ce n'est pas exactement comme une formule Excel mais proche.

D'après ce post, on ne peut pas ajouter de champ calculé en SQL mais avec du VBA c'est possible.

How to add a calculated column to Access via SQL
https://stackoverflow.com/questions/...access-via-sql

Ceci dit, généralement les tables des bases de données ont des structures fixes et il est rare d'avoir à les modifier.
Aussi on fait généralement les calculs dans des requêtes de sélection.

Pourquoi as-tu besoin de cela ?

A+

[Rémy.A]

Bonjour à tous,
Merci pour vos explications!

J'en ai besoin car je suis en train de faire un outil Excel afin de créer des schémas électriques sur AutoCAD.
J'ai fini mon outil sur Excel, qui fonctionne, mais la base de données est trop volumineuse pour Excel.... J'ai donc créer une base de donnée ACCESS et je dois la mettre à jour via mon outil.

J'ai réalisé une macro qui créer les champs lorsque je saisie une nouvelle entreprise dans l'outil Excel. Ce champs est un calcul de côtes dont j'ai besoin dans l'outil Excel. De plus j'aimerai qu'il ce créé automatiquement et non manuellement.


En créant ma base ACCESS j'apprends le SQL^^ je suis donc un bleu...

Voici ce que je veux faire en VBA pour le Champ en question:



Je vais voir le lien pour trouver une solution!

[Rémy.A]

Bonjour Marot r

J'ai lu le lien que tu as mis en réponse et il propose ce code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Public Sub CreateField()
 
    Dim DB As DAO.Database
    Dim TableDef As DAO.TableDef
    Dim Fld As DAO.Field2
 
    Set DB = CurrentDb()
    Set TableDef = DB.TableDefs("Table1")
 
    Set Fld = TableDef.CreateField("field3", dbDouble)
    Fld.Expression = "[field1] * [field2]"
    TableDef.Fields.Append Fld
 
    MsgBox "Added"
 
End Sub

Comment fait-on le code pour un connexion en AOD?

Car dans l'outil Excel j'ai créé un macro public pour se connecter à la base ACCESS et j'aimerais que mon code reste cohérent.
Voici la macro public:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
'Déclaration de la variable de connexion
Public cnx As ADODB.Connection
Public rst As ADODB.Recordset
Public CheminAccess As String
Public ChoixTABLE As String
 
Public Sub Connextion_ACCESS()
 
'Instanciation des variables
Set cnx = New ADODB.Connection
Set rst = New ADODB.Recordset
 
'Chemin de la base de données
CheminAccess = ActiveWorkbook.Path & "\EquipmentV4R3.accdb"
 
'Définition du pilote de connexion
cnx.Provider = "Microsoft.ACE.OLEDB.12.0"
'Définition de la chaîne de connexion
cnx.ConnectionString = CheminAccess
'Ouverture de la base de données
cnx.Open
 
End sub

Je ne comprend pas comment adapter le code vba en DAO vers AOD

Merci d'avance pour votre aide.

[marot_r]

Bonjour.

Désolé j'avais raté la partie "ADO"' (je ne m'en sers presque jamais).

Là je crois que tu ne peux simplement pas.

Ceci dit si j'ai bien compris ton calcul est en fait une constante (tu n'y reviens plus après la création) donc tu pourrait peut-être stocker le résultat et non la formule.
Ce n'est pas une solution habituellement recommandée car si jamais tu changes les termes du calcul, le résultat lui reste "gelé" et donc devient incohérent SANS AUCUNE alerte de Access.

Sinon, tu es pris à faire ton calcul dans une requête de sélection ce qui n'est pas non plus une mauvaise solution.

A+

[Rémy.A]

RE,

Désolé j'ai pas vu quand j'ai lu l'article dans le lien...

Il y avait comme commentaire :

"Bon point, c'est vrai. J'allais utiliser ADO plus tôt pour le faire, puis je l'ai fait avec DAO. Je vais modifier la réponse et supprimer la note concernant les références. - Zedfoxus 25 juillet 15 'à 4:16
Tu as raison! Je n'avais pas réalisé jusqu'à présent qu'ADO ou ADOX ne prend pas en charge l'ajout de colonne calculée. Merci pour l'illumination - zedfoxus 25 juil. 15 à 4:50"

Je suis arrivé à adapter le code et sa fonctionne comme je veux!!

La colonne s'ajoute bien et le format est comme souhaité!!

Voici mon code pour qui ça intéresse!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Sub Ouverture_ACCESS_DAO()
 
Dim oDBE As DAO.DBEngine
Dim DB As DAO.Database
Dim CheminAccess As String
Dim TableDef As DAO.TableDef
Dim Fld As DAO.Field2
 
CheminAccess = ActiveWorkbook.Path & "\EquipmentV4R3.accdb"
 
Set oDBE = New DBEngine
Set DB = oDBE.OpenDatabase(CheminAccess, True, False) ' ";PWD=test;")
 
    Set TableDef = DB.TableDefs("PLC_FDP")
 
    Set Fld = TableDef.CreateField("ESSAI5", dbDouble)
 
    Fld.Expression = "[ABB_DX_DESSIN_AutoCAD] / [RAPPORT_ECHELLE]"
    TableDef.Fields.Append Fld
 
    MsgBox "Added"
 
    DB.Close
Set DB = Nothing
Set oDBE = Nothing
 
End Sub

Merci à tous pour votre aide!!

[marot_r]

Bonjour.

Juste une note de précaution à propos des champs calculés :

Even more serious, the calculated results are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (through new records are edits are updated), so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results.

http://allenbrowne.com/casu-14.html

A+

[Rémy.A]

Bonjour,

Ah ok

Dans mon cas, le champ calculé aura toujours la même expression, le rapport d'échelle (champ RAPPORT_ECHELLE) aura toujours la même valeur, seul le champ ABB_DX_DESSIN_AutoCAD sera mis à jour (par VBA) si besoin.

Si je mets à jour le champ ABB_DX_DESSIN_AutoCAD, le résulta dans le champ calculé sera mis à jour correctement?

Calculated fields belong in queries, not tables.
Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.

Ask yourself, "Would it be wrong if my field contained a value different from the calculation?" If you answer Yes, you must not store the field. If the difference would be meaningful, you do need the field.

So, how do you get the calculated field if you do not store it in a table? Use a query:

Create a query based on your table.
Type your expression into the Field row of the query design grid:
Amount: [Quantity] * [UnitPrice]

Est-ce bien que j'utilise le champ calculé?
si j'ai bien compris, ce n'est pas comme ça que l'on travail dans les règles de l'arts.

Je peux laisser le champs calculé? par rapport au code ci-dessous, le champ calculé appartient à la table ou à la requête?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Sub Ouverture_ACCESS_DAO()
 
Dim oDBE As DAO.DBEngine
Dim DB As DAO.Database
Dim CheminAccess As String
Dim TableDef As DAO.TableDef
Dim Fld As DAO.Field2
 
CheminAccess = ActiveWorkbook.Path & "\EquipmentV4R3.accdb"
 
Set oDBE = New DBEngine
Set DB = oDBE.OpenDatabase(CheminAccess, True, False) ' ";PWD=test;")
 
    Set TableDef = DB.TableDefs("PLC_FDP")
 
    Set Fld = TableDef.CreateField("ESSAI5", dbDouble)
 
    Fld.Expression = "[ABB_DX_DESSIN_AutoCAD] / [RAPPORT_ECHELLE]"
    TableDef.Fields.Append Fld
 
    MsgBox "Added"
 
    DB.Close
Set DB = Nothing
Set oDBE = Nothing
 
End Sub

[marot_r]

Bonjour.

Si je mets à jour le champ ABB_DX_DESSIN_AutoCAD, le résulta dans le champ calculé sera mis à jour correctement?

Oui car tu ne changes pas la formule à postériori (sur des données existantes) donc ça va fonctionner.
Si tu venais à changer ta formule et que tu avais déjà des données, là il faudrait faire attention et lancer une requête de mise à jour (un peu stupide) qui assignerai la valeur des champs concernés à eux-mêmes ce qui forcerait le recalcul.

Est-ce bien que j'utilise le champ calculé?
si j'ai bien compris, ce n'est pas comme ça que l'on travail dans les règles de l'arts.

En fait avant on n'avait pas autre chose donc on n'avait pas le choix et c'est un peu contre la normalisation des données qui dit qu'un champ dans une table doit être totalement défini (ou quelque chose comme cela).
Personnellement je m'en sert pour faire des choses comme calculer la concaténation du Prénom et du Nom pour présenter des liste des sélections. Je m'en sers tellement souvent qu'avoir un champ calculé est plus pratique que de faire une requête qui va le calculer et me servir de cette requête.

Ce qui est aussi une légère violation des "règles" qui recommandent de toujours passer par une requête pour accéder aux données.

À mon avis il est important de connaître les règles car elles améliorent la qualité et la maintenabilité des applications.
De savoir pourquoi elles sont là et ensuite de décider après analyse si on les suit ou pas.
Parfois un peu de dénormalisation simplifie grandement la conception et la maintenance.

La première règle que l'on m'a appris quand j'ai commencé en programmation est qu'un bon programme c'est un programme qui répond aux besoins de l'utilisateur.
Sa qualité interne est importante (surtout si tu es pris à faire sa maintenance) mais son objectif premier est de servir le client

A+

[Rémy.A]

Bonjour marot_r

Merci pour toutes ces explications

J'ai fais plein de test, tout fonctionne sans aucunes erreurs

Merci à tous!!