VBA DB

from https://stackoverflow.com/questions/34389720/connection-string-for-sql-server-2014-express-vba

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 = "EXCEL-PC\EXCELDEVELOPER" ' Enter your server name here
    Database_Name = "AdventureWorksLT2012" ' Enter your database name here
    User_ID = "" ' enter your user ID here
    Password = "" ' Enter your password here
    SQLStr = "SELECT * FROM [SalesLT].[Customer]" ' 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("a1:z500") ' 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

from https://riptutorial.com/excel-vba/example/30609/how-to-use-adodb-connection-in-vba-

How to use ADODB.Connection in VBA?

Example#

Requirements:

Add following references to the project:

• Microsoft ActiveX Data Objects 2.8 Library
• Microsoft ActiveX Data Objects Recordset 2.8 Library

Declare variables

Private mDataBase As New ADODB.Connection
Private mRS As New ADODB.Recordset
Private mCmd As New ADODB.Command

Create connection

a. with Windows Authentication#

Private Sub OpenConnection(pServer As String, pCatalog As String)
    Call mDataBase.Open("Provider=SQLOLEDB;Initial Catalog=" & pCatalog & ";Data Source=" & pServer & ";Integrated Security=SSPI")
    mCmd.ActiveConnection = mDataBase
End Sub

b. with SQL Server Authentication#

Private Sub OpenConnection2(pServer As String, pCatalog As String, pUser As String, pPsw As String)
    Call mDataBase.Open("Provider=SQLOLEDB;Initial Catalog=" & pCatalog & ";Data Source=" & pServer & ";Integrated Security=SSPI;User ID=" & pUser & ";Password=" & pPsw)
    mCmd.ActiveConnection = mDataBase
End Sub

Execute sql command

Private Sub ExecuteCmd(sql As String)
    mCmd.CommandText = sql
    Set mRS = mCmd.Execute
End Sub

Read data from record set

Private Sub ReadRS()
    Do While Not (mRS.EOF)
        Debug.Print "ShipperID: " & mRS.Fields("ShipperID").Value & " CompanyName: " & mRS.Fields("CompanyName").Value & " Phone: " & mRS.Fields("Phone").Value
        Call mRS.MoveNext
    Loop
End Sub

Close connection

Private Sub CloseConnection()
    Call mDataBase.Close
    Set mRS = Nothing
    Set mCmd = Nothing
    Set mDataBase = Nothing
End Sub

How to use it?

Public Sub Program()
    Call OpenConnection("ServerName", "NORTHWND")
    Call ExecuteCmd("INSERT INTO [NORTHWND].[dbo].[Shippers]([CompanyName],[Phone]) Values ('speedy shipping','(503) 555-1234')")
    Call ExecuteCmd("SELECT * FROM [NORTHWND].[dbo].[Shippers]")
    Call ReadRS
    Call CloseConnection
End Sub

Result

ShipperID: 1 CompanyName: Speedy Express Phone: (503) 555-9831

ShipperID: 2 CompanyName: United Package Phone: (503) 555-3199

ShipperID: 3 CompanyName: Federal Shipping Phone: (503) 555-9931

ShipperID: 4 CompanyName: speedy shipping Phone: (503) 555-1234

Public Sub InsertDBsss()
    Dim cmdstring As String
    Dim i  As Integer
 
 
    On Error GoTo Errhandler

    Set rs = New ADODB.Recordset
    Set Cmd = New ADODB.Command
 
 
    Dim cnn1 As ADODB.Connection
    Set cnn1 = New 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

    Server_Name = "M8404\SQLExpress" ' Enter your server name here
    Database_Name = "QC" ' Enter your database name here
    User_ID = "cim" ' enter your user ID here
    Password = "cim" ' Enter your password here

    cnn1.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
 

    'cnn1.Open
    Cmd.ActiveConnection = cnn1
 
    If cnn1.State <> 1 Then
 
    Call WriteToLog("connection error", System.FixPath, "FMCSLOG", True)
        Exit Sub
    End If
 
    cmdstring = ""
    Cmd.CommandText = "INSERT INTO [QC].[dbo].[TableT] ([id]) Values ('aa2')"
    Set rs = Cmd.Execute

    cnn1.Close
    Set rs = Nothing
    Set Cmd = Nothing
    Exit Sub

Errhandler:

Call WriteToLog(Err.Description, System.FixPath, "FMCSLOG", True)
Call WriteToLog("InsertLocalDBErr :" & Cmd.CommandText, System.FixPath, "FMCSLOG", True)
    Resume Next
 
End Sub