Mixing
Can we connect to Redshift from Excel VBA?
0
May I ask can we make ADO or any connection to Redshift by Excel VBA?
So far, we connect to SQL Server and get the job results as follows;
-- Current Code for SQL Server 2012/2016
Sub Get_DB_Conncetion(StrDBName As String, StrSQL As String, DestSht As String, DestCell As String)
On Error GoTo Err_DBConnectOpen
Dim con As New ADODB.Connection
Dim conStr As String
Dim rs As Variant
Dim cmd As Object
'Connect String
conStr = "Provider=Sqloledb;Data Source=" & StrSvr _
& ";Initial Catalog=" & StrDBName _
& ";Connect Timeout=10" _
& ";user id=" & StrID _
& ";password=" & StrPWD _
& ""
Debug.Print Now & "：Connect String：" & conStr
'Connect to Database
con.Open conStr
'Execute SQL
con.CommandTimeout = 900
Set rs = con.Execute(StrSQL)
If Not rs.EOF Then
With Sheets(DestSht)
.Activate
.Range(DestCell).CopyFromRecordset rs
End With
End If
'Close Session
con.Close
Set con = Nothing
Exit Sub
'Error Handling
Err_DBConnectOpen:
MsgBox "Can't Connect" & vbCrLf & _
Err.Description, vbOKOnly, vbCritical
EXIT_FLG = True
'Close Connection
If con.State <> ADODB.adStateClosed Then
con.Close
End If
Set con = Nothing
Exit Sub
End Sub
Until we can make confirmed everything's OK, we need to check and compair the figures both of SQL Server and Redshift everyday. For efficiency and accuracy, we'd like to throw the check queries and get the results automatically as much as possible.
Any advice would be appreciated again.
Thank you.
excel vba excel-vba amazon-redshift ado
asked Nov 19 '18 at 14:13
Sachiko
758
add a comment |
0
May I ask can we make ADO or any connection to Redshift by Excel VBA?
So far, we connect to SQL Server and get the job results as follows;
-- Current Code for SQL Server 2012/2016
Sub Get_DB_Conncetion(StrDBName As String, StrSQL As String, DestSht As String, DestCell As String)
On Error GoTo Err_DBConnectOpen
Dim con As New ADODB.Connection
Dim conStr As String
Dim rs As Variant
Dim cmd As Object
'Connect String
conStr = "Provider=Sqloledb;Data Source=" & StrSvr _
& ";Initial Catalog=" & StrDBName _
& ";Connect Timeout=10" _
& ";user id=" & StrID _
& ";password=" & StrPWD _
& ""
Debug.Print Now & "：Connect String：" & conStr
'Connect to Database
con.Open conStr
'Execute SQL
con.CommandTimeout = 900
Set rs = con.Execute(StrSQL)
If Not rs.EOF Then
With Sheets(DestSht)
.Activate
.Range(DestCell).CopyFromRecordset rs
End With
End If
'Close Session
con.Close
Set con = Nothing
Exit Sub
'Error Handling
Err_DBConnectOpen:
MsgBox "Can't Connect" & vbCrLf & _
Err.Description, vbOKOnly, vbCritical
EXIT_FLG = True
'Close Connection
If con.State <> ADODB.adStateClosed Then
con.Close
End If
Set con = Nothing
Exit Sub
End Sub
Until we can make confirmed everything's OK, we need to check and compair the figures both of SQL Server and Redshift everyday. For efficiency and accuracy, we'd like to throw the check queries and get the results automatically as much as possible.
Any advice would be appreciated again.
Thank you.
excel vba excel-vba amazon-redshift ado
asked Nov 19 '18 at 14:13
Sachiko
758
1
If you download Amazon's Redshift drivers, you can simply build the connection string to Redshift. The connection string would look something like:connString = "Driver={Amazon Redshift (x64)};Server=ServerName;Database=DBName;UID=Login;PWD=password;Port=PortNum;SSL=true;Sslmode=Require"
– ArcherBird
Nov 19 '18 at 14:33
Thanks a lot, @ArcherBird !! I'm so glad to know it's possible, since it's a bit burden for me to check 200 tables figures on each database :) Thank you so much for your kind answer.
– Sachiko
Nov 21 '18 at 10:45
add a comment |
0
0
0
May I ask can we make ADO or any connection to Redshift by Excel VBA?
So far, we connect to SQL Server and get the job results as follows;
-- Current Code for SQL Server 2012/2016
Sub Get_DB_Conncetion(StrDBName As String, StrSQL As String, DestSht As String, DestCell As String)
On Error GoTo Err_DBConnectOpen
Dim con As New ADODB.Connection
Dim conStr As String
Dim rs As Variant
Dim cmd As Object
'Connect String
conStr = "Provider=Sqloledb;Data Source=" & StrSvr _
& ";Initial Catalog=" & StrDBName _
& ";Connect Timeout=10" _
& ";user id=" & StrID _
& ";password=" & StrPWD _
& ""
Debug.Print Now & "：Connect String：" & conStr
'Connect to Database
con.Open conStr
'Execute SQL
con.CommandTimeout = 900
Set rs = con.Execute(StrSQL)
If Not rs.EOF Then
With Sheets(DestSht)
.Activate
.Range(DestCell).CopyFromRecordset rs
End With
End If
'Close Session
con.Close
Set con = Nothing
Exit Sub
'Error Handling
Err_DBConnectOpen:
MsgBox "Can't Connect" & vbCrLf & _
Err.Description, vbOKOnly, vbCritical
EXIT_FLG = True
'Close Connection
If con.State <> ADODB.adStateClosed Then
con.Close
End If
Set con = Nothing
Exit Sub
End Sub
Until we can make confirmed everything's OK, we need to check and compair the figures both of SQL Server and Redshift everyday. For efficiency and accuracy, we'd like to throw the check queries and get the results automatically as much as possible.
Any advice would be appreciated again.
Thank you.
excel vba excel-vba amazon-redshift ado
asked Nov 19 '18 at 14:13
Sachiko
758
May I ask can we make ADO or any connection to Redshift by Excel VBA?
So far, we connect to SQL Server and get the job results as follows;
-- Current Code for SQL Server 2012/2016
Sub Get_DB_Conncetion(StrDBName As String, StrSQL As String, DestSht As String, DestCell As String)
On Error GoTo Err_DBConnectOpen
Dim con As New ADODB.Connection
Dim conStr As String
Dim rs As Variant
Dim cmd As Object
'Connect String
conStr = "Provider=Sqloledb;Data Source=" & StrSvr _
& ";Initial Catalog=" & StrDBName _
& ";Connect Timeout=10" _
& ";user id=" & StrID _
& ";password=" & StrPWD _
& ""
Debug.Print Now & "：Connect String：" & conStr
'Connect to Database
con.Open conStr
'Execute SQL
con.CommandTimeout = 900
Set rs = con.Execute(StrSQL)
If Not rs.EOF Then
With Sheets(DestSht)
.Activate
.Range(DestCell).CopyFromRecordset rs
End With
End If
'Close Session
con.Close
Set con = Nothing
Exit Sub
'Error Handling
Err_DBConnectOpen:
MsgBox "Can't Connect" & vbCrLf & _
Err.Description, vbOKOnly, vbCritical
EXIT_FLG = True
'Close Connection
If con.State <> ADODB.adStateClosed Then
con.Close
End If
Set con = Nothing
Exit Sub
End Sub
Until we can make confirmed everything's OK, we need to check and compair the figures both of SQL Server and Redshift everyday. For efficiency and accuracy, we'd like to throw the check queries and get the results automatically as much as possible.
Any advice would be appreciated again.
Thank you.
excel vba excel-vba amazon-redshift ado
excel vba excel-vba amazon-redshift ado
asked Nov 19 '18 at 14:13
Sachiko
758
asked Nov 19 '18 at 14:13
Sachiko
758
asked Nov 19 '18 at 14:13
Sachiko
758
asked Nov 19 '18 at 14:13
Sachiko
758
asked Nov 19 '18 at 14:13
Sachiko
758
758
1
If you download Amazon's Redshift drivers, you can simply build the connection string to Redshift. The connection string would look something like:connString = "Driver={Amazon Redshift (x64)};Server=ServerName;Database=DBName;UID=Login;PWD=password;Port=PortNum;SSL=true;Sslmode=Require"
– ArcherBird
Nov 19 '18 at 14:33
Thanks a lot, @ArcherBird !! I'm so glad to know it's possible, since it's a bit burden for me to check 200 tables figures on each database :) Thank you so much for your kind answer.
– Sachiko
Nov 21 '18 at 10:45
add a comment |
1
If you download Amazon's Redshift drivers, you can simply build the connection string to Redshift. The connection string would look something like:connString = "Driver={Amazon Redshift (x64)};Server=ServerName;Database=DBName;UID=Login;PWD=password;Port=PortNum;SSL=true;Sslmode=Require"
– ArcherBird
Nov 19 '18 at 14:33
Thanks a lot, @ArcherBird !! I'm so glad to know it's possible, since it's a bit burden for me to check 200 tables figures on each database :) Thank you so much for your kind answer.
– Sachiko
Nov 21 '18 at 10:45
1
1
If you download Amazon's Redshift drivers, you can simply build the connection string to Redshift. The connection string would look something like:
connString = "Driver={Amazon Redshift (x64)};Server=ServerName;Database=DBName;UID=Login;PWD=password;Port=PortNum;SSL=true;Sslmode=Require"– ArcherBird
Nov 19 '18 at 14:33
If you download Amazon's Redshift drivers, you can simply build the connection string to Redshift. The connection string would look something like:
connString = "Driver={Amazon Redshift (x64)};Server=ServerName;Database=DBName;UID=Login;PWD=password;Port=PortNum;SSL=true;Sslmode=Require"– ArcherBird
Nov 19 '18 at 14:33
Thanks a lot, @ArcherBird !! I'm so glad to know it's possible, since it's a bit burden for me to check 200 tables figures on each database :) Thank you so much for your kind answer.
– Sachiko
Nov 21 '18 at 10:45
Thanks a lot, @ArcherBird !! I'm so glad to know it's possible, since it's a bit burden for me to check 200 tables figures on each database :) Thank you so much for your kind answer.
– Sachiko
Nov 21 '18 at 10:45
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376490%2fcan-we-connect-to-redshift-from-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53376490%2fcan-we-connect-to-redshift-from-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If you download Amazon's Redshift drivers, you can simply build the connection string to Redshift. The connection string would look something like:
connString = "Driver={Amazon Redshift (x64)};Server=ServerName;Database=DBName;UID=Login;PWD=password;Port=PortNum;SSL=true;Sslmode=Require"– ArcherBird
Nov 19 '18 at 14:33
Thanks a lot, @ArcherBird !! I'm so glad to know it's possible, since it's a bit burden for me to check 200 tables figures on each database :) Thank you so much for your kind answer.
– Sachiko
Nov 21 '18 at 10:45