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
    otherWorkBook.Activate
    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
  
  
    connectionObject.Open
    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"
        cursor.Close
        connectionObject.Close
         Exit Sub
     End If
    
  
    row = 1
    column = 1
    For Each filed In cursor.Fields
        otherWorkBook.Cells(row, column).Value = filed.Name
        column = column + 1
    Next

    cursor.MoveFirst
    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
        Next
        row = row + 1
        cursor.MoveNext
    Loop
    cursor.Close
    connectionObject.Close
End Sub


What I have tried:

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

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;"
   
Comments
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