Excel et mySQL (et BDD en général)

Version imprimable

02/03/2013, 02h12
ANOVA

Excel et mySQL (et BDD en général)

Bonjour à tous,

je maitrise pas trop mal le VBA, mais j'ai besoin de gérer des volumes de données importants et donc je voudrais utiliser une vraie base de données avec recours à SQL pour l'interroger (j'ai fait du SQL il y a quelques années en école d'ingé, donc je veux utiliser ça pas un logiciel presse bouton).

Access ne semble pas adapté : d'une part, je ne sais pas m'en servir, j'ai l'impression que c'est presse bouton sans possibilité de faire du SQL. D'autre part, j'ai cru comprendre qu'il n'est pas possible de faire de CREATE TABLE ce qui peut limiter pas mal de choses.

Du coup, j'ai pensé que la bonne solution (gratuite) pouvait être d'utiliser mySQL. J'ai cru lire qu'on pouvait faire quasiment tout pour quelque chose de gratuit (c'est vrai ça qu'on peut tout faire ou presque ?). J'ai donc installé ce SGBD.

Maintenant, ce que j'aimerais, c'est créer une base toute simple, n'importe quoi (une fois que j'aurai le code pour quelque chose de simple, je pourrais adapter quelque chose de compliqué) à partir de code VBA Excel. Et je suis un peu perdu entre ADODB, ADO, ADOX, Microsoft Jet...C'est quoi tout ça ? Que dois-je utiliser ? Quelle est la doc la plus pertinente pour mon problème ? J'ai trouvé pas mal de trucs, mais j'ai pas envie de lire 100 pages pour me rendre compte que c'est pas ça qu'il faut utiliser. C'est pourquoi, je fais appel à vous.

Je vous remercie de votre aide
Cordialement
ANOVA
02/03/2013, 07h54
tototiti2008

Bonjour Anova,

Citation:

j'ai l'impression que c'est presse bouton sans possibilité de faire du SQL

Non, on peut très bien faire du SQL sur Access, même à travers une connexion ADO, ça va quand même plus loin que le presse-bouton

Citation:

j'ai cru comprendre qu'il n'est pas possible de faire de CREATE TABLE ce qui peut limiter pas mal de choses

A ma connaissance, CREATE TABLE fonctionne tout à fait sur Access, jamais eu de soucis avec
Les limites d'Access se situent plutôt sur le volume potentiel des données, l'absence de procédure stockée en Transact SQL, ce genre de chose

Citation:

J'ai cru lire qu'on pouvait faire quasiment tout pour quelque chose de gratuit

Moins compétent en MySQL, mais c'est réputé puissant, en effet (en espérant que ça reste gratuit depuis le rachat de Sun par Oracle)

Citation:

Et je suis un peu perdu entre ADODB, ADO, ADOX, Microsoft Jet

Oui, moi aussi c'est un peu flou pour moi
Ce que j'ai compris (j'espère que je ne vais pas dire une c...erie) :
ADO est le nom global du projet d'injection de SQL dans une base de données, et il utilise OLEDB (récent) ou ODBC (plus ancien) pour se connecter à une base distante
ADO inclut ADODB et ADOX, 2 parties du projet
Jet doit être le pilote de Microsoft qui relie ADO à OLEDB ?

Edit : Ah oui, l'ancêtre de ADO est DAO, et le pilote Jet a été remplacé par le pilote ACE, et Jet et ACE sont faits pour relier des bases Microsoft, donc il me semble que se connecter à MySQL demandera de passer par ODBC, mais c'est à confirmer...
02/03/2013, 10h58
jfontaine

Bonjour,

Une autre alternative à mysql et access est d'utiliser la version gratuite de SQL server (version MSDE ou SQL Express)
http://msdn.microsoft.com/fr-fr/express/aa718378.aspx
Avec ces versions, on est juste limité en taille de la base 2 Go pour MSDE et 4Go pour Express et en nombre de requêtes simultanées
Tu y gagne en gestion de sécutité et tu as la possibilité de faire des procédures stockées
02/03/2013, 12h40
Invité

Pas access

Bonjour,
Access, bien qu’une base de données à par entière, te limite à 1 giga de données et 15 connexions simultanées. En utilisant Access, tu te limite d’enté de jeux. Utilise MySql tu pourras effectuer des applis professionnel.
J’ai quelque par une class qui s’occupe de la connexion, et des requêtes. Je regarde et je la mets en ligne.
02/03/2013, 15h59
ANOVA

Bonjour,

merci à tous pour vos réponses. J'y vois déjà un peu plus clair. Le forum de developpez.net est toujours aussi bien.

rdurupt, tes exemples de code m'intéressent vraiment.

Je vous remercie

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub Test()
Dim Con As New ADODBRD
'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
'12 - MySQL
Con.TYPEBASE = 12
Con.Server = "MyServeur"
Con.User = "MyUser"
Con.PassWord = "MyPassXord"
Con.BASE = "MyBase"
 
Dim Sql As String

Class ADODBRD

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
191
192
193
194
195
196
197
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
'12 - MySQL
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
    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 11
            GenereCSTRING = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & Server & ";UID=" & User & ";DATABASE=" & BASE & ";Password=" & PassWord & ";"
 
    Case Else
        GenereCSTRING = "PAS ASSEZ DE PARAMETRES RENSEIGNES !!!"
 
 
End Select
''MsgBox GenereCSTRING
'Response.End
End Function
 
 
Public Function OpenConnetion()
'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 OpenSchema()
Dim OpenRecordSet As Object
  Set OpenRecordSet = CreateObject("ADODB.Recordset")
 Set OpenRecordSet = Connexion.OpenSchema(5)
OpenSchema = OpenRecordSet
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
    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
Dim Rs As Object
Con.OpenConnetion
Set Rs = Con.OpenRecordSet(Slq)
Set Rs = Con.CloseRecordSet(Rs)
Con.CloseConnection
End Sub

02/03/2013, 17h51
78chris

Bonjour

Il existe aussi une version gratuite d'Oracle : Oracle Database 11g Express Edition

Personnellement je trouve quelques limites à Mysql au niveau des relations et clés étrangères...

Il y a aussi Postgres en gratuit...

PS : Coucou à Tototiti
02/03/2013, 17h54
tototiti2008

Bonjour à tous,

Ce sujet est intéressant
@rdurupt : comme tu as l'air au fait de toutes ces chaines de connexion, il me semble que la connexion Oracle par OLEDB (OraOLEDB.Oracle.1) nécessite un pilote Oracle installé sur le poste, je suppose qu'il en est de même pour MySQL
Sur le site de MySQL, ils proposent des connecteurs, saurais-tu nous dire lequel charger ? http://dev.mysql.com/downloads/connector/

Merci

PS : Coucou à Chris ;)
02/03/2013, 19h34
Invité

D’abord, quand j’ai élaboré cette class qui permet de ce connecter à tout ce qui ressemble de près ou de loin à une basse de données, je ne pensai pas laisser quelque chose à la postérité, n’y de venir en aide à mes congénères.
Cette class est en quelque sorte un recueil d’anecdote. Ramier comme pas un, j’ai été amené durant mes mission d’ESSIISiste à effectuer différent type de connexion, et j’ai consigné ces info dans une bibliothèque pour ne plus devoir me poser la question.
Je ne suis pas plus n’y moins le dépositaire de la question que vous.

Ceci dit ou il faut installer les driver ODBC qui ne sont pas toujours fournis dans l’install de la

Voila le connecteur que j’utilise.

https://dc96.4shared.com/downloadhel....1.8-win32.exe
02/03/2013, 19h37
tototiti2008

Merci :)

Citation:

Envoyé par rdurupt

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub Test()
Dim Con As New ADODBRD
'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
'11 - MySQL
Con.TYPEBASE = 11
Con.Server = "MyServeur"
Con.User = "MyUser"
Con.PassWord = "MyPassXord"
Con.BASE = "MyBase"
 
Dim Sql As String

Class ADODBRD

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
191
192
193
194
195
196
197
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
'11 - MySQL
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
    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 11
            GenereCSTRING = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & Server & ";UID=" & User & ";DATABASE=" & BASE & ";Password=" & PassWord & ";"
 
    Case Else
        GenereCSTRING = "PAS ASSEZ DE PARAMETRES RENSEIGNES !!!"
 
 
End Select
''MsgBox GenereCSTRING
'Response.End
End Function
 
 
Public Function OpenConnetion()
'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 OpenSchema()
Dim OpenRecordSet As Object
  Set OpenRecordSet = CreateObject("ADODB.Recordset")
 Set OpenRecordSet = Connexion.OpenSchema(5)
OpenSchema = OpenRecordSet
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
    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
Dim Rs As Object
Con.OpenConnetion
Set Rs = Con.OpenRecordSet(Slq)
Set Rs = Con.CloseRecordSet(Rs)
Con.CloseConnection
End Sub