Excel VBA SQL Query Automation

[LiquidTobi]

I have connected Excel 2003 to a SQL 2000 database through ODBC, and I need to use VBA to make info typed in a certain field automatically query and stick the results from the recordset in specific fields.
First, how do I specify what constitutes an event?
Second, how do I customize the output of a SQL Query, cause I was thinking I could forward the recordset (row) to an associative array and then afterwards copy the individual fields into the fields on the excel spreadsheet, is there a more efficient way to do this?

[geekgirlau]

You would probably use the Worksheet Change event - you can use Intersect to determine whether a specific cell was updated. For example:


If Not Intersect(MySheet.Range("MyCell"), Target) Is Nothing Then


For your second question, that's normally the process that I use - send the SQL string to SQL Server, write the resulting recordset to an array, and use the array to populate the sheet.

[Bob Phillips]

Second, how do I customize the output of a SQL Query, cause I was thinking I could forward the recordset (row) to an associative array and then afterwards copy the individual fields into the fields on the excel spreadsheet, is there a more efficient way to do this?

There is a specific Recordset method for this, CopyFromRecordset



Sheet1.Range("A1").CopyFromRecordset oRS

[LiquidTobi]

W?nderbar! Is there a way to take a single column from the recordset and put it into a formatted cell? because it looks like the whole array is accounted for in the above code

[Bob Phillips]

W?nderbar! Is there a way to take a single column from the recordset and put it into a formatted cell? because it looks like the whole array is accounted for in the above code

Just change the SQL to retrieve the single column.

BTW, you can dump an array into a range in one go.

[LiquidTobi]

Ok, I read up on using intersect, but I am still not sure how to customize the event to fire right after the text is typed in, for example: I type in a 10 digit phone number and then the event fires, wherein the event handler would use that phone number as part of the SQL Query

[Bob Phillips]

Post your code.

[LiquidTobi]

I Don't have the code yet, I am trying to figure out what it will look like, but I am not sure about a lot of things, like when and where and how to connect to the database and send it a simple query, then how to properly initialize a recordset object, and then how to pass the row to the recordset.

[LiquidTobi]

Work with me here I am a noob
This code is a simple query from the database, recorded with excel's built in macro recorder.

I am going to assume That Selection.QueryTable queries the database and then writes the results to the Worksheet. How would I take these results and put them into an array instead of outputting them?


Sub QueryFromMonolith()
'
' QueryFromMonolith Macro
' Macro recorded 10/18/2006 by Tobi Lehman
'
' Keyboard Shortcut: Ctrl+Shift+F
'
With Selection.QueryTable
.Connection = "ODBC;DSN=Monolith;UID=Administrator;APP=Microsoft Office 2003;WSID=MINILITH;DATABASE=Master Campaign;Trusted_Connection=Yes"
.CommandText = Array( _
"SELECT Campaign.PhoneNum, Campaign.DateTimeofCall, Campaign.APCRAgent, Campaign.Fname, Campaign.Lname, Campaign.Address1, Campaign.City, Campaign.State, Campaign.Zip" & Chr(13) & "" & Chr(10) & "FROM ""Master Campaign"".dbo.Campai" _
, _
"gn Campaign" & Chr(13) & "" & Chr(10) & "WHERE (Campaign.PhoneNum='5036494644')" & Chr(13) & "" & Chr(10) & "ORDER BY Campaign.PhoneNum" _
)
.Refresh BackgroundQuery:=False
End With
End Sub

[Jan Karel Pieterse]

Why use VBA at all?

Sounds as if you want to update a listing based on some entry in a cell, right?

I think it will prov simpler if you create a parameter query.

Dick Kusleika explains how to here:
http://www.dicks-clicks.com/excel/ExternalData6.htm

[LiquidTobi]

Not sure what you mean by parameter query, the description looked to me what I am doing, here is my code I have so far, I found the right connection string, but the VBA compiler is telling me I don't have the libraries to connect to the database using ADO, which dll do I have to install? (here's the code I have so far)
Option Explicit
Dim myConn As New ADODB.Connection
Dim connStr As String
Dim strSQL As String
Dim recSet As New ADODB.Recordset
Sub ConnectToDb()
Set myConn = CreateObject("ADODB.Connection")
Set recSet = CreateObject("ADODB.Recordset")

connStr = "Driver={SQL Server};Server=Monolith;Database=Northwind;Uid=administrator;Pwd=~2b1ll1ANk ;"
End Sub
Sub DisconnectFromDb()
myConn.Close
End Sub
Function QueryDb(ByRef strSQL As String)
recSet.Open strSQL
QueryDb = recSet
End Function

[Jan Karel Pieterse]

A reference to the microsoft ActiveX Data Objects 2.x library (use highest number) is needed. (in the VBE Tools, references)

[LiquidTobi]

Ok, now ADODB is usable, but I can't log into my database, the username and password are correct, I think it has to do with my connection string, I am using ODBC with SQL Server 2000, here's my code:

Sub ConnectToDb()
Dim myConn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim strSQL As String

Set myConn = New ADODB.Connection
myConn.ConnectionString = "Driver={SQL Server};Server=192.168.1.111;Database=Northwind;Uid=dbuser;Pwd=dbpass;"
myConn.Open

Set recSet = New ADODB.Recordset
recSet.ActiveConnection = myConn
recSet.Open strSQL

strSQL = "SELECT * FROM Customers"

Dim incr As Integer
incr = 1

For Each X In recSet.Fields
Application.Cells(1, incr).Value = X.Name
incr = incr + 1
Next

recSet.Close
myConn.Close
Set recSet = Nothing
Set myConn = Nothing
End Sub

[Jan Karel Pieterse]

I think you missed what my point was: you don't need VBA to create a query that updates itself when you enter a different value for a certain criteria into a cell.
Isn't that what you need?

[stanl]

just a thought but...



recSet.Open strSQL
strSQL = "SELECT * FROM Customers"


should be



strSQL = "SELECT * FROM Customers"
recSet.Open strSQL



and you may need to add

recSet.CursorLocation = adUseClient

.02 Stan

[LiquidTobi]

I think you missed what my point was: you don't need VBA to create a query that updates itself when you enter a different value for a certain criteria into a cell.
Isn't that what you need?
Exactly, I just gathered that VBA was necessary to do that, could you explain how?

Oh and stanl, your post helped, the query works, another reason I was failing when connecting to the database is that I forgot I was using WinNT authentication in stead of SQL authentication.
Thank you all.

[Jan Karel Pieterse]

could you explain how?
I already have in my first post:

http://www.dicks-clicks.com/excel/ExternalData6.htm