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]

 

[MichelXld]

rebonjour

...et la base Access zippée...


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

 

[_Thierry]

Bonjour Michel, le Forum

Ton travail semble très interressant, je l'ai téléchargé et je regarderai du bureau car sous la main je n'ai pas Access sur mon PC de 'vadrouillages net' lol !

Mais sinon merci à toi de faire partager tes découvertes et le fruit de ton travail, c'est aussi ça 'l'esprit XLD', et par contre je trouve que soit c'est dû à la nouvelle présentation, soit les intervenants n'ont pas (ou plus) le même état d'esprit que dans le Forum XLD 'Première Génération', mais il est bien triste qu'après un travail comme ça tu n'aies pas encore eu un commentaire d'encouragement.

Je pense aussi que c'était dimanche et probablement pas mal ont raté cette Démo, comme la mienne d'ailleurs Lien supprimé, qui, malgré 154 visites ,'a engendré que 2 commentaires dans le Fil... (snif)

Je ne manquerai pas de repasser par ici, car les manipulations ADO sont trop rares sur ce Forum, après Michel_M, maintenant MichelXLD les ADOSpécialists d'XLD !!!

Bonne Journée et Semaine
@+Thierry

Message édité par: _Thierry, à: 21/03/2005 08:04

 

[Abel]

Bonjour MichelXLD, _Thierry,

Oh, bé, hé, ho !

Thierry, laisse nous le temps d'arriver !

Bon, alors on avait Barbatruc. Qu'est-ce qu'on va pouvoir trouver maintenant ?

Je travaille rarement avec Access mais je pense que ce truc venu d'ailleurs (je ne l'ai pas encore épluché) va pouvoir bien me (nous) dépatouiller dans les échanges Excel/Access.

Merci Michel.

Abel.

 

[MichelXld]

bonjour @+Thierry ,bonjour Abel

merci pour vos messages

Thierry , effectivement ta demo vaut vraiment le détour et c'est vraiment dommage qu'il n'y ai pas eu plus de réaction !
en regardant ton classeur cela me fait penser que j'ai oublié d'ajouter des entetes aux colonnes de ma ListBox ...ce sera pour la V02


concernant 'l'état d'esprit' je me suis fait la meme reflexion suite à ton message au sujet de la durabilité du moteur de recherche

Lien supprimé

comme le forum XLD et un lieu de partage de connaissance , le soucis que tu évoques aurait dû interpeller le plus grand nombre



je vous souhaites un bon début de semaine
MichelXld

 

[michel_m]

Re Bonjour Michel

Je réponds à notre dialogue de ce matin sur un autre fil; merci, il y a des tas de trucs qui m'ont hachement intéressé comme la création d'une table Access, ce après un premier parcours de fin de semaine.

Actuellement, j'ai plutôt tendance à retourner sur DAO comme le préconise les big sachems d'Access come Hervé (?) Inisian -ses bouquins sont très bien faits (site self-access)_ et Walter Stucker (? orthographe ?)

2 pb pour le fun que je n'ai pas su résoudre: faire une jointure avec 2 base de données - ou 2 classeurs_ de même qu'une jointure avec 3 tables- pb posé il y a qq jours sur xld.

Faut dire que depuis 3 mois, j'ai 'la t^te dans le guidon' dans mon nouveau boulot et que, ma foi, les petits casse-têtes...

Amicalement

Michel

 

[MichelXld]

bonjour @+Thierry , Abel et Michel_M

Michel , ton interrogation sur les jointures entre 2 bases de données et aussi entre 3 tables m'interesses . n'hésites pas à faire part de tes découvertes à ce sujet .


vous trouverez ci joint quelques exemples supplémentaires concernant la gestion des bases Access , mais aussi quelques exemples sur les classeurs Excel fermés
(toutes les procédures ont été testées avec Excel2002 , Access2002 & WinXP )
necessite d'activer la reference Microsoft ActiveX Data Objects x.x Library


Tranférer les données d'un onglet dans un fichier texte , utilisation de la méthode GetString

Sub excelVersFichierTexte()
Dim Rs As New ADODB.Recordset
Dim Fichier As String, Feuille As String
Dim xConnect As String, xSql As String

Fichier = 'C:\\Documents and Settings\\michel\\monClasseur.xls'
Feuille = 'Feuil1'

xConnect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & Fichier & ';' & _
'Extended Properties=Excel 8.0;'

xSql = 'SELECT * FROM [' & Feuille & '$];'

Set Rs = New ADODB.Recordset
Rs.Open xSql, xConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

Open 'C:\\Documents and Settings\\michel\\essai.txt' For Output As #1

Do Until Rs.EOF
Print #1, Rs.GetString(, 600, ';', vbCrLf, ''Â'Â'); 'dans cet exemple le séparareur est ';' (point virgule)
Loop
Close #1
End Sub

Additionner les tableaux de plusieurs classeurs fermés

Le fichier zippé
Lien supprimé





Vérifier dans une base Access , si une valeur existe dans le champ 'NumeroRemorque' d'une table 'Remorque'

Sub controleValeurTable() 
Dim Conn As ADODB.Connection 
Dim rsT As ADODB.Recordset 
Dim Fichier As String, rSQL As String 
Dim Valeur As Integer 

Fichier = 'C:\\maBase.mdb' 
Valeur = InputBox('Saisir le numero à rechercher dans la table'Â'Â') 

Set Conn = New ADODB.Connection 
Conn.Open 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & _ 
   Fichier & ';' 

rSQL = 'SELECT Remorque.NumeroRemorque' & _ 
' FROM Remorque' & _ 
' WHERE NumeroRemorque=' 
  
Set rsT = New ADODB.Recordset 
  
With rsT 
.ActiveConnection = Conn 
.Open rSQL & Valeur, , adOpenStatic, adLockOptimistic, adCmdText 
End With 

If rsT.EOF Then 
MsgBox 'Le numéro ' & Valeur & ' n'existe pas dans la table Remorque . ' 
Else 
MsgBox 'Le numéro ' & Valeur & ' existe la table Remorque . ' 
End If 

rsT.Close 
Conn.Close 
End Sub

Lister les éléments du champ 'Matricule' d'une table nommée 'Table2' , sans afficher de doublons

Sub listeElementsChamp_SansDoublon()
Dim Conn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim Fichier As String, rSQL As String

Fichier = ThisWorkbook.Path & '\\MaBase_V01.mdb'
Set Conn = New ADODB.Connection
Conn.Open 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & _
   Fichier & ';'

rSQL = 'SELECT DISTINCT Matricule' & _
' FROM Table2 '
Set rsT = New ADODB.Recordset
 
With rsT
.ActiveConnection = Conn
.Open rSQL, , adOpenStatic, adLockOptimistic, adCmdText
End With

If rsT.EOF Then
rsT.Close
Conn.Close
Exit Sub
End If
    
Feuil1.Range('A1'Â'Â').CopyFromRecordset rsT
rsT.Close
Conn.Close
End Sub

Extraire la valeur Maxi ( ou Mini) dans le champ 'nbHeuresAstreinte' de la Table2 , d'une base Access

Sub ExtraireValeurMaxTableAccess()
Dim cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Fichier As String
Fichier = ThisWorkbook.Path & '\\MaBase_V01.mdb'
Set cn = New ADODB.Connection
cn.Open 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & _
Fichier & ';'
Set Rs = New ADODB.Recordset
With Rs
.ActiveConnection = cn
.Open 'SELECT max(NbHeuresAstreinte) FROM Table2', , adOpenStatic, adLockOptimistic, adCmdText
'.Open 'SELECT min(NbHeuresAstreinte) FROM Table2', , adOpenStatic, adLockOptimistic, adCmdText
End With
MsgBox Rs(0)
Rs.Close
Set Rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Dans une base Access , lister les éléments du champ 'Matricule' de la 'Table1' qui n'existent pas dans le champ 'Matricule' de la 'Table2'

Sub listeElementsChamp_InexistantsDans2emeTable()
Dim Conn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim Fichier As String, rSQL As String

Fichier = 'C:\\MaBase_V01.mdb'
Set Conn = New ADODB.Connection
Conn.Open 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & _
   Fichier & ';'

rSQL = 'SELECT Table1.Matricule,Table1.Nom' & _
' FROM Table1 WHERE Table1.Matricule NOT IN  (SELECT Table2.Matricule' & _
' FROM Table2 )'

Set rsT = New ADODB.Recordset
 
With rsT
.ActiveConnection = Conn
.Open rSQL, , adOpenStatic, adLockOptimistic, adCmdText
End With

If rsT.EOF Then
rsT.Close
Conn.Close
Exit Sub
End If
    
Feuil1.Range('A1'Â'Â').CopyFromRecordset rsT
rsT.Close
Conn.Close
End Sub

bon week end
MichelXld

Message édité par: MichelXld, à: 02/04/2005 19:53

Message édité par: MichelXld, à: 02/04/2005 19:57

 

[dg62]

Bonjour MichelXLD, Thierry, le forum


Michel, je viens de découvrir ta démo sur excel et Access

Très beau travail !

Cela va me servir car je developpe une appli avec excel et access en source de données.

Tu as posté ta démo un dimanche soir et le Lundi je suis pas trop disponible donc je l'ai râtée.

Encore merci.

 

[ChTi160]

Reiloter une base Access par ActiveX Data Object

Salut 'MichelXLD
re dg62
tout comme @+Thiery tu fais parti des indispensables et même incontournables de ce forum
même si moi j'ai énormément de mal à comprendre ou simplement à assimilé ce code qui t'est apparemment familier
mais J'apprends énormément de tes procèdures
donc merci
Amicalement
Jean Marie

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour Michel,Thierry,Jean-marie, Abel, dg62

Félicitations Michel comme d'hab !

Getstring et tes demos sur les recherches de valeur (sql & valeur, fallait oser !) sont super !

J'espère que tu archives tout çà sur le wiki même si, héélas, on ne le voit plus sur le nouveau XLd

Beaucoup de boulot jusqu'à début mai où j'essaierai de résoudre les 2 pb cités + haut ;j'ai aussi une démo de thierry- les étiquettes- à adapter pour une assoce du coin...

Bon dimanche j'espère ensoleillé pour toi. ici en Ardèche c'est OK

Amicalement

Michel

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour à tous

voici la suite .... qui traite toujours du pilotage des bases de données Access et aussi des classeurs Excel fermés




Récupérer les informations d'un classeur corrompu

Le lien sur le forum XLD
Lien supprimé

une autre possibilité sans macro ( à partir d'Excel2000 ?)

menu Fichier
Ouvrir
Sélectionnez le classeur qui a un probleme dans la boite de dialogue
cliquez sur la droite du bouton 'Ouvrir' , en bas dans la boite de dialogue
Sélectionnez l'option 'Ouvrir et réparer'



Créer une nouvelle base Access

Sub creerNouvelleBaseDeDonnees()
'activer la reference microsoft ADO ext x.x for DLL and Security
Dim Cat As ADOX.Catalog
Set Cat = CreateObject('ADOX.Catalog'Â'Â')
Cat.Create _
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\maNouvelleBase.mdb'
End Sub

Ecrire dans un classeur fermé , en insérant les données à la suite des valeurs existantes

Le lien sur le forum XLD
Lien supprimé
Le fichier zippé
Lien supprimé



Afficher la deuxieme valeur la plus elevee du champ ''NbHeuresAstreinte' de la table 'Table2'

Sub deuxiemeValeurPlusElevee()
'Afficher la 2eme valeur la plus élevée dans
'le champ 'NbHeuresAstreinte' de la table 'Table2'
Dim Conn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim Fichier As String, rSQL As String

Fichier = ThisWorkbook.Path & '\\MaBase_V01.mdb'

Set Conn = New ADODB.Connection
Conn.Open 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & _
   Fichier & ';'
                
rSQL = 'SELECT MAX(NbHeuresAstreinte) AS NbHeuresAstreinte FROM Table2' & _
' WHERE(NbHeuresAstreinte <(SELECT MAX(NbHeuresAstreinte) AS NbHeuresAstreinte FROM Table2))'

Set rsT = New ADODB.Recordset
 
With rsT
.ActiveConnection = Conn
.Open rSQL, , adOpenStatic, adLockOptimistic, adCmdText
End With
MsgBox rsT.Fields(0).Value
rsT.Close
Conn.Close
End Sub

Compacter une base Access

Sub CompacterBaseAccess()
'necessite d'activer la reference Microsoft Jet and Replication Objects 2.6 Library
Dim bdCompact As String, Fichier As String
Dim Source As String, Destination As String
Dim jtEng As JRO.jetEngine
    
Fichier = 'C:\\Documents and Settings\\michel\\MaBase_V01.mdb' 'la base existante
bdCompact = 'C:\\Documents and Settings\\michel\\maBaseCompactee.mdb' 'la nouvelle base créée
    
Source = 'Data Source=' & Fichier
Destination = 'Data Source=' & bdCompact & ';' & 'Jet OLEDB:Encrypt Database=True'

Set jtEng = New JRO.jetEngine
jtEng.CompactDatabase Source, Destination
    
Set jtEng = Nothing
End Sub

Ecrire dans une cellule spécifique d'un classeur fermé
( exemple dans la cellule A1 de la Feuil1 )

Sub exportDonneeDansCellule()
Dim Cn As ADODB.Connection
Dim Cd As ADODB.Command
Dim Rst As ADODB.Recordset
Dim Fichier As String

Fichier = 'C:\\MDB\\monClasseur.xls' 'adapter le chemin des fichiers fermés

Set Cn = New ADODB.Connection
Cn.Open 'Provider=Microsoft.Jet.OLEDB.4.0;' & _
           'Data Source=' & Fichier & ';' & _
           'Extended Properties=''Excel 8.0;HDR=No;'';'

Set Cd = New ADODB.Command
Cd.ActiveConnection = Cn

Cd.CommandText = 'SELECT * from `Feuil1$A1:A1`'

Set Rst = New ADODB.Recordset
Rst.Open Cd, , adOpenKeyset, adLockOptimistic

Rst(0).Value = ' donnée test'
Rst.Update
  
Cn.Close

Set Cn = Nothing
Set Cd = Nothing
Set Rst = Nothing
End Sub

Importer les données d'une base Access dans une plage de cellules nommées discontinues
pour que cela fonctionne , le nombre de cellules dans la plage de cellules nommées ('DayRange') doit etre identique au nombre de données extrait de la table ('maTable')

Sub ImportDonneesAccess_Dans_Cellules_Discontinues()
Dim cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Fichier As String, TableName As String
Dim Cell As Range
Dim i As Byte

Fichier = ThisWorkbook.Path & '\\MaBase_V01.mdb'
TableName = 'maTable'

Set cn = New ADODB.Connection
cn.Open 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' & _
   Fichier & ';'
Set Rs = New ADODB.Recordset
    
With Rs
.ActiveConnection = cn
.Open 'SELECT * FROM ' & TableName, , adOpenStatic, adLockOptimistic, adCmdText
End With

For Each Cell In Range('TargetName'Â'Â')
Cell = Rs.Fields(i).Value
i = i + 1
Next

Rs.Close
Set Rs = Nothing
cn.Close
Set cn = Nothing
End Sub

bon dimanche
MichelXld

Message édité par: MichelXld, à: 15/05/2005 07:09

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour Michel et le Forum

Sacré boulot que tu donnes à XLD; félicitations et merci; je vais regarder d'un peu + près tout ça...

J'en profite pour te remercier encore sur une de tes demos sur les éléments du PC (je n'ai plus le nom du fichier en tête) car tu m'as fait découvrir WMI: ca ma paru beaucoup moins rébarbatif que les API
et le lien que fournit mdsn France vers développez.com est très explicatif.

mais je diverge du thème....

Bon Dimanche

Michel

 

[ChTi160]

Reiloter une base Access par ActiveX Data Object

Salut MichelXld,michel_m
bonjour le Fil
merci une fois encore pour toutes ces procèdure, qui nous permettent une fois encore d'avancer et d'avoir sur ce forum de nouvelles références à disposition
bon Dimanche

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour Michel, Jean Marie Didier et le forum


Dans la continuité des démos de mon pote MichelXLD, j'ai fouiné la création de bases

J'enfonce peut-être une porte ouverte mais ON PEUT AINSI CREER UNE BASE DE DONNEES ACCESS SANS AVOIR MS ACCESS INSTALLE. J'ai essayé sur un vieux bouzin n'ayant pas Access et ca a marché

J'ai donc créé une base avec ADOX avec clé primaire en 'numeroauto' et des colonnes typées (nombre et texte)

Les essais ont été effectués sur une bécane 'normale' (1,7Ghz, 512 Mo) avec 100000 enregistrement et une importation sur Excel de 50000 Lignes réalisée en 12 secondes.

La 2° étape, quand j'aurai un peu de temps au boulot, consistera à créer des classeurs frontaux sur différents postes avec des permissions différentes (p.e.junior: lecture seule par paramètres de recordset plutôt que par le serveur, ce qui laisse tranquille l'administrateur réseau) et de voir les temps de réaction et d'accès partagé.

Cela peut intéresser des TPE (très petites entreprises) qui n'ont pas les moyens de se payer 15 ou 20 licences access.

A+ de vous lire

Michel (at work)



[file name=ado_creertableV4.zip size=22476]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/ado_creertableV4.zip[/file]

 

[ChTi160]

Reiloter une base Access par ActiveX Data Object

Salut Michel
moi je suis un bleu et je comprends pas grand chose à ces procèdures, mais je suis comme un gamin et je me dit que peut être un jour je comprendrai et donc je pourrai utiliser, j'ai déjà testé et ça marche du feu de dieu
merci Michel

Message édité par: ChTi160, à: 04/08/2005 14:18