Base Oracle et requêtes SQL sous VBA

Version imprimable

08/04/2014, 09h59
Crysta17

Base Oracle et requêtes SQL sous VBA

Bonjour tout le monde,

Je poste ce message dans l'espoir que quelqu'un puisse m'éclairer.

J'explique ma situation. Je dispose d'une base Oracle. J'ai importée une table de cette base sous Excel pour pouvoir y appliquer des requêtes. Seulement peut-on exécuter des ordres SQL en VBA si la table est affichée sous forme de tableau dans Excel et n'est donc pas dans une base de données ?

De plus, lors de mon importation de la base via Excel/Données externes, le bouton : "Ne rien importer et ne créer que la connexion" était grisé. Cela veut-il dire que je vais devoir importer toutes mes tables une à une sur plusieurs onglets ??

En fait, j'ai besoin d'exécuter des requêtes SQL sur cette base et de mettre les résultats sous des tableaux Excel afin d'en faire des graphiques. Est-ce possible ? Si oui, comment ?

Je connais le langage SQL mais je débute en VBA, et je ne m'en sors pas ! C'est pourquoi je vous remercie de vos réponses.

Crysta

2 pièce(s) jointe(s)

Bonjour,

Regarde ça fichier joint!

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test()
Dim Con As New ADODBRD
Dim Sql As String
Dim Rs As Object
Con.TYPEBASE = 2
Con.User = "MOI"
Con.PassWord = "12345"
If Con.OpenConnetion = False Then
MsgBox "La connexion à la base de données n'a pas réussi ", vbCritical
Else
Sql = "Select MyTable.* from MyTable Where [MyTable].[MyChamp]='toto';"
Set Rs = Con.OpenRecordSet(Sql)
If Rs.EOF Then ActiveSheet.Range("a1").CopyFromRecordset Rs
End If
End Sub

Code:

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
Private Connexion
Public TYPEBASE
Public BASE
Public Server
Public Fichier
Public User
Public PassWord
 
Private Function GenereCSTRING()
'Permet de générer le Cornec String
'1 - ODBC
'2 - ORACLE
'3 - ACCESS 2012
'4 - ACCESS 2000
'5 - ACCESS 97
'6 - SQL SERVER
'7 - SQL SERVER 2005 Express
'9 - SQLite
'10 - SQLite3
If Trim("" & Fichier) = "" Then Fichier = BASE
 
Select Case TYPEBASE
    Case 1
        GenereCSTRING = "Provider=MSDASQL.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Data Source=" & BASE
    Case 2
        GenereCSTRING = "Provider=OraOLEDB.Oracle.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Data Source=" & BASE
 
    Case 3
        GenereCSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";"
 
    Case 4
        GenereCSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier & ";Persist Security Info=False"
    Case 5
        GenereCSTRING = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & Fichier
    Case 6
            GenereCSTRING = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Fichier
           ' GenereCSTRING = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Fichier & ";user=" & User & ";Passwors=" & PassWord
    Case 7
        GenereCSTRING = "Provider=SQLOLEDB.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Initial Catalog=" & BASE & ";Data Source=" & Server
    Case 8
        GenereCSTRING = "Provider=SQLNCLI.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Initial Catalog=" & BASE & ";Data Source=" & Server
    Case 9
        GenereCSTRING = "Provider=OleSQLite.SQLiteSource.3; Data Source=" & Fichier
 
        GenereCSTRING = "Driver={SQLite ODBC (UTF-8) Driver};Database=" & Fichier & ";StepAPI=;Timeout="
    Case 10
        GenereCSTRING = "Driver={SQLite3 ODBC Driver};Database=" & Fichier & ";LongNames=0;Timeout=4000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    Case Else
        GenereCSTRING = "PAS ASSEZ DE PARAMETRES RENSEIGNES !!!"
 
 
End Select
''MsgBox GenereCSTRING
'Response.End
End Function
 
 
Public Function OpenConnetion() As Boolean
'Ouvre une connexion à  la base de données.
'Dim Fso As New Scripting.FileSystemObject
    OpenConnetion = False
    On Error Resume Next
    Dim ConnecString
 
     Dim NbErr
 
    Set Connexion = CreateObject("ADODB.Connection")
    Connexion.Open GenereCSTRING
'ConnecString
 
    If Err = 0 Then
 
        OpenConnetion = True
       Connexion.CommandTimeout = 14400
    Else
  MsgBox Err.Description
 
    End If
'    Debug.Print Err.Description
    Err.Clear
    On Error GoTo 0
End Function
 
 
Public Function CloseConnection()
'Referme la connexion
CloseConnection = False
On Error Resume Next
    Connexion.Close
    Set Connexion = Nothing
     If Err = 0 Then
        CloseConnection = True
    End If
    Err.Clear
    On Error GoTo 0
End Function
 
 
Public Function OpenRecordSet(Sql)
'Retourne un RecordeSet
On Error Resume Next
    Dim Rs
Dim NbErr
 
Err.Clear
If Connexion.State = 0 Then
    OpenConnetion
End If
'Debug.Print Sql 'Replace(Sql, "%", "*")
    Set OpenRecordSet = CreateObject("ADODB.Recordset")
 
   ' OpenRecordSet.LockType = adLockOptimistic
    ''MsgBox  adLockOptimistic & vbcrlf & Err.Description
    Debug.Print Sql
    OpenRecordSet.Open Sql, Connexion, 1, 3
 
    If Err Then
   MsgBox Err.Description
 
    NbErr = NbErr + 1
        If NbErr < 11 Then
 
            Set OpenRecordSet = Nothing
 
 
        End If
 
    End If
    Err.Clear
 
End Function
Public Function RetournConnection()
Set RetournConnection = Connexion
End Function
Public Function OpenRecordSetParametre(Sql, Param)
Dim Commande
Dim Params
Set Commande = CreateObject("ADODB.Command")
Dim MyParameter
Set MyParameter = CreateObject("ADODB.Parameter")
Set Commande.ActiveConnection = Connexion
Commande.CommandText = "select Requête2.* from Requête2;"
 Commande.CommandType = adCmdText
 
 Set MyParameter = Commande.CreateParameter("[NumJob]", adNumeric)
         MyParameter.Value = 10
Commande.Parameters.Append MyParameter
 
 
 
'aa.Parameters.Append("MyRef") = "243410M660"
Set Rs2 = Commande.Execute
 
End Function
Public Function CloseRecordSet(Rs)
On Error Resume Next
    Rs.Close
    Set CloseRecordSet = Nothing
End Function
Public Function Execute(Sql)
    Execute = False
    On Error Resume Next
    Dim NbErr
Reprise:
If Connexion.State = 0 Then
    OpenConnetion
End If
Debug.Print Sql
    Connexion.Execute Sql
    If Err = 0 Then
        Execute = True
 
 
 
 
'     Else
'    'MsgBox Err.Description
'         Err.Clear
'    NbErr = NbErr + 1
'    If NbErr < 11 Then
'
'        GoTo Reprise
'    End If
Else
    'MsgBox Err.Description
    End If
 
    Err.Clear
 
End Function

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub tesGrapht()
Dim Plage As Range
Dim Graph As New ClsGraph
Set Plage = Range("A1:C12")
Graph.Delete Sheets("Graph1"), "Graph1"
Graph.Nouveau Sheets("Graph1"), "Graph1"
Graph.Style ExlConeCol
Graph.Source Plage, xlRows
Graph.SeriesCollection "=Recap!$C$64:$CP$64"
Graph.SeriesCollection_Caption "Mois", msoElementPrimaryCategoryAxisTitleAdjacentToAxis
Graph.SeriesCollection_Caption "ETPs", msoElementPrimaryValueAxisTitleRotated
Graph.SeriesCollection_Caption "Plan de charge Technique groupé par famille", msoElementChartTitleAboveChart
Graph.Position 0, 0
Graph.Taille 550, 700
Graph.SeriesCollection_Visible
Graph.SaveAs_Image Sheets("Graph1"), "Graph1", ActiveWorkbook.Path & "\" & "graphe.gif"
 
End Sub

Code:

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
Dim Shap As Shape
Enum Style
    ExlColumnClustered = xlColumnClustered
    ExlColumnStacked = xlColumnStacked
    ExlColumnStacked100 = xlColumnStacked100
    Exl3DColumnClustered = xl3DColumnClustered
    Exl3DColumnStacked = xl3DColumnStacked
    Exl3DColumnStacked100 = xl3DColumnStacked100
    Exl3DColumn = xl3DColumn
    ExlCylinderColClustered = xlCylinderColClustered
    ExlCylinderColStacked = xlCylinderColStacked
    ExlCylinderColStacked100 = xlCylinderColStacked100
    ExlCylinderCol = xlCylinderCol
    ExlConeColClustered = xlConeColClustered
    ExlConeColStacked = xlConeColStacked
    ExlConeColStacked100 = xlConeColStacked100
    ExlConeCol = xlConeCol
    ExlPyramidColClustered = xlPyramidColClustered
    ExlPyramidColStacked = xlPyramidColStacked
    ExlPyramidColStacked100 = xlPyramidColStacked100
    ExlPyramidCol = xlPyramidCol
    ExlLine = xlLine
    ExlLineStacked = xlLineStacked
    ExlLineStacked100 = xlLineStacked100
    ExlLineMarkers = xlLineMarkers
    ExlLineMarkersStacked = xlLineMarkersStacked
    ExlLineMarkersStacked100 = xlLineMarkersStacked100
    Exl3DLine = xl3DLine
    ExlPie = xlPie
    ExlPieExploded = xlPieExploded
    ExlPieOfPie = xlPieOfPie
    ExlBarOfPie = xlBarOfPie
    Exl3DPie = xl3DPie
    Exl3DPieExplodede = xl3DPieExploded
    ExlBarClustered = xlBarClustered
    ExlBarStacked = xlBarStacked
    ExlBarStacked100 = xlBarStacked100
    Exl3DBarClustered = xl3DBarClustered
    Exl3DBarStacked = xl3DBarStacked
    Exl3DBarStacked100 = xl3DBarStacked100
    ExlCylinderBarClustered = xlCylinderBarClustered
    ExlCylinderBarStacked = xlCylinderBarStacked
    ExlCylinderBarStacked100 = xlCylinderBarStacked100
    ExlConeBarClustered = xlConeBarClustered
    ExlConeBarStacked = xlConeBarStacked
    ExlConeBarStacked100 = xlConeBarStacked100
    ExlPyramidBarClustered = xlPyramidBarClustered
    ExlPyramidBarStacked = xlPyramidBarStacked
    ExlPyramidBarStacked100 = xlPyramidBarStacked100
    ExlArea = xlArea
    ExlAreaStacked = xlAreaStacked
    ExlAreaStacked100 = xlAreaStacked100
    Exl3DArea = xl3DArea
    Exl3DAreaStacked = xl3DAreaStacked
    Exl3DAreaStacked100 = xl3DAreaStacked100
    ExlXYScatter = xlXYScatter
    ExlXYScatterSmooth = xlXYScatterSmooth
    ExlXYScatterSmoothNoMarkers = xlXYScatterSmoothNoMarkers
    ExlXYScatterLines = xlXYScatterLines
    ExlXYScatterLinesNoMarkers = xlXYScatterLinesNoMarkers
    ExlDoughnut = xlDoughnut
    ExlDoughnutExploded = xlDoughnutExploded
    ExlBubble = xlBubble
    ExlBubble3DEffect = xlBubble3DEffect
    ExlRadar = xlRadar
    ExlRadarMarkers = xlRadarMarkers
    ExlRadarFilled = xlRadarFilled
    ExlSurface = xlSurface
    ExlSurfaceWireframe = xlSurfaceWireframe
    ExlSurfaceTopView = xlSurfaceTopView
    ExlSurfaceTopViewWireframe = xlSurfaceTopViewWireframe
End Enum
 
Public Sub SaveAs_Image(Feuille As Worksheet, Non As String, fichier As String)
For Each MyObject In Feuille.Shapes
    If MyObject.Name = Non Then
        MyObject.Chart.Export Filename:=fichier, FilterName:="GIF"
        Exit Sub
    End If
Next
End Sub
Public Sub Delete(Feuille As Worksheet, Non As String)
For Each MyObject In Feuille.Shapes
    If MyObject.Name = Non Then MyObject.Delete: Exit Sub
Next
End Sub
Public Sub Nouveau(Feuille As Worksheet, Non As String)
Set Shap = Feuille.Shapes.AddChart
Shap.Name = Non
End Sub
Public Sub Source(MyRange As Range, Orientation As Long)
 Shap.Chart.SetSourceData MyRange, Orientation
End Sub
Public Sub Style(MyStyle As Style)
 Shap.Chart.ChartType = MyStyle
End Sub
Public Sub SeriesCollection_Caption(Caption As String, Element As MsoChartElementType)
Dim e
  Shap.Chart.SetElement Element
  Select Case Element
            Case 2
                Shap.Chart.ChartTitle.Text = Caption
            Case 301
                Shap.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = Caption
            Case 309
                Shap.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = Caption
  End Select
End Sub
Public Sub SeriesCollection(V)
 Shap.Chart.SeriesCollection(1).XValues = V
End Sub
Public Sub SeriesCollection_Visible()
 
 Shap.Chart.SeriesCollection(1).ApplyDataLabels
End Sub
Public Sub Taille(Hauteur As Integer, Largeur As Integer)
 Shap.Height = Hauteur
 Shap.Width = Largeur
End Sub
Public Sub Position(X As Integer, Y As Integer)
 Shap.Top = X
 Shap.Left = Y
End Sub

08/04/2014, 11h42
Crysta17

Merci beaucoup !

J'ai bien l'impression que c'est ce dont j'ai besoin, mais après adaptation à ma base...

'ORA-12560 : TNS : Erreur d'adaptateur de protocole'

Une idée ? :?

Un collègue m'a indiqué que je devais récupérer l'adresse IP du serveur où est stockée ma base de données afin de pouvoir m'y connecter.
Est-ce nécessaire ?

ce code je l'ai créé au fils du temps en fonction de mes besoin, il es possible que la version du driver oracle que tu utilise ne soit pas la même il faut regarder dans le module de classe ADODBRD la fonction GenereCSTRING i le dirver "Provider=OraOLEDB.Oracle.1!

Code:

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
38
39
40
41
42
43
'Permet de générer le Cornec String
'1 - ODBC
'2 - ORACLE
'3 - ACCESS 2012
'4 - ACCESS 2000
'5 - ACCESS 97
'6 - SQL SERVER
'7 - SQL SERVER 2005 Express
'9 - SQLite
'10 - SQLite3
If Trim("" & Fichier) = "" Then Fichier = BASE
 
Select Case TYPEBASE
    Case 1
        GenereCSTRING = "Provider=MSDASQL.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Data Source=" & BASE
    Case 2
        GenereCSTRING = "Provider=OraOLEDB.Oracle.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Data Source=" & BASE
 
    Case 3
        GenereCSTRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fichier & ";"
 
    Case 4
        GenereCSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Fichier & ";Persist Security Info=False"
    Case 5
        GenereCSTRING = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & Fichier
    Case 6
            GenereCSTRING = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Fichier
           ' GenereCSTRING = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Fichier & ";user=" & User & ";Passwors=" & PassWord
    Case 7
        GenereCSTRING = "Provider=SQLOLEDB.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Initial Catalog=" & BASE & ";Data Source=" & Server
    Case 8
        GenereCSTRING = "Provider=SQLNCLI.1;Password=" & PassWord & ";Persist Security Info=True;User ID=" & User & ";Initial Catalog=" & BASE & ";Data Source=" & Server
    Case 9
        GenereCSTRING = "Provider=OleSQLite.SQLiteSource.3; Data Source=" & Fichier
 
        GenereCSTRING = "Driver={SQLite ODBC (UTF-8) Driver};Database=" & Fichier & ";StepAPI=;Timeout="
    Case 10
        GenereCSTRING = "Driver={SQLite3 ODBC Driver};Database=" & Fichier & ";LongNames=0;Timeout=4000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    Case Else
        GenereCSTRING = "PAS ASSEZ DE PARAMETRES RENSEIGNES !!!"
 
 
End Select

Citation:

Envoyé par Crysta17

Un collègue m'a indiqué que je devais récupérer l'adresse IP du serveur où est stockée ma base de données afin de pouvoir m'y connecter.
Est-ce nécessaire ?

avidement soit par le nom de la machine soit son IP
mais j'avoue avoir oubillé ce ditail qui n'en est pas un

Code:

con.BASE="MonIp"

08/04/2014, 12h03
Crysta17

A force de poser des questions à tout le monde, je vais finir par y arriver !! :P

Pourriez-vous m'indiquer où est-ce que je peux voir le provider que j'utilise ?
Je suis débutante, donc je ne sais pas du tout...

Merci
08/04/2014, 12h22
Invité

Dans Win Xp panneau de configuration->Administration->ODBC là on trouve tous le drivers.
dans win7 et plus ????
si non mon amis google
"http://www.boitasite.com/tutoriels/pdf/VBA/ADO%20definition.pdf"
"http://www.prosygma.com/iishelp/iis/htm/asp/iiwadata.htm"
"http://www.netalya.com/fr/Lobjet_Connexion.asp"
08/04/2014, 14h14
Crysta17

1 pièce(s) jointe(s)

Re,

Pièce jointe 142410

Devrait-il se trouver là ?...
08/04/2014, 14h23
Invité

Je ne voie pas de pilote pour Oracle, Que du SQL serveur; de plus tu devrais disposer mais je ne sais plus où d'un fichier que Oracle utilise pour connaitre tes droits Base(Serveur) lecture écriture création....
08/04/2014, 14h27
Crysta17

C'est bien ce qui m'inquiète !

Ce pilote Oracle doit être téléchargé en plus du logiciel ? Ou devrait-il être déjà installé ?

Normalement je devrais disposer de tous les droits car il s'agit d'un serveur virtuel (pour ne pas que je casse tout, ils ont bien fait !:mrgreen: )
08/04/2014, 14h57
Invité

là c'est du ressort de l'administrateur en espérant que pour la petite histoire ce n'est pas toi :help:
tu devrais pouvoir le télécharger directement chez Oracle profites en pour récupérer Oracle SQL développeur qui lui dispose peut-être déjà le driver???? en tous les cas Oracle SQL développeur pourra s'avérer utile.
08/04/2014, 15h22
Crysta17

Bon, je vais continuer de creuser...
En vain j'en ai bien l'impression !!

Je te remercie pour ton aide et ton indulgence !

Je possède déjà oracle SQL developer.
S'agit-il de ODBC.net ?
08/04/2014, 15h41
Invité

à bientôt sur le canal developpez.net, pour de nouvelles aventures
18/04/2014, 09h10
pyfux

Couche cliente Oracle

Bonjour,

Il me semble que vous êtes sur un OS Win 7 x64.

Je vous conseille la lecture de cet article (pour commencer)
http://blogs.msdn.com/b/support_sql_...s-64-bits.aspx
et de voir la version Oracle cible...

Microsoft OLE DB Provider for Oracle (fichier msdaora.dll)… Le provider OLEDB pour Oracle développé par Microsoft. Il faut noter que ce provider est en mode “deprecated” – et n'est supporté que contre des serveurs Oracles 7 et 8 (cf INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider sur http://support.microsoft.com/kb/244661)

Courage
18/04/2014, 11h53
Oliv-
Bonjour,
J'avais justement un besoin similaire voici ce qui marche chez moi.
Code:

1 2 Case 13 GenereCSTRING = "Provider=MSDAORA.1;User ID=" & User & ";Data Source=" & Server & ";Password=" & PassWord
et dans le module
Code:

1 2 Con.TYPEBASE = 13 Con.Server = "SERVEUR/SERVICENAME"
je dois juste modifier quelque chose car le CopyFromRecordset ne focntionne pas mais je peux parcourir le recordset
18/04/2014, 12h25
Invité

Bonjour Oliv,

Ravis de voir que ma classe fait de émules :D