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 , Gael , JJM et Daniel

merci pour vos encouragements tres sympas

ci joint d'autres exemples



Récupérer des plages de cellules précises dans plusieurs classeurs fermés

une démo de @+Thierry

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

Une adaptation de michel_M pour les versions d'office antérieures
Le fichier zippé
Lien supprimé




Lister le nom des feuilles d'un classeur fermé
sans prendre en compte les plages nommées dans le classeur

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

Fichier = ThisWorkbook.Path & '\\classeurFerme.xls'

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

Set Cat = CreateObject('ADOX.Catalog')
Set Cn = CreateObject('ADODB.Connection')

Cn.Open xConnect
Set Cat.ActiveConnection = Cn

'boucle sur les feuilles & plages nommées(tables) du classeur fermé
For Each Feuille In Cat.tables
'filtrer pour ne pas prendre en compte les plages nommées
If Feuille.Name Like '*$*' Then MsgBox Feuille.Name
Next

Set Cn = Nothing
Set Cat = Nothing
End Sub

Lister les utilisateurs connectés à une base Access

Sub listerConnectesBaseAccess()
Dim Cible As String
Dim Fso As Object
Cible = 'J:\\maBase.ldb'

Set Fso = CreateObject('Scripting.FileSystemObject')
If Fso.FileExists(Cible) = False Then Exit Sub

Open Cible For Input As #1
Input #1, Cible
Close #1

MsgBox Cible
End Sub

Exporter des images dans une base Access

dans le classeur joint , une démo pour sélectionner une image sur le disque dur et l'exporter dans une base Access
Une option permet ensuite de lister les données de la base et d'afficher les images dans un UserForm




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

Message édité par: MichelXld, à: 14/07/2005 11:45

 

[_Thierry]

Reiloter une base Access par ActiveX Data Object

Bonsoir les Michels !!!, les intervenants de ce Fil, le Forum

Ce fil est vraiment une mine d'or !!!

Bravo Michel pour le imagesDansBaseAccess.zip c'est aussi Hachement bien si on veut faire une super interface UserForm !! (je n'aurai même pas pensé qu'on pouvait le faire lol)

Je tenais aussi à remercier Daniel, car son Post est très sympa et il a au moins de l'authenticité sincère !

Mais si vous saviez que moi aussi j'ai eu bien des surprises en surfant parfois en voyant des 'grosses m...' que des 'rapaces' osent essayer de vendre ! c'est carrément fou... Il y a vraiment des gens qui n'ont pas de scrupule et qui se croient les 'rois du monde', enfin l'important pour moi, et the 'Michel Gang', c'est que nous progressons aussi en vous fesant partager nos découvertes.

Enfin donc ce fil est monstrueusement interressant !

Merci à vous
@+Thierry

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour Michel , Abel , Didier , Jean-Marie , Chris , Gael , JJM , @+Thierry et Daniel

Thierry , encore merci ton super message qui ne va pas m'encourager à m'arreter ...;o)



Supprimer les enregistrements d'une table Access , si le champ 'Ville' est vide

Sub supprimerEnregistrements_SiChampVide()
Dim Cn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim Requete As String, maTable As String

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

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

maTable = 'Table1'

'supprime l'enregistrement si le champ 'Ville' est vide
Requete = 'DELETE * FROM ' & maTable & ' WHERE [Ville]IS NULL'
Cn.Execute Requete

Cn.Close
End Sub

Afficher la version MDAC du poste ( Microsoft Data Access Components )

Sub VersionMDAC()
Dim Cn As ADODB.Connection
Set Cn = CreateObject('ADODB.Connection')
MsgBox 'Version MDAC : ' & Cn.Version
Set Cn = Nothing
End Sub

Les limitations d'Excel , utilisé comme une base de données

il n'est pas possible de supprimer les lignes complétes dans un classeur fermé (enregistrements )
vous obtiendrez un message d'erreur 'La suppression des données dans un table attachée n'est pas géré par le pilote ISAM''
Vous pouvez uniquement vider les cellules

Vous ne pourrez pas supprimer les lignes vides qui contenaient les données supprimées et les requetes continueront d'afficher les enregistrements vides correspondant à ces lignes vides.

Il n'est pas possible de supprimer une cellule contenant une formule
Vous aurez un message d'erreur 'L'opération demandée n'est pas autorisée dans ce contexte'

Excel ne peut pas gérer les connections multiples et simultanées à un meme classeur

Les requetes répétées peuvent entrainer des problemes de mémoire disponible dans Excel
http://support.microsoft.com/kb/319998

Il n'est pas possible d'utiliser un classeur protégé par un mot de passe

Il n'est pas possible d'utiliser le classeur si la feuille contenant les données est protégée

La gestion des tables :
Les onglets ( les tables ) contiennent le symbole $ en fin de nom , ce qui n'est pas le cas des plages de cellules nommées ( pourtant aussi considérées comme des tables lors des requetes )
Par contre si vous avez ajouté un onglet dynamiquement dans un classeur fermé ( en utilisant par exemple 'Create Table' ou 'SELECT INTO' ) , 2 noms différents sont renvoyés pour cette nouvelle table : avec et sans $ . En fait si vous ouvrez le classeur manuellement vous constaterez que l'onglet est bien ajouté mais aussi une plage de cellules nommée , correspondant à la plage de données insérées dynamiquement ( voir insertion/nom/definir ) ar exemple =maNouvelleFeuille!$A$1:$C$1265
Nota :
Lors des requètes pour lister le nom des onglets d'un classeur fermé , par ADOX ou ADO(méthode OpenSchema) , les noms sont renvoyés par ordre alphabétique


Par défaut , le pilote ODBC analyse uniquement les 8 premieres lignes du classeur fermé pour déterminer le type de données dans chaque colonne.
Cela peut entrainer 2 types de problemes :

1. Dans certains cas particuliers , les données exportées vers un classeur fermé peuvent etre tronquées . Si , par exemple , les 8 premiers enregistrements d'un champ contiennent des données texte inférieur ou égal à 255 caractères , le champ sera considéré de type Texte . Si ensuite vous ajoutez des enregistrements de longueur plus importante ils seront tronqués .
http://support.microsoft.com/kb/189897/

2. Si vous voulez importer les informations d'une colonne qui contient à la fois des données numériques et texte , c'est le type majoritaire dans les 8 premiere lignes qui définira le type de données à récupérer : les autres données de la colonnes seront considérées comme NULL (vide)
Si la colonne contient 4 valeurs numériques et 4 valeurs texte , la requete renvoie 4 nombres et 4 valeurs NULL.
La seule solution consiste à activer l'option d'importation 'IMEX=1' ( exemple : 'extended properties=''Excel 8.0;IMEX=1''' ) . Les données numériques seront alors importées comme du texte .

Je n'ai pas vérifié le point suivant , mais l'aide MSDN indique :
Avertissement concernant la modification de données Excel à l'aide d'ADO : Lorsque vous insérez des données texte , la valeur de texte est précédée d'une apostrophe. Ceci peut provoquer des problèmes par la suite lors du travail avec les nouvelles données.



Copier une table Access dans une nouvelle feuille d'un classeur fermé

Sub tranfertTableAccess_Vers_ClasseurExcelFerme_V02()
'copier une table Access 'Table2'
'dans une nouvelle feuille d'un classeur fermé
Dim Cn As New ADODB.Connection
Dim nbEnr As Long
Dim maBase As String

maBase = ThisWorkbook.Path & '\\MaBase_V01.mdb'
    
Cn.Open 'Provider=Microsoft.Jet.OLEDB.4.0;' & _
'Data Source=' & maBase & ';'
Cn.Execute 'SELECT * INTO [Excel 8.0;' & _
'Database=' & ThisWorkbook.Path & '\\Classeur1_Fermé.xls].[MaNouvelleFeuille] FROM ' & _
'Table2', nbEnr

Cn.Close
MsgBox nbEnr & ' Enregistrements copiés .'
End Sub

bon apres midi
MichelXld

Message édité par: MichelXld, à: 24/07/2005 14:55

 

[michael]

Reiloter une base Access par ActiveX Data Object

Bonjour

Impressionnant le boulot que vous avez fait sur ce fil (exemples, explications) vraiment très bien.

Encore bravo à tous

 

[jeanpierre]

Reiloter une base Access par ActiveX Data Object

Bonsoir MichelXLD, le fil et le Forum,

Un Grand Merci. Déjà que tes interventions sont soignées....

De plus développer sur des thèmes très précis, comme tu le fais, c'est fantastique. Cela va devenir quelque chose d'encyclopédique, non, va devenir ne convient pas et il faut plutôt remplacer par : Encyclopédique, tout simplement....
Pour finir, et même si je n'emploie pas Access et qu'il m'est difficile de savourer comme il se doit, je te renouvelle mon GRAND MERCI.

Une bonne soirée vous tous.

Jean-Pierre

PS : Je viens d'envoyer le lien de ce fil à quelques personnes qui seront, sans doute, intéressées.

Message édité par: jeanpierre, à: 24/07/2005 18:47

 

[Yanis]

Reiloter une base Access par ActiveX Data Object

Bonjour MichelXLD,

 

[Yanis]

Reiloter une base Access par ActiveX Data Object

Bonjour MichelXLD,

Au hasard de mes recherches, je suis tombé nez à nez avec ce lien qui correspond exactement à ce qu'il me fallait pour solutionner un problème qui me tracassait !!!


Cependant, ma petite expérience a vite fait de me jouer des tours car lorsque j'ai voulu tirer profit du code concernant l'écriture dans un fichier fermé, je me suis confronter à un problème :unsure:


:ermm: En fait, le programme lancé bloque sur la ligne
'' Dim Cn As ADODB.Connection ''

En me diant qu'il ne reconnaît pas le type défini par l'utilisateur

Alors je ne comprends pas pourquoi ça ne marche pas alors que sur l'exemple que tu mets à disposition... Ca marche du tonnerrre :woohoo:

Mon incompression est grande inch:

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour Yanis

il faut que tu actives la reference Microsoft ActiveX Data Objects x.x Library
(x.x dépend de la version que tu utilises )

tu vas dans l'editeur de macro
Menu Outils
References
coches la ligne 'Microsoft ActiveX Data Objects x.x Library'
cliques sur OK pour valider


bon apres midi
MichelXld

Message édité par: michelxld, à: 11/08/2005 12:54

 

[Yanis]

Reiloter une base Access par ActiveX Data Object

HOURRRA ça mARCHE !!!

HOURRRRRRRRRRRRRA



C trop magnifique



MErci beaucoup pour tout ce travail que tu fais MichelXLD ainsi que tous ceux qui contribuent à faire vivre ce merveilleux forum où j'en apprends tous les jours !!!

 

[Yanis]

Reiloter une base Access par ActiveX Data Object

Oh rage Oh Désespoire, alors que je croyais mon objectif atteint !

Je suis boulversé ! Après avoir activer la référence adéquate je pensais que je n'aurais plus de pb mais malheureusement, les données que j'insère à la suite de mon tableau se trouvent à chaque fois décalées de 5 voir 10 lignes plus bas qu'il ne faudrait ... :unsure:

J'ai bien essayer de remédier à ça en utilisant

'' Range('A1').End(xlDown).OffSet(0,1) ''

Mais rien n'y fait . Pourtant sur ton exemple MichelXLD, l'ajout suit bien continuellement les premières lignes du tableau.

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour

ci joint quelques Demos et informations complémetaires


Publipostage ciblé Word Excel

Cette démo de michel_M permet d'effectuer un publipostage ciblé avec plusieurs options de filtre

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



Insérer des formules de liaison dans un classeur fermé

Une démo de michel_M

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



Information sur la propriété HDR , pour la connection à un classeur fermé

par exemple : 'Extended Properties=''Excel 8.0;HDR=Yes'''

si HDR =No , la premiere ligne est considérée comme un enregistrement
si HDR=Yes , la premiere ligne est considérée comme un entête



Supprimer une table dans une base Access

Sub supprimerTableAccess()
Dim Cn As New ADODB.Connection
Dim maBase As String

maBase = 'C:\\Documents and Settings\\michel\\dossier\\maBase.mdb'
Cn.Open 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & maBase
Cn.Execute 'DROP TABLE laTable'
Cn.Close
Set Cn = Nothing
End Sub

Requête filtrée sur des dates , des nombres ou du texte

pour du texte il faut une quote ''' de chaque coté de la donnée recherchée :

rSQL = 'SELECT * FROM [Feuil1$] WHERE [Article] ='' & Range('F3') & '''

pour une date ( il faut un dièse '#' de chaque coté de la donnée recherchée ):
de plus chez moi cela fonctionne uniquement au format mois/jour/annee

rSQL = 'SELECT * FROM [Feuil1$] WHERE [laDate] =#' & Format(Range('F3'), 'mm/dd/yyyy') & '#'

pour un nombre :

rSQL = 'SELECT * FROM [Feuil1$] WHERE [NumCommande] =' & Range('F3')

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



Filtrer une plage de valeurs à importer

(exemple : récupérer uniquement les valeurs entre 3 et 5) dans le champ 'numeroType' de la Table1
rSQL = 'SELECT * FROM Table1 WHERE numeroType BETWEEN 3 AND 5'



Une autre méthode pour ajouter un enregistrement dans une table Access

Sub ajoutEnregistrementTableAccess()
'source : K Dales
Dim Cn As ADODB.Connection
Dim xSQL As String

Set Cn = New ADODB.Connection
Cn.Open 'DRIVER={Microsoft Access Driver (*.mdb)}; ' & _
'DBQ=C:\\Documents and Settings\\michel\\dossier\\Database.mdb'

xSQL = 'INSERT INTO [Table1] ' _
& 'VALUES ('' & Range('A1').Value & '', '' & Range('A2').Value & '', '' _
& Range('A3').Value & '', '' & Range('A4').Value & '')'
Cn.Execute xSQL

Cn.Close
Set Cn = Nothing
End Sub

bonne journée
MichelXld


ps
Yanis , peux tu ouvrir un nouveau fil de discussion pour ta question

 

[Jam]

Reiloter une base Access par ActiveX Data Object

Salut à tous,

chris écrit:

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.

Je profite de mon passage sur le thread pour apporter une ch'tite réponse. à cette question et aux 2 qui suivent (MYSQL...).

La seule chose nécessaire pour exploiter les bdd est la présence du MDAC version 2.x (celui-ci est présent par défaut sur Win2K et Win XP). Donc pas de problème avec les bécanes 'récentes'. Dans le pire des cas il suffit de récupérer le MDAC (version 2.8 si je suis bien à jour) sur le site de Micro$oft à cette adresse Ce lien n'existe plus

Pour MYSQL, ça marche pareil. Il faut juste récupérer le driver MYSQL ODBC sur leur site, puis de pointer sur la base. Ensuite c'est tout pareil.

Pour la gestion des bdd, tout peux se faire via des requêtes SQL, il faut donc maîtriser celui-ci. Pour ceux que cela intéresse, 2 sites de référence à connaître (mais doit y en avoir d'autre):
http://sqlpro.developpez.com/ en français
et http://www.carlprothman.net/Default.aspx?tabid=81 pour tout connaître des paramètres de connection à TOUTES les bdd (MYSQL, Oracle, SQLServer...via ODBC, .NET...

Bonne journée à tous

Message édité par: jam, à: 25/08/2005 11:35

 

[michel_m]

Reiloter une base Access par ActiveX Data Object

Salut Jam, MichelXLD et les autres intervenants de ce fil,

Merci pour les liens indiqués: celui de Carl Prothman ouvre pas mal de possibilités et les articles de developpez.com sont toujours intéressants.

Donc, re-merci

Amicalement,

Michel_M

 

[chris]

Reiloter une base Access par ActiveX Data Object

Bonjour à MichelXLD, Jam et tout le forum

J'ai enfin trouvé un peu de temps pour exploiter cette super idée.
Voici quelques précisions par rapport à mes propres réalisations :

Il faut au minimum la version 2000 d'Excel.

Avec MDAC (merci à Jam de cette importante info) tout marche bien tant sur Win 98 que Me.

On peut lancer des requêtes stockées dans la base sous réserve que le code de ces requêtes n'utilise pas de fonctions Access ou stockées dans un module de la base. Dans mon cas j'ai trouvé un paliatif mais cela reste à creuser car certaines fonctions sont bien pratiques.

J'ai aussi trouvé un bout de code pour compacter et encrypter la base : je vous le livre ici
--------------------
(base étant le nom de la base utilisée défini en variable public)

Public Sub CompactAndEncrypt()
'Il faut ajouter la référence à JetEngine
'Compactage base

Dim base2 As String
Dim je As New jro.JetEngine

' S'assurer que le fichier de la base compactée n'existe pas déjà
base2 = 'tempo.mdb'

If Dir(base2) <> '' Then Kill base2

' Compacter et encrypter une version de la base

je.CompactDatabase 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & base, _
'Provider=Microsoft.Jet.OLEDB.4.0;' & 'Data Source=' & base2 & ';' & _
'Jet OLEDB:Encrypt Database=True'

Kill base
Name base2 As base

End Sub
------------------
Cela remplace en grande partie la runtime.
C'est vraiment super.

 

[MichelXld]

Reiloter une base Access par ActiveX Data Object

bonjour Chris , Michel et Jam

merci pour votre participation à ce fil

Chris , dans le post du 15/05/2005 07:07 , tu trouveras aussi une version (très proche) pour le compactage d'une base


bonne journée
MichelXld