Beste helpers,
Ik heb code gekregen/gemaakt via andere collega's, maar nu is de vraag of de connectie na het refreshen sluit of niet. Dit omdat onze server van de DB waarnaar geconnecteerd wordt slechts een maximaal aantal connecties toelaat voor de performantie. Het is dus belangrijk dat de connectie sluit (maar niet gedelete) wordt na het refreshen.
Wie kan mij hiermee helpen?
Ik heb code gekregen/gemaakt via andere collega's, maar nu is de vraag of de connectie na het refreshen sluit of niet. Dit omdat onze server van de DB waarnaar geconnecteerd wordt slechts een maximaal aantal connecties toelaat voor de performantie. Het is dus belangrijk dat de connectie sluit (maar niet gedelete) wordt na het refreshen.
Wie kan mij hiermee helpen?
Code:
Sub Read_view()
Dim cnx As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cnx = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
cnx.Open ("User ID=PROVTOOL;Password=paswoord;Data Source=ODSA01;Provider=MSDAORA.1")
Query = "BEGIN ODS.SET_TOP_PARENT ('" & Range("A1").Value & "', '" & Application.UserName & "'); END;"
rs.Open Query, cnx
cnx.Close
Set Param1 = Nothing
Set cnx = Nothing
'tweede deel code met refresh
With ActiveWorkbook.Connections( _
"ODSA01 (Default) V_PROVTOOL").OLEDBConnection
.BackgroundQuery = True
.CommandText = "SELECT ""Top Parent"",""Company Name"",""Payment responsible"",""Level 40"",""MSISDNs"",""Tariff Models"",""Service Features"",""BSCS Status""," & """Split Billing"",""Card Type"",""Shape Profile"",""SB Profile"",""Data National"",""Data Roaming 1"",""Data Roaming 2"",""Roaming For Business""," & _
"""Easy International"",""Roaming Fleet"",""ROA-Roaming Europe 50 MB"",""ROA-Roaming Europe 1.5 GB"",""ROA-Roaming World 100 MB""," & """BlackBerry (Enterprise ed.)"",""Business Everywhere (MAX)"",""Business Everywhere Optima"",""Connect Enterprise"",""Mail roaming world 10MB""," & _
"""Roaming for business free"",""iPhone for Business"",""single-country service (2.47.)"",""RfB World (at 17.)"",""Fleet on standard, on RfB""," & """Lux on standard, on RfB"",""RfB Wolrd Advantage"",""Blackberry Premium"",""ICA Business (B)"",""Wirefree Office National 40h"",""Blackberry Internet""," & _
"""Connect Mail"",""Connect Mail and Surf"",""International Mobile Advantage"",""Roaming for Business"",""Roaming for Business Advantage""," & """Call-bundle 40euro"",""200 Sms-bundle"",""30 SMS ATAN free"",""50perc. Disc. wirefree office"",""Free Roaming Fleet"",""MMS 15"",""MMS 30""," & _
"""500 Sms-bundle"",""Pro VoiceMail"",""SMS 150"",""Office Zone Comfort Master"",""Office Zone Comfort Slave"",""SMS 50"",""40 SMS ATAN free""," & """Roaming Internet Daily (100MB)"",""Fax Service"",""MaTMa flag w/o Monthly Fee"",""Business Everywhere 200MB"",""Business Everywhere 1GB""," & _
"""Mobile Data Sum/Share 200MB"",""Mobile Data Sum/Share 500MB"",""Mobile Data Sum/Share 1GB"",""favourite country France"",""favourite country Netherlands""," & """International Mobile Adv Free"",""Business Everywhere 5 GB"",""Business Everywhere daily 1GB"",""sms bundle 120 sans discount"",""BlackBerry Tribe""," & _
"""BlackBerry Complete"",""SMS Unlimited"",""250 SMS"",""1000 SMS"",""250 SMS ATAN Free"",""Business Everywhere for iPhone"",""Business Everywhere 2GB""," & """7.5% Atan"",""10% Atan"",""200 SMS ATAN free"",""SMS ATAN for 9 cents"",""5% International Reduction"",""15% International Reduction""," & _
"""ATAN dicsount 15%"",""25% International reduction"",""ATAN discount 5%"",""Roaming Europe Traveller"",""VPN On Net service (mobile)""," & """Office Zone"",""Office zone Privilege"",""National discount"",""All GPRS Multiline discount"",""Orange MNC offer"",""Anytime free hours""," & _
"""GPRS Roaming World Flexible"",""Voice Pack SMS 120"",""90 fleet SMS free"",""Option WIFI 3 h"",""WireFree Office 4 euro"",""GPRS Roaming EU+ Flexible""," & """20 SMS ATAN free"",""KEEP Wirefree Office Nat. Free"",""KEEP 1hour ATAN Free 24months"",""30 min fleet monocard"",""GPRS Roaming RoW Flexible""," & _
"""GPRS Roaming Flexible RoW 2012"",""GPRS Roaming Flexible Ext 2012"",""GPRS Roaming Flexible Eu 2012"",""GPRS Roaming Flex Ext/Euro2012""," & """Bus. Everywhere 1GB Duo Supp"",""Bus. Everywhere 3GB Duo Supp"" FROM ODS.V_PROVTOOL"
'"SELECT * FROM ODS.V_PROVTOOL")
.CommandType = xlCmdSql
.Connection = "OLEDB;User ID=PROVTOOL;Password=paswoord;Data Source=ODSA01;Provider=MSDAORA.1"
.RefreshOnFileOpen = False
.SavePassword = True
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
'.ServerFillColor = False
'.ServerFontStyle = False
'.ServerNumberFormat = False
'.ServerTextColor = False
End With
With ActiveWorkbook.Connections( _
"ODSA01 (Default) V_PROVTOOL")
.Name = "ODSA01 (Default) V_PROVTOOL"
.Description = ""
End With
With ActiveWorkbook.Connections( _
"ODSA01 (Default) V_PROVTOOL").OLEDBConnection
.RefreshOnFileOpen = True
.BackgroundQuery = False
End With
Laatst bewerkt: