0

I need to transfer 5 times per second excel columns to an mssql server. I want to overwrite the existing data in the sql server. I have tried a lot of code on the internet but it will not work. I have a working code to read a sql table to a worksheet in excel. Is it possible to write the data to the sql server? I have not much experience with vba or a sql server. I hope someone can help with the code.

I have post my working code to read the data out of the table in the SQL server, I have have deleted the password. Sorry for bad English.

Sub ADOExcelSQLServer()


   ' Carl SQL Server Connection
  '
   ' FOR THIS CODE TO WORK
    ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library

    Dim cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "NLDONL0113" ' Enter your server name here
    Database_Name = "Stroomwaarden" ' Enter your database name here
    User_ID = "Admin" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT * FROM [Stroomwaarde]" ' Enter your SQL here"

    Set cn = New ADODB.Connection
  cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
   ";Uid=" & User_ID & ";Pwd=" & Password & ";"

   rs.Open SQLStr, cn, adOpenStatic
    ' Dump to spreadsheet
   With Worksheets("sheet1").Range("M3:M500") ' Enter your sheet name and range here
       .ClearContents
       .CopyFromRecordset rs
   End With
    '            Tidy up
   rs.Close
   Set rs = Nothing
   cn.Close
   Set cn = Nothing

End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
Mitchel
  • 1
  • 2
  • If the data isn't changing, why do you need to update the table so many times per second? I would write the code to only update if a change actually happens. – Kevin Cook Apr 24 '19 at 11:45
  • I have you try for SSIS Lot : (https://www.google.com/search?client=firefox-b-d&q=SSIS+Excel+to+SQL+Server+datatable) or example (https://www.red-gate.com/simple-talk/sql/ssis/moving-data-from-excel-to-sql-server-10-steps-to-follow/) – Sanpas Apr 24 '19 at 11:50
  • The data is changing, I measure every 10 milliseconds a current sample. I want to save the data in the sql database and visualize the actual data with software that reads the database. I think that if the data to the sql server updates 5 times /second it is good enough. – Mitchel Apr 24 '19 at 11:52
  • Think again. Without knowing how you intend to represent this information, I can guess that it is likely (perhaps highly likely) that no one will be able to even recognize sub-second changes to your data. Not only does your "application" need to update the database but something "pulling" it must also recognize (or poll for) changes, retrieve that information, and then convert it into whatever graphical form you have (or intend). You should consider a more robust coding environment. – SMor Apr 24 '19 at 14:40
  • 1
    I think SMor is right and you should re-think and this is probably a [X-Y-Question](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). You asked X but your actual problem is Y. Can you explain a bit more about the background of your task? I think you are on the wrong path. • Why is there a need for 5 times a second? If it is for visualisation even once a second would be hard to follow for humans. • I would push all the measurment data into SQL (including a timestamp) and then use any other tool to visualize by querying the database when needed. – Pᴇʜ Apr 24 '19 at 15:00
  • I use a data acquisition device to sample a current of a servomotor. I measure every 10 milliseconds a sample. The servomotor have a moving cycle of 3.15 seconds. I get the data in excel and I want to transfer the data to a sql server. I can read the data with scada/hmi visualization software (Ignition Automation) in a trend. I want to have the data (including timestamp) of the last minute. I have made a FiFo (first in first out) in excel and I want to send the FiFo 5 times/second to the sql server. It is for the operator to see the actual servomotor currents to monitor them. – Mitchel Apr 25 '19 at 05:20
  • Why don't you push *all* the measurements raw to the database (as they are measured) and let the visualization tool query only the last minute data? To ensure your database does not grow too fast you could easily delete everything that is older than eg 5 or 10 minutes (each time you push new data). I think that would be much easier than having a time critical process in VBA (which does not support multithreading and therefore makes time critical things quite hard). Actually I think values updating visually 5 times a second is a heavy load for a human brain, don't you? – Pᴇʜ Apr 25 '19 at 06:31
  • Yes that's possible but is it possible to send that fast? I have 8 measurements on 1 data acquisition device, so it means that I need to send 8 cells in excel to sql server every 10 milliseconds. the operator sees a trend line and not a decimal value that updates 5 times/second. – Mitchel Apr 25 '19 at 06:54
  • @Mitchel Actually if you need data every 0.2 seconds then just measure every 0.2 seconds. What is the actual reason of measuring all the data and produce a heavy load and finally dump most of it? The issue here is VBA, if you need to do something every 0.2 seconds and the action to process it needs 0.21 then it doesn't work anymore because VBA does not support multi-threading. Therefore I would re-think to use VBA if it is time critical. – Pᴇʜ Apr 25 '19 at 07:50
  • I need to have a sample every 10 milliseconds because I need to check the current profile. The movement of the servomotor is very fast (3,15 seconds) some peaks are from 1 to 8 amps within 80 milliseconds. To visualize the current in good resolution I need to sample the current every 10 ms. it is not enough to know the peak value. The operator can see on the current profile if the settings of the machine are good. – Mitchel Apr 25 '19 at 08:19
  • the data acquisition device update automatically the cell every 10ms, when I send the value to the sql server on cell change in excel it will work? then i create a fifo in the sql server. – Mitchel Apr 25 '19 at 09:03
  • You could give it a try on cell change but I assume if your code needs more than 10ms to proceed the insert/update query it will probably disturb the update of the cell. – Pᴇʜ Apr 25 '19 at 09:14
  • if it takes more time for the code to proceed I can buffer 10 values so the code have 100ms to proceed. But it brings me back to my first question, the code in my first post can only read from the database. can someone help my to modify the code that I can write to the sql database? Thanks for the help so far I appreciate it. – Mitchel Apr 25 '19 at 10:04
  • @Mitchel [INSERT data from Excel into SQL DB](https://stackoverflow.com/questions/3767879/insert-data-from-excel-into-sql-db) If you reaseach you will find more tutorials for that task than you can read. – Pᴇʜ Apr 25 '19 at 12:18
  • I have read a lot and tried a lot of codes but nothing will work. I tried to use the code in the link but I do something wrong with the columns. – Mitchel Apr 26 '19 at 06:37
  • How do you expect us to help/explain you what you did wrong if you don't show us what you did. *"Did not work"* is not an error description. Please ask a new question for that. Show the code you have tried, include which errors you got and in which line. Explain what your raw data looks like and what your expected result should look like (screenshots can help to illustrate/explain). Please have a look at [mcve] this might help you to write a good question, that we are possible to answer. – Pᴇʜ Apr 26 '19 at 07:20

0 Answers0