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 |
Partager