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
| Private Const Requete As String = "Requete"
Private Const c_sql As String = "SELECT * FROM maTable;"
Private Const c_MotDePasse As String = "motdepasse"
Public Sub CreationRequeteAvecMotDePasse(sql As String, RangeDestination As String, Feuille As Worksheet, QueryName As String)
'Pour le developpeur
Dim serveur, NomBASE, RepertoireBase, connexion, baseconnexion, repertoireconnexion
Dim DebuRequete As range: Set DebuRequete = range(RangeDestination)
Dim strcrypt As String
On Error GoTo Erreur_CreationRequete
serveur = Sheets("Param").range("Serveur").Value
NomBASE = Sheets("Param").range("Nom_BASE_AvecMotDePasse").Value
RepertoireBase = Sheets("Param").range("RepertoireBase")
baseconnexion = serveur & "\" & RepertoireBase & "\" & NomBASE
repertoireconnexion = serveur & "\" & RepertoireBase
connexion = "ODBC;DSN=MS Access Database;DBQ=" & baseconnexion & ";DefaultDir=" & repertoireconnexion & ";" _
& "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;PWD=" & c_MotDePasse
Worksheets("Portefeuille").Activate
DebuRequete.Select
With Feuille.QueryTables.Add(Connection:=(connexion), Destination:=DebuRequete, sql:=(sql))
.CommandText = ((sql))
.Name = QueryName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
' .RefreshStyle = xlInsertDeleteCells
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
'ARO 29/09/14
Exit_CreationRequete:
Exit Sub
Erreur_CreationRequete:
MsgBox Err.Description
Resume Exit_CreationRequete
End Sub
Public Sub CreationRequeteSansMotDePasse(sql As String, RangeDestination As String, Feuille As Worksheet, QueryName As String)
'Pour le developpeur
Dim serveur, NomBASE, RepertoireBase, connexion, baseconnexion, repertoireconnexion
Dim DebuRequete As range: Set DebuRequete = range(RangeDestination)
Dim strcrypt As String
On Error GoTo Erreur_CreationRequete
serveur = Sheets("Param").range("Serveur").Value
NomBASE = Sheets("Param").range("Nom_BASE_SansMotDePasse").Value
RepertoireBase = Sheets("Param").range("RepertoireBase")
baseconnexion = serveur & "\" & RepertoireBase & "\" & NomBASE
repertoireconnexion = serveur & "\" & RepertoireBase
connexion = "ODBC;DSN=MS Access Database;DBQ=" & baseconnexion & ";DefaultDir=" & repertoireconnexion & ";" _
& "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
Worksheets("Portefeuille").Activate
DebuRequete.Select
With Feuille.QueryTables.Add(Connection:=(connexion), Destination:=DebuRequete, sql:=(sql))
.CommandText = ((sql))
.Name = QueryName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
' .RefreshStyle = xlInsertDeleteCells
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Exit_CreationRequete:
Exit Sub
Erreur_CreationRequete:
MsgBox Err.Description
Resume Exit_CreationRequete
End Sub |
Partager