Piloter une base Access par ActiveX Data Objects

[MichelXld]

bonjour

dans le fichier joint vous trouverez quelques exemples pour piloter une base Access depuis Excel à l'aide d'ActiveX Data Objects


Les exemples :

Lister les tables de la base Access

Lister les champs d'une table

Créer une nouvelle table dans la base Access
( utilisation des données de la Feuil1 pour l'exportation )

Afficher les données d'une table , dans une ListBox (1ere exemple )
afficher toutes les données d'une table choisie dans le Frame1

Afficher les données dans une ListBox , par jointure entre tables (2eme exemple )
Il y a 2 Tables dans la base Access :
La Table1 contient le nom des intervenants et leur numero de matricule
La table2 contient les heures d'astreinte par date et par numero matricule
L'objectif de cette macro est d'effectuer une jointure entre les 2 tables en affichant les heures d'astreinte par nom d'intervenant
Une option (ComboBox2) est proposée pour n'afficher que les interventions dont la durée est supérieure à x heures

Afficher la liste des MétaDonnées de la base Acces
(la procedure fonctionne aussi pour les classeurs Excel : interessant pour visualiser la structure d'un classeur fermé )
source :


testé avec Excel2002 , Access2002 et WinXP
necessite d'activer la reference Microsoft ActiveX Data Objects 2.0 Library
necessite d'activer la reference Microsoft ADO Ext. 2.5 for DDL and Security


Les exemples sont en grande partie issus et adaptés du classeur 'Sample.xls' fourni par Microsoft , et des démos de mon cher ami Michel_M , sur le pilotage des fichiers fermés

Lien supprimé

Lien supprimé

Lien supprimé

Lien supprimé

Lien supprimé

Lien supprimé


pour des problemes de taille de fichier , j'ai été obligé de créer 2 zip ( le second arrive dans le message suivant )
il faut ensuite regrouper les 2 fichiers dans un meme repertoire



bonne soirée
MichelXld [file name=ImportExport_Access_ActiveX_Data_Objects.zip size=42327]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/ImportExport_Access_ActiveX_Data_Objects.zip[/file]

 

[chris]

Reiloter une base Access par ActiveX Data Object

Bonjour
Désolée je n'avais pas vu. Ce fil est tellement riche que je ne l'avais pas relu assez attentivement.
Bonne journée.

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour aux ADO,

juste un petit mot pour signaler un exposé de l'école supinfo qui explique bien et simplement la technique ADO pour les gens qui débutent là dessus

Ce lien n'existe plus

A noter dans ce topo des définitions très intéressantes de drivers pour connexions à d'autres bases de données comme Oracle, Mysql, SQL server etc.

D'ailleurs ce site regorge d'exposés très pédagogiques réalisés par les élèves-ingénieurs: a voir, qualité ***

autre lien celui de l'ODBC mysql 'au cas où'
http://dev.mysql.com/downloads/connector/odbc/3.51.html

Amicalement,

Michel_M

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour à toutes et à tous

Michel , merci pour ces lien tres interessants



ci joint d'autres exemples de procedures


Comparer 2 colonnes dans des feuilles Excel différentes , et lister les données communes

La Feuil1 contient une colonne dont l'entete s'appelle numeroPeriode1 .La Feuil2 contient une colonne dont l'entete s'appelle numeroPeriode2
L'objectif est de comparer les 2 colonnes , puis de lister les données de la Feuil2 qui apparaissent aussi dans la Feuil1

Sub requeteControleDoublons()
Dim Source As ADODB.Connection
Dim Requete As ADODB.Recordset
Dim Fichier As String, xSQL As String
Dim i As Long

Fichier = 'C:\\maBase.xls'

Set Source = New ADODB.Connection
Source.Open 'Provider = Microsoft.Jet.OLEDB.4.0;' & _
'data source=' & Fichier & ';' & _
'extended properties=''Excel 8.0;HDR=Yes'''

xSQL = 'SELECT DISTINCTROW Feuil2$.NumeroPeriode2 ' & _
'FROM [Feuil2$] ' & _
'INNER JOIN [Feuil1$] ON Feuil2$.NumeroPeriode2 = Feuil1$.NumeroPeriode1'

Set Requete = New ADODB.Recordset
Set Requete = Source.Execute(xSQL)

If Requete.EOF Then
MsgBox 'Il n'y a pas de doublons'
Else
'MsgBox 'il y a des doublons .'
Range('A1').CopyFromRecordset Requete
End If

Requete.Close
Source.Close
End Sub

Vérifier si la table 'Table1' existe dans une base Access

Sub verifierSiTableAccessExiste()
Dim Cat As ADOX.Catalog
Dim Table As ADOX.Table
Dim Fichier As String, xConnect As String
Dim Cn As ADODB.Connection

Fichier = ('C:\\Documents and Settings\\michel\\dossier\\dataBase.mdb')

Set Cn = New Connection
With Cn
.Provider = 'Microsoft.Jet.OLEDB.4.0'
.Open Fichier
End With

Set Cat = CreateObject('ADOX.Catalog')
Set Cat.ActiveConnection = Cn

On Error Resume Next
Set Table = Cat.tables('Table1')
If Table Is Nothing Then
MsgBox 'La table n'existe pas .'
Else
MsgBox 'La table existe .'
End If

Set Cn = Nothing
Set Cat = Nothing
End Sub

Compter le nombre d'enregistrements total dans la table 'Table1'

Sub nombreEnregistrementsTotal_dansTable()
Dim Cn As New ADODB.Connection
Dim Rs As ADODB.Recordset
Dim maBase As String

maBase = 'C:\\Documents and Settings\\michel\\dossier\\general\\excel\\dataBase.mdb'
Cn.Open 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & maBase

Set Rs = Cn.Execute('SELECT COUNT(*)FROM Table1')
MsgBox Rs(0)
Cn.Close
Set Cn = Nothing
End Sub

Compter le nombre d'enregistrements conditionnels

(Exemple : le nombre d'enregistrements dont le champ 'nombreHeures' est superieur ou égal à 7)

Set Rs = Cn.Execute('SELECT COUNT(*)FROM Table1 WHERE nombreHeures >= ' & 7)

Importer un fichier texte par la methode ADO

Sub importFichierTexte_ADO()
Dim Rc As ADODB.Recordset
Dim cn As String, Chemin As String, Fichier As String
Dim i As Long

Chemin = 'C:\\Documents and Settings\\michel\\dossier\\general\\excel'
Fichier = 'monFichier.txt'

cn = 'Driver={Microsoft Text Driver (*.txt; *.csv)};' & _
'Dbq=' & Chemin & ';Extensions=asc,csv,tab,txt'

Set Rc = New ADODB.Recordset
Rc.Open Source:='SELECT * FROM ' & Fichier, ActiveConnection:=cn

If Not Rc.EOF Then
For i = 0 To Rc.Fields.Count - 1 'recuperation entetes
Cells(1, 1).Offset(0, i) = Rc.Fields(i).Name
Next
Range('A2').CopyFromRecordset Rc
End If

Rc.Close
End Sub

Comment piloter un fichier DBase (.dbf) depuis Excel

Sub piloterDBase_ajoutEnregistrement()
'necessite d'activer la reference Microsoft ActiveX Data Objects x.x Library
Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Chemin As String, Cible As String, laBase As String

Chemin = 'C:\\Documents and Settings\\michel\\dossier'
laBase = 'maBase.dbf'

Set Cn = New ADODB.Connection
Cn.Open _
'Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=' & _
Chemin & ';'

Cible = 'SELECT * FROM ' & laBase & ';'

Set Rs = New Recordset
Rs.Open Cible, Cn, adOpenKeyset, adLockOptimistic

With Rs
.AddNew
.Fields(0) = 'Texte'
.Fields(1) = CDate('2005-07-04')
.Fields(2) = 10001
.Fields(3) = 'un commentaire'
.Update
End With

Rs.Close
Cn.Close
End Sub

bonne journée
MichelXld

Message édité par: michelxld, à: 18/09/2005 12:27

 

[JeanMarie]

Bonjour Michel

Je voulais te dire Merci, pour ton fichier qui m'a permis en l'adpatant de réaliser pour le boulot une fonction de calcul de DI (calcul théorique de résistance d'acier en fonction d'une analyse chimique).

Cette petite fonction, va nous faire gagner du temps, et simplifier le travail du premier de Four.

Encore Merci Michel

@+Jean-Marie

 

[MichelXld]

bonsoir

cher Jean-Marie , merci pour ton message meme si je ne suis pas sur qu'il me soit adressé



voici quelques exemples supplémentaires

Une autre solution pour lister les utilisateurs connectés à une base Access

( la premiere methode est proposée dans le post du 14/07/2005 11:38 )

Sub listerConnectesBaseAccess_V02()
Const JET_SCHEMA_USERROSTER = '{947bb102-5d43-11d1-bdbf-00c04fb92675}'
Dim Cnn As New ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Fichier As String
Fichier = 'C:\\dataBase.mdb'
Cnn.Open 'Provider=Microsoft.Jet.OLEDB.4.0;' & _
Data Source= & Fichier & ';'
Set Rst = Cnn.openSchema(adSchemaProviderSpecific, , JET_SCHEMA_USERROSTER)
Debug.Print Rst.getString
Cnn.Close
End Sub

Effectuer une jointure entre 2 fichiers DBase (.dbf)

Le lien sur internet
http://www.excelforum.com/showthread.php?p=1344302&posted=1#post1344302





Gérer les apostrophes contenus dans les enregistrements , lors d'une requete

'l'apostrophe du mot à rechercher dans la table doit etre doublé
donneeCible = ''Saint Jean D''Angely''
Fichier = 'C:\\dataBase.mdb'

Set Conn = New ADODB.Connection
Conn.Open 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & Fichier & ';'
                
rSQL = 'SELECT CodePostal FROM Table1 WHERE NomVille='
  
Set rsT = New ADODB.Recordset
  
With rsT
.ActiveConnection = Conn
.Open rSQL & donneeCible, , adOpenStatic, adLockOptimistic, adCmdText
End With

Gérer des noms de tables et des noms de champs qui contiennent des espaces

Pour que les requetes fonctionnent , il faut encadrer les noms par des crochets

With Rs
.ActiveConnection = Cn
.Open 'SELECT * FROM [Prix prod]', , adOpenStatic, adLockOptimistic, adCmdText
End With

Le lien sur le forum XLD
Lien supprimé





Une autre solution pour exporter des images dans une base Access puis les réimporter dans un userForm Excel

(La premiere méthode est proposée dans le post du 14/07/2005 11:38 )

Cet nouvelle procédure utilise l'objet Stream (uniquement disponible à partir de la version ADO 2.5)

Le fichier zippé
Lien supprimé




bonne soirée
MichelXld

Message édité par: michelxld, à: 19/10/2005 21:18

 

[lapinou1]

Bonjour tout le monde !!!

Y'a bien quelque'un qui va pouvoir m'aider un peu je pense ;-)

Je vous explique mon soucis : je reprends en main un sytème qui a été crée sous Access et je me retrouve avec une des bases qui fait plus de 1Go sous Access 97. ALlant bientôt arriver à saturation de cette base il faut que je fasse quelque chose !!!
Le compactage m'a fait gagner 30 Mo environ donc rien du tout !

J'ai commencé par prendre les tables et vérifié les paramètres de chaque champs mais c'est extrèmement long...

Comment pourrais-je faire pour obtenir une liste des différents objets de la base avec leur taille respectives...ceci afin de m'orienter vers les objets les plus importants...

Merci beaucoup pour votre aide, je suis sur que vous allez me dépatouiller de tout ça :woohoo:

 

[michel_m]

Salut Lapinou, Michel

J'avais fait un truc pour mon 100° post avec Excel 2000 qui donne le dictionnaire d'une base de données Access.
Dans la PJ la macro est dans le module2: il faudra sans doute que tu l'adaptes;
Compte tenu du peu d'écho qu'avait eu cette petite démo, je n'ai pas continué et je n'ai pas programmer les clés primaires et étrangères des tables

A+
Michel_M [file name=admin_B2.zip size=44499]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/admin_B2.zip[/file]

 

[lapinou1]

Bonjour Michel, Michel,...

Ton outil est super intéressant mais je ne comprends pas comment faire pour créer le dictionnaire par rapport à une base existant sous G:\\...\\....mdb

Si tu ne me connais pas je suis plus que très faible en VBA, certains de le diront :woohoo: je vois déjà venir Thierry

En fait j'aimerais appliquer l'idée du dictionnaire sur une base existante et pourquoi pas qu'il m'indique également, la taille physique dans la base de chaque objet (surtout les tables)

Merci à tous

 

[MichelXld]

bonjour Lapinou1 , bonjour Michel


tu peux tester cette procedure pour afficher la taille et le nombre d'enregistrements dans toutes les tables d'une base Access


Sub dimensionDesTables_baseAcces()
'necessite d 'activer la reference Microsoft ActiveX Data Objects x.x Library
'necessite d'activer la reference Microsoft ADO Ext. x.x for DDL and Security
Dim Cat As ADOX.Catalog
Dim lesTables As ADOX.Tables
Dim laTable As ADOX.Table
Dim Fichier As String
Dim Cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim i As Long
Dim Resultat As Double

Fichier = 'C:\\\\\\\\MaBase_V01.mdb'

Set Cn = New Connection
With Cn
.Provider = 'Microsoft.Jet.OLEDB.4.0'
.Open Fichier
End With

Set Cat = CreateObject('ADOX.Catalog')
Set Cat.ActiveConnection = Cn
Set lesTables = Cat.Tables

For Each laTable In lesTables
Resultat = 0

If laTable.Type = 'TABLE' Then
Set Rst = New ADODB.Recordset
With Rst
.ActiveConnection = Cn
.Open 'SELECT * FROM ' & laTable.Name, , adOpenKeyset, adLockReadOnly
End With

Rst.MoveFirst

Do Until Rst.EOF
For i = 0 To Rst.Fields.Count - 1
Resultat = Resultat + Rst.Fields(i).ActualSize
Next i
Rst.MoveNext
Loop

Debug.Print 'table ' & laTable.Name _
& vbLf & Rst.RecordCount & ' enregistrements' _
& vbLf & Resultat & ' bytes' & vbLf
Set Rst = Nothing
End If

Next

Cn.Close
Set Cn = Nothing
Set Cat = Nothing
End Sub


sinon pour recuperer la structure d'une base Access , tu peux aussi consulter le premier classeur placé dans ce fil de discussion (ImportExport_Access_ActiveX_Data_Objects.zip) et testes le module 6 : 'ListerMetaDonnees_BaseAccess'



bonne journée
MichelXld

Message édité par: michelxld, à: 27/12/2005 21:59

 

[lapinou1]

Bonjour Michel, le fil, le forum...

J'aimerais juste savoir now comment activer la reference Microsoft ActiveX Data Objects x.x Library la reference Microsoft ADO Ext. x.x for DDL and Security stp ?

Le PC est sur office 97 encore, est-ce que cela sera possible ?

Bon we

 

[MichelXld]

bonsoir

pour activer les references :

dans l'editeur de macros
Menu Outils
References
coches les lignes
Microsoft ActiveX Data Objects x.x Library
et
Microsoft ADO Ext. x.x for DDL and Security
(x.x dépend de la version installée sur ton poste )
cliques sur OK pour valider


si la reference 'Microsoft ADO Ext. x.x for DDL and Security' est absente , tu peux tester cette autre version


Sub dimensionDesTables_baseAcces_V02()
'necessite d 'activer la reference Microsoft ActiveX Data Objects x.x Library
Dim Cnn As New ADODB.Connection
Dim Rst As ADODB.Recordset, Rs As ADODB.Recordset
Dim Fichier As String
Dim i As Long
Dim Resultat As Double

Set Cnn = New Connection
Fichier = 'C:\\\\\\\\MaBase_V01.mdb'

With Cnn
.Provider = 'Microsoft.Jet.OLEDB.4.0'
.Mode = adModeRead
.Open Fichier
End With

Set Rst = Cnn.OpenSchema(adSchemaTables)

While Not Rst.EOF

If Rst.Fields('TABLE_TYPE') = 'TABLE' Then
Resultat = 0
Set Rs = New ADODB.Recordset
With Rs
.ActiveConnection = Cnn
.Open 'SELECT * FROM ' & Rst.Fields('TABLE_NAME'), , adOpenKeyset, adLockReadOnly
End With

Rs.MoveFirst

Do Until Rs.EOF
For i = 0 To Rs.Fields.Count - 1
Resultat = Resultat + Rs.Fields(i).ActualSize
Next i
Rs.MoveNext
Loop

Debug.Print 'table : ' & Rst.Fields('TABLE_NAME') _
& vbLf & Rs.RecordCount & ' enregistrements' _
& vbLf & Resultat & ' bytes' & vbLf

Set Rs = Nothing
End If

Rst.MoveNext
Wend

Cnn.Close
Set Cnn = Nothing
Set Rst = Nothing
End Sub


bonne soiree
MichelXld

Message édité par: michelxld, à: 27/12/2005 21:58

 

[lapinou1]

Bonsoir Michel !

J'ai activé ces 2 références, lançé le code rien ne se passe...mais a priori il tourne bien.
Idem avec la V02.

J'ai peut-être oublié un truc ?

Bonne soirée aussi

 

[MichelXld]

bonjour Lapinou1

Les 2 exemples utilisent l'objet Debug qui renvoie les résultats dans la fenêtre Exécution de l'editeur de macros

si cette fenetre n'apparait pas dans l'editeur de macros :
Menu Affichage
Fenetre Execution (Ctrl + G )

sinon , tu peux faire un essai en remplaçant Debug.Print par MsgBox

MsgBox 'table : ' & Rst.Fields('TABLE_NAME') _
& vbLf & Rs.RecordCount & ' enregistrements' _
& vbLf & Resultat & ' octets'

ou renvoyer directement les resultats dans la feuille de calcul


bonne journée
MichelXld

 

[lapinou1]

ok merci Michel cela marche nickel ;-)

Mon fichier fait 163 000 octets environ et quand je fait l'analyse avec ton code j'a une table à 3392 octets et une autre à 960 octets. Je n'ai rien d'autres dans le fichier, c'est assez énorme l'écart que l'on a quand même !!!

Je testerais demain sur Office 97 avec la vrai base que je souhaite analysé.

Bon dimanche

 

[lapinou1]

Bonjour Michel, le fil, le forum,...

J'ai fait des essais sur la base que je souhaitais tester et j'ai une erreur de syntaxe sur la clause FROM

.Open 'SELECT * FROM ' & laTable.Name, , adOpenKeyset, adLockReadOnly

.
A priori cela vient des noms de tables de la base, à savoir que tout types de noms existe avec des _ des : mais 95% des tables se nomment 'T: x x x'.

Merci pour ton aide,
Bonne journée à tous