Click here to Skip to main content
14,449,862 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have existing VBA code that I need to now convert to use SQL Server Windows Authentication without password. Below is my current; please advise how to fix it to execute please. I tried with no luck
Option Explicit
Dim database As String
Dim userName As String
Dim password As String

Sub QueryNAME()
    Dim database As String
    Dim connectionObject As ADODB.Connection
    Dim cursor As ADODB.Recordset
    Dim filed As ADODB.Field
    Dim theQuery As String
    Dim row As Long
    Dim column As Integer
    Dim theWorkBook As ThisWorkbook
    Dim otherWorkBook As Worksheet
     userName = Range("A2").Value
    password = Range("A3").Value
    database = Range("A1").Value
    Set otherWorkBook = ActiveWorkbook.Sheets.Add
    Set theWorkBook = ThisWorkbook
    Application.ScreenUpdating = False
    ActiveSheet.Name = database & "_" & Format(Date, "mmddyy") & " _" & Format(Time, "hhmmss")
    Set connectionObject = New ADODB.Connection
    Set cursor = New ADODB.Recordset
    theQuery = " use " & database & ";" & _
       "MY QUERY IS HERE "
    On Error Resume Next
    connectionObject.ConnectionString = "driver={SQL Server};" & _
    "server=myservername;uid=myusername;pwd=mypassword;database=" & database
    connectionObject.ConnectionTimeout = 30
    cursor.Open theQuery, connectionObject

    If cursor.EOF Then
        MsgBox "no matching records found, or you forgot to enter your user name and password in Cel A2, A3"
         Exit Sub
     End If
    row = 1
    column = 1
    For Each filed In cursor.Fields
        otherWorkBook.Cells(row, column).Value = filed.Name
        column = column + 1

    row = row + 1

    Do While Not cursor.EOF
        column = 1
        For Each filed In cursor.Fields
            otherWorkBook.Cells(row, column).Value = filed
            column = column + 1
        row = row + 1
End Sub

What I have tried:

connectionObject.ConnectionString = "driver={SQLOLEDB};" & _
    "Data Source=MySERVERNAME;Initial Catalog=database;Integrated Security=SSPI"
Updated 11-Feb-20 4:21am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

All you need to do then is to change the connection string, removing the username/password attributes and replacing with trusted_connection. Here is the general format:
"Provider=SQLOLEDB;Data Source=[Your DB Name];Trusted_connection=yes;"
Maciej Los 11-Feb-20 14:24pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100