Using VBA to run SQL commands

[sleuth]

Has anyone used VBA to run SQL commands from a command line?

I have SQL Developer installed on my machine, and there is a command line interface using the command "sdcli".
I want to figure out how to connect to a database, run an SQL query, and download the data returned from that query to Excel for further manipulation.

I have figured out how to connect to the database and run a query within the SQL Developer interface, but honestly, I'm not an IT guy and haven't used SQL in 15 years or more.

Any ideas how to do this? Even basic command line commands to connect to the database and run the query would be helpful.

 

[sleuth]

I found this code on another site, but I'm having trouble getting my connection string to work. My macro errors out on the cnn.Open statement and says the Provider is not installed. I think it's because PROVIDER is set up for a different SQL Database type, but I can't seem to get the connection string to work.

I know my username and password, which I have successfully used to connect in the SQL Developer UI. I'm not sure what to put for remote IP address or database. Would that be the hostname and the SID in the connection properties dialog in SQL Developer? (The hostname looks more like a url without the http than an ip address. Not sure if hostname and ip address is an interchangeable term)

Sub Download_Reports()
'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String


'Setup the connection string for accessing MS SQL database
   'Make sure to change:
       '1: PASSWORD
       '2: USERNAME
       '3: REMOTE_IP_ADDRESS
       '4: DATABASE
    ConnectionString = "Provider=ORAOLEDB.ORACLE;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Data Source=REMOTE_IP_ADDRESS;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=DATABASE"
    
    '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


    'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT TOP 10 * FROM tbl_table"


    'Performs the actual query
    rst.Open StrQuery, cnn
    'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

 

[sleuth]

bump

 

[slack7639]

Here, I used a BAT file to run SQL commands . . . bookmarks-to-CSV.pdf . . . [A pdf I have made, and uploaded to my Google Docs] . . . This first page provides all you need to know, to get your Bookmarks into a .CSV file (This won't do anything to your actual Bookmarks, it just extracts a copy of them.) . . . Then, the next few pages provide the details of what's going on . . . https://drive.google.com/open?id=1xYWPQtijqCzk-1nzTsTb0ZUVKYJNFokR

 

[slack7639]

Here's the VBA I used on the SQLite3 CLI, to get my bookmarks out of Firefox

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

' Timer Function - See comments in "Sub SQLite3_VBA_code()"
Function waitTime(ByVal milliSeconds As Double)
Application.Wait (Now() + milliSeconds / 24 / 60 / 60 / 1000)
End Function

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub SQLite3_VBA_Code(control As IRibbonControl)

' Use Excel 2010's VBA to run SQLite3 to get the Bookmarks, which is one less click if combined in with the code to process them, but,
' If you want to Debug the processing code, below . . .
' . . . "waitTime" makes F8 hang. . . and . . . "Call SendKeys" makes F8 hang
' . . . . . . Debug might also include modifying the code for new things
' . . . so, these commands stop you from using Debug . . . therefore, it makes sense to keep these two functions separate

' Give the SQLite3 one second to open, otherwise, VBA is too quick, and erroneously writes the Commands to the Worksheet
' Use the Timer Function and waitTime - you can't nest a Function inside a procedure. It needs to be above.
' Application.Wait (used if these two processes are combined) doesn't work on the Shell: Application.Wait (Now + TimeValue("00:00:01"))
' ShellandWait would be better to use, but is more complicated, and I can't figure out how to get it to work.

Dim SQLITE3_BOOKMARKS As String
SQLITE3_BOOKMARKS = "C:\Program Files (x86)\SQLite3\sqlite-tools-win32-x86-3210000\sqlite3.exe"

Call Shell(SQLITE3_BOOKMARKS, vbNormalFocus)
waitTime (1000)

' output.csv could go to a folder like:
' C:\1\01\1 backup\a Bookmarks BAT files\temp
' but you might forget to delete it, so, it's good to send it to the Desktop

' Commands to get the Bookmarks. In SQLite3, you have to use forward slashes in the paths. Curly brackets are needed around ENTER.
' You have to have "ENTER" after each command

' Application.SendKeys Method (Excel) . . . https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-sendkeys-method-excel
' True to have Microsoft Excel wait for the keys to be processed before returning control to the macro.
' False (or omitted) to continue running the macro without waiting for the keys to be processed.

Call SendKeys(".echo off", True)
Call SendKeys("{ENTER}", True)

Call SendKeys(".open C:/Users/MyUserName/AppData/Roaming/Mozilla/Firefox/Profiles/u55555dh.default/places.sqlite", True)
Call SendKeys("{ENTER}", True)

Call SendKeys(".mode csv", True)
Call SendKeys("{ENTER}", True)

Call SendKeys(".once C:/Users/MyUserName/Desktop/output.csv", True)
Call SendKeys("{ENTER}", True)

' Curly brackets are needed around the inner parentheses
Call SendKeys("SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime{(}a.dateAdded/1000000,'unixepoch','localtime'{)} AS Date FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id;", True)
Call SendKeys("{ENTER}", True)

Call SendKeys(".quit", True)
Call SendKeys("{ENTER}", True)

End Sub

 

[sleuth]

I'm needing help with my connection string. Can anyone help me with this?
I'm connecting to a database via Oracle SQL Developer. I am able to connect within the UI by providing the following items:

1. Connection Name - got it.
2. myUsername - got it.
3. myPassword - got it.
4. Connection Type = Basic, Role = default
5. myHostname - got it.
6. myPort - got it.
7. mySID - got it.

However, I am unable to get my connection string to work in VBA. When I run the script I get "Run-time Error '3076'. Provider cannot be found. It may not be properly installed" on the line beginning with cnn.open.

Here is my connection string that is not working:

ConnectionString = "Provider=ORAOLEDB.ORACLE;Password=myPassword;Persist Security Info=True;User ID=myUsername;Data Source=myhostname:myport;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=mySID"

Is my provider specified incorrectly? Is there a way in SQL Developer to determine what the provider or even the entire connection string should be?

 

[slack7639]

Try this page? . . . https://docs.oracle.com/cd/B28359_01/win.111/b28375/featConnecting.htm

 

[sleuth]

Try this page? . . . https://docs.oracle.com/cd/B28359_01/win.111/b28375/featConnecting.htm

Thanks. I've checked all your links and don't seem to be getting anywhere. I'm going to try posting this question on Stack Overflow.