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]

Reiloter une base Access par ActiveX Data Object

bonjour Michel , bonjour Jean-Marie

Michel , Merci pour ton apport sur ce fil : ne te prive surtout pas d'ajouter de nouvelles infos

dans les messages précédents il y a un exemple pour créer une nouvelle base Access, mais je n'aurais pas imaginé que ça pouvait fonctionner sans qu'Access soit installé sur le poste : merci pour l'info

dès que possible ( sans doute ce WE ) ,j'ajouterai un lien vers ton classeur , dans la wiKiPage 6


bon apres midi
MichelXld

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour Michel

Ci joint version V5 un peu plus présentable pour le Wiki , c'est un honneur, Merci, et c'est sincère car ton wiki est un super manuel d'utilisation.

Le mystère est la durée de restitution des articles avant hier 62500 extractions en 22 sec, hier en 3sec, aujourd'hui en 33 secondes ???
peut-être dû à des RAM à prix discount qui équipent les bécanes au boulot....


bon WE ensoleillé

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

 

[chris]

Reiloter une base Access par ActiveX Data Object

Bonjour

Merci de ces contributions passionnantes.
Je n'ai pas encore eu le temps de les approfondir mais je les mets au frais pour cet été.

Je trouve géniale la possibilité de créer une base Access sans Access.
Savez vous si on peut exploiter la base au moyen de requêtes toujours sans le moteur ACCESS ?

En fait cela permettrait de se passer d'une run time si on ne souhaite pas se servir de l'interface Access et ouvre donc des horizons.

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour,

On peut toujours créer des requêtes à partir d'Excel bien sûr;

Pour créer des requêtes purement Access et sauvegardées en tant que telles, je suis plutôt pessimiste mais on ne sait jamais. D'après ce que j'ai compris, le catalogue ADOX, permet uniquement (...) de construire des tables; access, bien sûr, mais peut être aussi MySql (ta question de l'autre jour) avec ODBC : la doc Microsoft est comme par hasard assez discrète sur les bases non MS...

Pour le moment je vais essayer de créer des index sans doublons, des clés étrangères avec des intégrités référentielles: question de temps au boulot sans être dérangé...

Bien sur, le mieux est de construire les tables avec une bécane équipée Access et de la charger sur une non équipée !

Amicalement

Michel

 

[chris]

Reiloter une base Access par ActiveX Data Object

Re
Oui j'avais effectivement idée de créer les tables et requêtes depuis Access. Ce que je ne sais pas c'est si les requêtes SQL aboutissent sur un PC où le moteur Access n'est pas installé.
SQL utilise je crois ADO.

Si j'ai bonne mémoire je crois avoir exploité une base Access à partir d'Acrobat (version complète) sans avoir Access sur le PC mais il me semble qu'on n'utilisait pas SQL.

Je n'aurai sans doute pas le temps de ma pencher la-dessus avant mi-juillet mais après je fonce et je vous tiens au courant. Cela setait une alternative à mon exploration MYSQL.

@ +

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Re,

j'ai peut_être mal compris ta réponse.

A partir d'Excel tu peux utiliser effectivement ADO avec du SQL pour faire des requ^tes sur la base au format Access.

L'intérêt que je vois à tout ca, c'est de faire une base dorsale 'Access-maison'

Et des frontales -Excel.

L'alternative restant PHP-MYSQL

L'objectif étant de proposer une solution sans investissement logiciel avec la fiabilité et la capacité d'une SGBD

Amicalement

Michel

 

[chris]

Reiloter une base Access par ActiveX Data Object

Re
Nous sommes bien en phase.
Merci.
Come indiqué je me plonge dedans cet été.B) :silly:
@+

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour

ci joint d'autres exemples pour piloter les classeurs fermés et les Access


Ajouter un enregistrement dans une table Access

Sub exportDonnees_Excel_Vers_Access()
'ajouter un enregistrement dans une table Access
'necessite d'activer la reference Microsoft ActiveX Data Objects x.x Library
Dim Conn As New ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim maTable As String

maTable = 'Table1'

With Conn
.Provider = 'Microsoft.JET.OLEDB.4.0'
.Open 'C:\\Documents and Settings\\michel\\Excel\\MaBase_V01.mdb'
End With
        
With rsT
.ActiveConnection = Conn
.Open maTable, LockType:=adLockOptimistic
End With
    
With rsT
.AddNew
.Fields('Nom').Value = Range('A1')
.Fields('PrixUnit').Value = Range('B1')
.Fields('Matricule').Value = Range('C1')
.Update
End With
    
rsT.Close
Conn.Close
End Sub

Suppression conditionnelle d'enregistrements , dans une table Access

Sub suppressionEnregistrementTable()
Dim Cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Requete As String, maTable As String, Donnee As String
Dim Valeur As Integer

Set Cn = New ADODB.Connection
Set Rst = New ADODB.Recordset

Cn.Provider = 'Microsoft.Jet.Oledb.4.0'
Cn.ConnectionString = 'C:\\MaBase_V02.mdb'
Cn.Open

maTable = 'LesPoints'
'
'****pour des valeurs numeriques ****
'suppression des enregistrements si le champ 'nbPoints' est égal à 5 , dans la table 'lesPoints'
'
'Valeur = 5
'Requete = 'DELETE * FROM ' & maTable & ' WHERE NbPoints=' & Valeur
'
'**** pour des données texte ********
'suppression des lignes si le champ 'Nom' est égal à 'Nom03' , dans la table 'lesPoints'
Donnee = 'Nom03'
Requete = 'DELETE * FROM ' & maTable & ' WHERE [Nom]='' & Donnee & '''
'************************************
Cn.Execute Requete

Cn.Close
End Sub

dans le classeur joint , 4 autres exemples qui concernent les classeurs fermés Excel et aussi Access :

Boucler sur tous les classeurs fermés d'un répertoire , et importer les données de cellules discontinues

Importer dans la feuille active les données de tous les onglets d'un classeur fermé

Ajouter un nouvel onglet dans un classeur fermé , et y insérer des données

Transférer une Table Access dans un classeur fermé



bonne soiree
MichelXld [file name=ActionsDansClasseursFermes.zip size=48997]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/ActionsDansClasseursFermes.zip[/file]

Message édité par: MichelXld, à: 05/06/2005 21:28

 

[myDearFriend!]

Reiloter une base Access par ActiveX Data Object

Bonsoir le fil,

MichelXLD, que dire ?
Encore un fichier de référence à conserver bien au chaud sur son disque dur ! Je n'ai jamais été vraiment dingue de l'ADO, mais là, michel_m et toi, vous m'avez convaincu... c'est de la bombe !

Merci mille fois.
A très bientôt.

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour à tous,

Merci Michel; Je télécharge, engrange et regarderai à tête reposée: la semaine s'annonce dure au boulot mais..;

Amicalement

Michel at work

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour

Chers Didier et Michel , merci pour vos messages
ci joint de nouveaux exemples pour alimenter le sujet


Ajouter une nouvelle colonne dans la table 'LesPoints' d'une base Access

Sub AjoutColonne_TableAccessExistante()
Dim Cnn As New ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim NouvelleColonne As New ADOX.Column
Dim Rst As New Recordset
Dim Reponse As String, Fichier As String
    
On Error GoTo Fin
    
Fichier = 'C:\\MaBase_V02.mdb'
Cnn.Open 'Provider='Microsoft.Jet.OLEDB.4.0';' & _
'Data Source= '' & Fichier & '';'
    
Set Cat.ActiveConnection = Cnn
'paramétrage colonne
NouvelleColonne.Name = 'NouveauChamp'
NouvelleColonne.Type = adChar
NouvelleColonne.DefinedSize = 50
NouvelleColonne.Attributes = adColNullable
Cat.Tables('LesPoints').Columns.Append NouvelleColonne.Name, adWChar, 50
    
Rst.Open 'LesPoints', Cnn, adOpenKeyset, adLockOptimistic, adCmdTable
    
Fin:
Cnn.Close
Set Rst = Nothing
Set Cat = Nothing
Set NouvelleColonne = Nothing
Set Cnn = Nothing
End Sub

Effectuer un tri croissant dans la colonne 'Nom' d'une table Access

Sub Tri_Croissant_Champ_BaseAccess()
'effectuer un tri croissant dans la colonne 'Nom' de la table 'LesPoints'
Dim Cnn As New ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim indexTri As New ADOX.Index
Dim Rst As New ADODB.Recordset
Dim Fichier As String
    
On Error GoTo Fin

Fichier = 'C:\\MaBase_V03.mdb'
Cnn.Open 'Provider='Microsoft.Jet.OLEDB.4.0';' & _
'Data Source='' & Fichier & '';'

Set Cat.ActiveConnection = Cnn

With indexTri
.Columns.Append 'Nom'
.Columns('Nom').SortOrder = adSortAscending 'tri croissant
.Name = 'Ascending'
'.Columns('Nom').SortOrder = adSortDescending 'pour les tris décroissants
'.Name = 'Descending'
.IndexNulls = adIndexNullsAllow
End With

'ajout d'un index pour la table 'LesPoints'
'attention : renvoie une erreur si un index existe déja
Cat.Tables('LesPoints').Indexes.Append indexTri
    
Rst.Index = indexTri.Name
Rst.Open 'LesPoints', Cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
        
'import dansd la feuille Excel des données triées
Feuil1.Range('A1').CopyFromRecordset Rst

Cat.Tables('LesPoints').Indexes.Delete indexTri.Name 'suppression index
    
Fin:
Cnn.Close
Set Cat = Nothing
Set indexTri = Nothing
Set Rst = Nothing
Set Cnn = Nothing
End Sub

Créer un nouvel onglet dans un classeur fermé et y exporter les données de la feuille active

Sub Export_VersNouvelleFeuille_ClasseurExcelFerme()
'transférer la feuille 'devis' dans un nouvel onglet d'un classeur fermé
Dim oRS As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim maFeuille As String, prepaTable As String
Dim j As Integer, i As Integer

'nom(sans espace!) de la feuille Excel qui va etre créée dans le classeur fermé
maFeuille = 'ArchiveDevis002'

Set oConn = New ADODB.Connection
oConn.Open 'Provider=Microsoft.Jet.OLEDB.4.0;' & _
'Data Source=C:\\Classeur1_Fermé.xls;' & _
'Extended Properties=''Excel 8.0;HDR=NO;'''
    
For i = 1 To 10 'nombre de colonnes à transférer
'paramétrage entêtes de colonnes et types de données
prepaTable = prepaTable & 'Colonne' & i & ' Memo ,'  'adapter les types de données
Next i

prepaTable = Left(prepaTable, Len(prepaTable) - 1)
'creation nouvelle Feuille Excel
oConn.Execute 'create table ' & maFeuille & '(' & prepaTable & ')'

Set oRS = New ADODB.Recordset
oRS.Open 'Select * from ' & maFeuille, oConn, adOpenKeyset, adLockOptimistic
    
For j = 1 To 40 'nombre de lignes à transferer
oRS.AddNew
For i = 1 To 10 'nombre de colonnes à transférer
oRS.Fields(i - 1) = ActiveSheet.Cells(j, i)
Next i
RS.Update
Next j
    
oRS.Close
oConn.Close
End Sub

Modifier un champ dans une table Access
(Rechercher la valeur de la cellule A1 dans le champ 'Matricule' de la table 'maTable' , et modifier le champ 'leChamp' )

Sub test()
Dim Conn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim fld As ADODB.Field

Set Conn = New ADODB.Connection
With Conn
' Définition du fournisseur OleDB pour la connexion
.Provider = 'Microsoft.JET.OLEDB.4.0'
' Ouverture d'une connexion
.Open ThisWorkbook.Path & '\\MaBase_V01.mdb'
End With

Set rsT = New ADODB.Recordset
'table nommée 'maTable'
rsT.Open 'maTable', Conn, adOpenKeyset, adLockOptimistic

With rsT
.MoveFirst
'recherche la valeur de la cellule A1 dans champ 'Matricule'
.Find ('Matricule=' & Cells(1, 1))

'quand la valeur est trouvée , on modifie le champ 'leChamp'
.Fields('leChamp') = 'xxxx'
.Update
End With

Conn.Close
End Sub

Requête dans un classeur Excel fermé :
Additionner les valeurs d'un champ en intégrant plusieurs critères de filtration

Le lien sur le forum
Ce lien n'existe plus
Le fichier zippé
Lien supprimé





bon dimanche
MichelXld

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Bonjour Michel et le forum

OK Merci !

Dès que je sortirai d'autres langages (php, javascript, vba, sql... A force je me mélange les syntaxes et je dis des gros mots; vendredi j'ai mis des accolades dans un If de VBA !)
donc, disais je je ferai un tour dans ce ADOX qui est super puissant mais pas toujours facile et j'aurai certainement besoin de coups de main de ta part !

Bon dimanche

Michel

 

[Gael]

Reiloter une base Access par ActiveX Data Object

Bonjour Michel, bonjour le Forum,

C'est réellement un travail très intéressant et complet qui montre vraiment toutes les possibilités des requêtes externes avec VBA.

On peut déjà faire beaucoup de choses avec XL et MSQuery sans utiliser de code, et je trouve d'ailleurs qu'on ne s'en sert pas assez, mais tes exemples montrent qu'on peut aller encore beaucoup plus loin.

C'est également très sympa de partager non seulement tes connaissances mais le fruit de ton travail en le mettant à la disposition de tous d'autant plus que sa mise au point n'a pas dû être si évidente.

Merci beaucoup.

@+

Gael

 

[JJM]

Reiloter une base Access par ActiveX Data Object

Bonsoir MichelXLD, le Forum,

Je viens de découvrir ce fil par hasard et j'en reste bouche bée : ton exemple est un véritable cours. Je trouve cela formidable, de donner gracieusement un enseignement d'une telle qualité, pouvant en outre rendre d'immenses services.
Ce n'est plus du vba, c'est du VBA+ (voire ++).

 

[daniel]

un message de remerciement

En parcourant le forum ce matin, j'ai trouvé le commentaire ci-dessous , dans un post de MichelXld. Je ne sais pas si Thierry a raison ou non, mais j'en profite -car il est vrai que je ne rends pas toujours à César ce qui lui est dû...- pour sincèrement REMERCIER tous ceux qui ont, comme Thierry, Michel et bien d'autres, cette volonté et cette générosité de faire partager leur savoir.

Loin de moi l'idée de faire du lèche bottes, mais grâce aux multiples 'démos USF' de Thierry, j'ai réussi à bricoler quelques petites appli qui me servent réellement dans mon travail. RIEN n'aurait été possible sans ces exemples. Je leur suis d'autant plus reconnaissant que je ne compte plus le sites vendant des 'applications' Excel qui n'arrivent parfois pas à la cheville de ce que l'on trouve ici gratuitement !!

Voilà, c'était juste un message 'gratuit', sans rien demander (pour l'instant, lol), ça ne fait pas de mal...

Bonne journée, gens du forum !

Daniel

_Thierry écrit:

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<br><br>Message édité par: _Thierry, à: 21/03/2005 08:04