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 |
excel to sql server (frequently)
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 "Jean-Yves" wrote: For Standard Security (from http://www.carlprothman.net/Default....erForSQLServer) 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" wrote in message ... 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" wrote: Hi John, For your connection object, what is your data source exactly ? So we can buid the connection string correctly. Regards JY "JohnE" wrote in message ... 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 |
All times are GMT +1. The time now is 05:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com