SQL Server + VBA programming (1)

We can choose to connect a remote database on a server or local database.

Remote: Server=(IP address)

This piece of code means to access database and read the content of Car_Info and write them into excel sheet.

Sub a()

 

    'Dim i As Integer, sht AsWorksheet

    Dim cn As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    Dim strCn As String, strSQL AsString

    cn.Open"Provider=sqloledb;Server=(local);Database=Fleet_management;Uid=sa;Pwd=*****;"

    strSQL = "select VIN,Car_IDfrom dbo.Car_Info"

    rs.Open strSQL, cn

    Set sht =ThisWorkbook.Worksheets("sheet3")

    i = 1

   

    Do While Not rs.EOF

       

      

        sht.Cells(i, 1) =rs("VIN")

        sht.Cells(i, 2) =rs("Car_ID")

 

        rs.MoveNext

        i = i + 1

    Loop

   

   

rs.Close

   

  

cn.Close

 

 

 

End Sub