Example of connecting SQL Server databases in VBA _VB

When we are programming with Excel, we often need to use a database.

So how do you connect the database and then read the data from the database?

VBA connects to an instance of SQL SERVER database:

Dim strconn As String, strSQL as String Dim conn as ADODB. Connection Dim DS as ADODB. Recordset Dim col as Integer ' connect the database string strconn = ' Provider=SQLOLEDB.1; Persist Security info=true; User Id=[user]; Password=[password];initial Catalog=[database];D ata source=[database IP address or database server name]; Connect timeout=720;
   "' Query statement, if the SQL statement is very long can be used strsql=strsql+ to connect to multiple paragraphs of statements, if the statement is very short can be written on only one line.
 
   strSQL = "SELECT * from hy_kpi_shop_dept_weekrpt" strSQL = strsql+ "where sdate= ' 2014-01-01 ' ORDER by Sdate,shopid" Set conn = New ADODB. Connection Set ds = New ADODB. Recordset ' Open database connection conn.
   Open strconn ' This sentence and the database connection string at the Connect timeout=720, said that if the statement runs for a long time, these two sentences can extend the waiting time of VBA, without these two sentences, VBA often reported the query timeout. Conn.commandtimeout = 720 with DS ' obtains data based on query statements. Open strSQL, Conn ' automatic control adds all column headings for col = 0 to DS.
   Fields.count-1 ' Please note that the parameters in offset (0, col) must be correct, which means that the title will be written in the first row, starting with the A1 cell, and if you do not want to write to the header row, you can comment out the following sentence. Worksheets ("KPI weekly for each class in the store"). Range ("A1"). Offset (0, col). Value = ds. Fields (COL).
 
  Name Next' Add all row data, which means the query results will be written in the first row, starting with cell A1, but because the header row is written in the first row, the actual line is written from one line under the heading. Worksheets ("Sheet1"). Range ("A1"). Offset (1, 0). CopyFromRecordset DS End With ' Closes the database connection and empties the resource Set ds = Nothing conn. Close Set conn = Nothing