Connection to SQL

Question

I want to add a connection to a sheet so that each time a cell is refreshed, a value is passed in from SQL. I want it to reference a textbox on one of the other sheets for where ID =

How do I set the connection to refresh with that value. What do I put in my connection string? I can get it to work staticly.

Answer 1

Excel has a handy little feature called "Import External Data". Here's what I would do:

1. In your Excel file, goto Data->Import External Data->New Database Query (In your case, the Import Data option won't be the best method)

2. Go through the wizard. Choose your datasource, etc. Except on the last option, choose View data or edit query in Microsoft Query

3. MS Query is basically a little query designer. Choose the show/hide criteria icon (looks like eyeglass + funnel).

4. This interface for putting in criteria is much like Access. Choose a field and instead of hard coding values, use [Criteria] (or something).

5. Close out MS Query and go back to the Excel file.

6. In your new data range, right-click to Parameters.->Get the value from the following cell

7. Don't forget the Refresh automatically when cell value changes

To summarize, you are importing external data using a parameter, and declaring the value of that parameter to be a cell in your file. Well, that was a nice little break from my own work. I hope this helps.

Answer 2

What causes the cell to be refreshed? The connection string will be dependent on what database you are using. The below example uses Access and needs a reference to Microsoft ActiveX Dataobjects 2.8

The data is put in cell(1,1) on sheet1. The where clause is taken from textbox1 on sheet2:

Private Sub CommandButton1_Click() 

Dim sqlQuery As String

sqlQuery = "SELECT * FROM myTable WHERE " & Worksheets("sheet2").TextBox1.Text
fetchData "C:\file_databases\myDatabase.accdb", Worksheets("sheet1").Cells(1, 1), sqlQuery

End Sub

Private Function fetchData(databaseName As String, targetRange As Range, sqlQuery As String)

Dim connection As New ADODB.connection
Dim records As New ADODB.Recordset
Dim connectionString As String
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databaseName & ";"

connection.Open connectionString
records.Open sqlQuery, connection
targetRange.CopyFromRecordset records

records.Close
connection.Close

End Function