excel to sql server (frequently)

[JohnE]

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

 

[Jean-Yves]

Hi John,

For your connection object, what is your data source exactly ?
So we can buid the connection string correctly.

Regards
JY

 

[JohnE]

The whole connection would be something like;
Data Source = Mobile29
Initial Catalog = StockQuote (which is on SQL Sever 2005)

Integrated Security = SSPI

The table is called tbl_StockQuote.

Thank you for responding.

.... John

 

[Jean-Yves]

For Standard Security (from
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer)

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"
oroConn.Open "Provider=sqloledb;" & _
"Server=myServerName;" & _
"Database=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"
BecomesoConn.Open "Provider=sqloledb;" & _
"Data Source=Mobile29;" & _
"Initial Catalog=StockQuote ;" & _
"User Id=myUsername;" & _
"Password=myPassword"Sub UpdateSQL()Dim conn As
ADODB.ConnectionDim rst as ADODB.RecordsetDim i as Integerdb =
WorkbookNameHere.Path (here is a sticking spot - is there more to put
here?)Set conn = New ADODB.ConnectionSet rec = New ADODB.ConnectionConn.Open
"Provider=sqloledb;" & _
"Data Source=Mobile29;" & _
"Initial Catalog=StockQuote ;" & _
"User Id=myUsername;" & _
"Password=myPassword"strSQL = "SELECT * FROM
tbl_StockQuote"rec.open strSQL , conn, adOpenForwardOnly, adLockReadOnly
For i - 0 To rst.Fields.Count - 1 Range("A1").Offset(0,i).Value =
rst.Fields(i).Name NextIf rec.EOF = False Then rng.Offset(37,
0).CopyFromRecordset recEnd If Range("A1").Offset(1,0).CopyFromRecordset
rstEnd WithSet rst = Nothingconn.CloseSet conn = NothingEnd SubUNTESTED,
but should bring you in the correct direction

regards
JY

 

[JohnE]

Well, I tried working with this but nothing goes. I am at a loss on
what/where to go from here. I guess my newbie-ness is really showing thru on
this.
Can anyone see what is wrong as to why this is not getting to the db on sql
server?
Thanks.
.... John