How do I write to SQL table using excel VBA

Question

I have a SQL table called Audit. There are two fields in this table called UN and CN. My server name is analive and DB is DW_ALL. I am trying to capture in excel the username and computer name that accesses/opens my workbook or sheet then write that audit information to my SQL table.

Sub UpdateTable()

Dim cnn As ADODB.Connection
Dim uSQL As String
Dim strText As String
Dim strDate As Date

strText = ActiveSheet.Range("b4").Value
''strDate = Format(ActiveSheet.Range("c4").Value, "dd/mm/yyyy")''

Set cnn = New Connection
cnnstr = "Provider=SQLOLEDB; " & _
        "Data Source=icl-analive; " & _
        "Initial Catalog=DW_ALL;" & _
        "User ID=ccataldo;" & _
        "Trusted_Connection=Yes;"

cnn.Open cnnstr

''uSQL = "INSERT INTO tbl_ExcelUpdate (CellText,CellDate) VALUES ('" & strText & "', " & strDate & ")"''
''uSQL = "INSERT INTO Audit (UN,CN) VALUES (MsgBox Environ("username"), MsgBox         Environ("username""''
uSQL = INSERT INTO Audit (UN,CN) VALUES ('MsgBox Environ("username") ', 'MsgBox Environ("username"'))

Debug.Print uSQL

cnn.Execute uSQL
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

Re-reading your post; I'm no longer sure I've correctly understood your issue. Can you post the error code, and tell us which line is responsible. — David Rushton, Nov 15 '16 at 15:25
What is the error? Are you not going to be needing to hard code a user/pass if the workbook is to be shared with others? If so, don't forget to lock out the macros so they aren't readable -- but that's not unbreakable! https://stackoverflow.com/questions/272503/how-do-i-remove-the-password-from-a-vba-project — Dave C, Nov 15 '16 at 16:04
Also, your best bet would be to create a sql account to use, and give it NO permissions, except execute on a stored proc which does the insert for you. — Dave C, Nov 15 '16 at 16:06

score 0 · Answer 1 · answered Nov 15 '16 at 15:24

Connection strings can be tricky things. I rely heavily on ConnectionStrings.com to refresh my memory.

Trusted_Connection and User ID are mutually exclusive. Use trusted connection when you want to log onto SQL Server using your Windows account. Username and password are for logging in with a SQL account.

Assuming you want to use your Windows login; try this:

Provider=SQLNCLI11;Server=analive;Database=DW_ALL;Trusted_Connection=yes;

score 0 · Answer 2 · answered Nov 15 '16 at 16:23

Here is a sample script that writes to an AccessDB. The SQL Should be similar as well as the needed vba statements. I hope it helps

Also it uses DAO and not Addob connection type.

Private Sub thisbetheshitmane()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim tb As DAO.TableDef
    Dim vAr As String
    Dim i As Integer
    Dim y As Integer
    Dim InCombined As Boolean
    Dim InOpen As Boolean
    Dim vbSql As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Dim StartTime As Double
    Dim SecondsElapsed As Double

    StartTime = Timer

    Set db = DBEngine.OpenDatabase("C:\Users\dzcoats\Documents\Microsoft.accdb")

    For Each tb In db.TableDefs
        If Len(tb.Connect) > 0 Then
            tb.RefreshLink
        End If
    Next tb     

    Set rst = db.OpenRecordset("SELECT DISTINCT [Table_Name].Defect FROM [Table_Name] WHERE [Table_Name].Defect IS NOT NULL;")

    Dim QResult() As Variant
    QResult = rst.GetRows(rst.RecordCount)
    For a = LBound(QResult, 2) To UBound(QResult, 2)
        vAr = QResult(0, a)
    Next a

    For y = LBound(QResult, 2) To UBound(QResult, 2)
        If vAr <> "Defect" And vAr <> vbNullString And vAr <> "" Then

            If InCombined = True And InOpen = True Then
                vbSql = "UPDATE [Table_Name] SET [Table_Name].Status ='Bad Defect Number' WHERE ((([Table_Name].Defect)='" & vAr & "'));"
                db.Execute vbSql
            End If

            If InCombined = False And InOpen = True Then
                vbSql = "UPDATE [Table_Name] SET [Table_Name].Status ='Completed' WHERE ((([Table_Name].Defect)='" & vAr & "'));"
                db.Execute vbSql
            End If

        End If
    Next y

    rst.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

How do I write to SQL table using excel VBA

2 Answers2