DavidInLove
asked on
How to fetch the data from SQLSERVER in an array in VBA EXCEL instead to display it in worksheet
Hi,
My question is simple for EE used to EXCEL VBA
I want to get the data from SQLSERVER in an array instead to display
it in the worksheet
My code works to select the data and display it in the EXCEL worksheet
it is done via this command
Can Someone tell me how to fetch the rst in an array inside the macro VBA please?
Thanks Dave
My question is simple for EE used to EXCEL VBA
I want to get the data from SQLSERVER in an array instead to display
it in the worksheet
My code works to select the data and display it in the EXCEL worksheet
it is done via this command
ThisWorkbook.Sheets("my_sheet").Range("D7").CopyFromRecordset rst
Can Someone tell me how to fetch the rst in an array inside the macro VBA please?
Thanks Dave
Sub load_users()
Dim cnn As ADODB.Connection
Dim connectionString As String Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim strSQL As String
Dim User_ID As String
Dim User_PWD As String
Dim Initial_Catalog As String 'Nom de la base de données
Dim Workstation_ID As String
Dim Data_Source As String
Application.ScreenUpdating = False
On Error GoTo Verif_Connexion
User_ID = "my login " '"SYGES"
User_PWD = "my password" ' "SYGES2014"
Initial_Catalog = "DATABASE"
Data_Source = "url"
Workstation_ID = "url" '
Set cnn = New ADODB.Connection
connectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=" & User_ID & ";PWD=" & User_PWD & ";Initial Catalog=" & Initial_Catalog & ";Data Source=" & Data_Source & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & Workstation_ID & ";Use Encryption for Data=False;Tag with column collation when possible=False;"
ThisWorkbook.Sheets("my_sheet").Range("A2:AZ65536").Value = ""
strSQL = "select login, password from MYTABLE "
cnn.Open connectionString
Set rst = cnn.Execute(strSQL)
ThisWorkbook.Sheets("my_sheet").Activate
' HERE IS MY QUESTION
'This put the result in the worksheet
'I would like to have it in an array inside the code instead to displa it in the worksheet
ThisWorkbook.Sheets("my_sheet").Range("D7").CopyFromRecordset rst
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
end sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Still doesn't work:
Since I am not good at VBA maybe the way to fetch the data in array I did is wrong:
Since I am not good at VBA maybe the way to fetch the data in array I did is wrong:
Dim arrRecordArray As Variant
Set rst = cnn.Execute(strSQL)
arrRecordArray = rst.GetRows
MsgBox (arrRecordArray(1)(1))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok Thanks to you.
ASKER
I've tried to code something but it doesn't work.
Open in new window
I just would like to do the same as the following working in PHP
Open in new window
So if SO could give me the code to fetch
Thanks Dave