Marco Porzio
unread,Sep 16, 2021, 5:53:56 AM9/16/21Sign in to reply to author
Sign in to forward
You do not have permission to delete messages in this group
Sign in to report message as abuse
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to
Ciao,
nel mio file Excel, su click di un pulsante, mi connetto ad un database poi eseguo 4 query legate alla stessa tabella ma con filtri diversi.
se commento la query n° 3 (StrQueryX3) funziona tutto mentre se la lascio in uso mi va in timeout. fino a qualche giorno fa funzionava tutto anche se il caricamento dei dati impiegava molto tempo. Ora non so perchè faccia così, magari esiste un codice più rapido per ottenere i dati oppure occorre mettere una pausa tra una query e l'altra per non sovraccaricare il tutto?
Questo il codice in uso al momento:
Sub EseguiFiltri()
Dim cnn As New ADODB.Connection
Dim rstX1 As New ADODB.Recordset
Dim rstX2 As New ADODB.Recordset
Dim rstX3 As New ADODB.Recordset
Dim rstX4 As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQueryX1 As String
Dim StrQueryX2 As String
Dim StrQueryX3 As String
Dim StrQueryX4 As String
Application.DisplayAlerts = False
ConnectionString = "Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User ID=yyy;Data Source=SERVER\ABC;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=CATALOGO"
'Opens connection to the database
cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
cnn.CommandTimeout = 900
'Query dati (Semplificata per non creare confusione in questo post)
StrQueryX1 = "SELECT TOP (50) Campo1, Campo2... FROM [CATALOGO].[dbo].[MIAVISTA] where CARATTERISTICA= 'X1' ORDER BY DATA DESC"
StrQueryX2 = "SELECT TOP (50) Campo1, Campo2... FROM [CATALOGO].[dbo].[MIAVISTA] where CARATTERISTICA= 'X2' ORDER BY DATA DESC"
StrQueryX3 = "SELECT TOP (50) Campo1, Campo2... FROM [CATALOGO].[dbo].[MIAVISTA] where CARATTERISTICA= 'X3' ORDER BY DATA DESC"
StrQueryX4 = "SELECT TOP (50) Campo1, Campo2... FROM [CATALOGO].[dbo].[MIAVISTA] where CARATTERISTICA= 'X4' ORDER BY DATA DESC"
rstX1.Open StrQueryX1, cnn
rstX2.Open StrQueryX2, cnn
rstX3.Open StrQueryX3, cnn
rstX4.Open StrQueryX4, cnn
Sheets(ActiveSheet.Name).Range("DA2").CopyFromRecordset rstX1
Sheets(ActiveSheet.Name).Range("EK2").CopyFromRecordset rstX2
Sheets(ActiveSheet.Name).Range("FU2").CopyFromRecordset rstX3
Sheets(ActiveSheet.Name).Range("A59").CopyFromRecordset rstX4
ActiveSheet.PivotTables("tp_GrafX1").PivotCache.Refresh
ActiveSheet.PivotTables("tp_GrafX2").PivotCache.Refresh
ActiveSheet.PivotTables("tp_GrafX3").PivotCache.Refresh
la query n° 4 non usa tabelle pivot
Application.DisplayAlerts = True
End Sub
suggerimenti?
grazie
Marco