View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
johne johne is offline
external usenet poster
 
Posts: 40
Default excel to sql server (frequently)

Hello. I have a situation in which I seek the assistance from the newsgroup.
There is an excel 2003 spreadsheet that is populated with stock information
thru a web service. This information is updated every few seconds. This
works fine. An RTD addin was developed that pulls the stock information out
of a table in sql server 2005 every few seconds. This also works fine. My
situation is getting the information from the excel sheet to sql server every
few seconds. This would be a complete send of all 3200+ stock symbols and
pricing. You might be wondering why not have the RTD grab the information
from the worksheet? That was tested and if it updates as the RTD is getting
the info, trouble occurs. Why not have the RTD get the info from the web
service? That was also tested and the strain on the data line was too great
and costly. Going this "round-a-bout" way actually will save $30,000+ per
year. Below is what I have so far. There are areas marked that I am a bit
lost on. Here is what I have;

Sub UpdateSQL()

Dim db As String
Dim connection As String
Dim strSQL As String
Dim conn As ADODB.Connection
Dim rst as ADODB.Recordset
Dim i as Integer

db = WorkbookNameHere.Path (here is a sticking spot - is there more to put
here?)
Set conn = New ADODB.Connection
connection = "Provider = ?????" (here is a sticking spot)
connection = connection & "Data Source = " & db & ";"
conn.Open connectionstring:= connection
Set rst = New ADODB.Recordset

With rst
strSQL = "sql statement goes here" (uncertain as to type..insert,
select, etc)
.Open Source:= strSQL, ActiveConnection = conn

For i - 0 To rst.Fields.Count - 1
Range("A1").Offset(0,i).Value = rst.Fields(i).Name
Next

Range("A1").Offset(1,0).CopyFromRecordset rst

End With

Set rst = Nothing
conn.Close
Set conn = Nothing

End Sub

I am not as proficient as I would like to be so excuse the newbie-ness of it
all. I am wondering if this in on the right path or way out of line? Also,
the timing of it going to sql server every few seconds also has me lost. Not
sure if it would be added into the above or what?

Any assistance from the newsgroup is appreciated.

Thank you.

.... John