I have an Excel workbook that builds a bunch of SQL Update scripts, and then executes them in SQL Server.
I got assistance with the below VBA script. The below works fine if I am running it while logged in as the Admin user Windows. However, when running from a users workstation I run into issues.
The main issue seems to be the user id and password are incorrect. I am not sure where on the below I can add the system administrator (sa) user name and password for SQL Server. Please may I get some assistance.
My code:
Sub test()
Const SERVER = "SRV\ServerName"
Const DATABASE = "Test Database"
Dim fso As Object, ts As Object, ar
Dim ws As Worksheet
Dim iLastRow As Long, i As Long
Dim sql As String, timestamp As String
Dim Folder As String, SQLfile As String, LOGfile As String
Dim t0 As String: t0 = Timer
' query file and log filenames
timestamp = Format(Now, "YYYYMMDD_HHMMSS")
Folder = "\\SRV\Test Folder\"
SQLfile = Folder & timestamp & ".sql"
LOGfile = Folder & timestamp & ".log"
Set fso = CreateObject("Scripting.FileSystemObject")
' read data from sheet into array to build sql file
Set ws = ThisWorkbook.Sheets("UDF Update")
iLastRow = ws.Cells(Rows.Count, "N").End(xlUp).Row
If iLastRow = 1 Then
MsgBox "No data in Column N", vbCritical
Exit Sub
End If
ar = ws.Range("N2").Resize(iLastRow - 1).Value2
' connect to server and run query
Dim sConn As String, conn, cmd, n As Long
sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
";Initial Catalog=" & DATABASE & _
";Trusted_Connection=yes;"
' open log file
Set ts = fso.CreateTextFile(LOGfile)
' make connection
Set conn = CreateObject("ADODB.Connection")
conn.Open sConn
' execute sql statements
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = conn
For i = 1 To UBound(ar)
ts.writeLine ar(i, 1)
.CommandText = ar(i, 1)
.Execute
On Error Resume Next
Next
End With
ts.Close
conn.Close
MsgBox UBound(ar) & " SQL queries completed (ADODB)", vbInformation, Format(Timer - t0, "0.0 secs")
End Sub
If you use Trusted_Connection=yes, the SQL server accepts/rejects you via Windows authentication. It seems that your admin account is accepted by the server while other accounts are not.
Either the other accounts are added to the database server by the database admin or you need to provide credentials and set Trusted_Connection=no (or omit it as that is the defaults)
sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
";Initial Catalog=" & DATABASE & _
";Trusted_Connection=no" & _
";User ID=MyUserID;Password=MyPassword;"