Option Base 1
Option Explicit
Option Private Module
'====================================================================================================
Sub SQL()
Dim aCoNN As New ADODB.Connection, aComm As New ADODB.Command, aRS As New ADODB.RecordSet
Dim arr(), t!
PRDX_ADODB_Open aCoNN, aComm, "server IP or address", "DB_Name"
'PRDX_ADODB_CommandToRecordset aCoNN, aRS, "select * from [AKK_estimate]"
PRDX_ADODB_CommandToRecordset aCoNN, aRS, "select * from [tbl_Name]"
't = Timer
' arr = PRDX_ADODB_RecordSetToArr2D(aRS)
'Debug.Print Round(Timer - t, 2), "ToArr2D" ' 80
't = Timer
' Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value2 = arr
'Debug.Print Round(Timer - t, 2), "ToSheet" ' 20
t = Timer
Range("A1").CopyFromRecordset aRS
Debug.Print Round(Timer - t, 2), "ToSheet" ' 49.86
aRS.Cancel: aComm.Cancel: aCoNN.Close
End Sub
'====================================================================================================
'====================================================================================================
'====================================================================================================
'https://learntutorials.net/ru/excel-vba/topic/9958/sql-%D0%B2-excel-vba---%D0%BB%D1%83%D1%87%D1%88%D0%B8%D0%B5-%D0%BF%D1%80%D0%B0%D0%BA%D1%82%D0%B8%D0%BA%D0%B8
Sub PRDX_ADODB_Open(aCoNN As ADODB.Connection, aComm As ADODB.Command, ByVal iServer$, ByVal iDB$, Optional ByVal iUserID$, Optional ByVal iPswrd$)
iDB = "Provider=MSOLEDBSQL.1;Initial Catalog=" & iDB & ";Data Source=" & iServer & ";Integrated Security=SSPI"
aCoNN.Open iDB, iUserID, iPswrd: aComm.ActiveConnection = aCoNN
End Sub
'----------------------------------------------------------------------------------------------------
Sub PRDX_ADODB_CommandToRecordset(aCoNN As ADODB.Connection, aRS As ADODB.RecordSet, ByVal iCommand$)
Set aRS = aCoNN.Execute(iCommand)
End Sub
'----------------------------------------------------------------------------------------------------
Function PRDX_ADODB_RecordSetToArr2D(aRS As ADODB.RecordSet) As Variant()
Dim a(), B(), r&, c&
a = aRS.GetRows: ReDim B(1 + UBound(a, 2), 1 + UBound(a, 1))
For c = 0 To UBound(a, 1)
For r = 0 To UBound(a, 2)
B(r + 1, c + 1) = a(c, r)
Next r
Next c
PRDX_ADODB_RecordSetToArr2D = B
End Function
'====================================================================================================
'====================================================================================================
|