ADODB Connection to SQL-Server via Excel

Question

I opened a similar question before with no avail on: 'https://stackoverflow.com/questions/58408918/vba-runtime-error-when-connection-to-sql-database

but since a few things have been tried, I thought I'd try again. The problem is with the "Open" command". I get a runtime error 80040e4d Error on login for the user 'XXXX'

I looked at a stack link with similar issue which unfortunately didn't help either: VBA Runtime Error when connection to SQL Database

I also tried usind the connection wizard in Excel which worked, so my connection data is seemingly correct. I wanted to use the connection string used by excel for my code in my modul,but the main difference of the provider being "Microsoft.Mashup.OleDb.1" didn't really change anything. Here is my code: '''

Sub DBCOnnectII()
   Dim cnConn As ADODB.Connection
   Set cnConn = New ADODB.Connection
   With cnConn
       .Provider = "SQLOLEDB.1"
       .CursorLocation = adUseClient
       .ConnectionTimeout = 0
       .Properties("Data Source").Value = "VMSQL19"
       .Properties("Password").Value = "XXXX"
       .Properties("User ID").Value = "XXXX"
       .Properties("Initial Catalog").Value = "AuswertungenTest"
       .Open
   End With
   End Sub

'''

Answer 1

Here is an example of Inert Into.

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=your_server_name"

'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL


'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

Here is an example of Select.

Sub ImportFromSQLServer()

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 = "your_server_name"
Database_Name = "your_db_name"
'User_ID = "******"
'Password = "****"

SQLStr = "select distinct ID from mytable1"

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";"

RS.Open SQLStr, Cn, adOpenStatic

    With Worksheets("Sheet1").Range("A1")
        .ClearContents
        .CopyFromRecordset RS
    End With

RS.Close
Set RS = Nothing
Cn.Close
Set Cn = Nothing

End Sub

Answer 2

I found out how complicated it is via this website, which gives all possible entries: https://www.connectionstrings.com/ole-db-driver-for-sql-server/

For my purposes with ADO, and a trusted connection( windows authenticated) it was this one:

con.Open "Provider=MSOLEDBSQL;Server=vmsql19;Database=XXXX;Trusted_Connection=yes;"

Answer 3

Here try this more simplified approach. Make sure to add the correct reference to use the ADO libraries.

Private Sub NonRecordset()
    Dim vbSql As String, cnnstr as String
    Dim cnn As ADODB.Connection
    Dim rs As New ADODB.Recordset

    vbSql = vbSql & "SQL STATEMENT" 
    Debug.Print ; vbSql
    Set cnn = New Connection
    cnnstr = "Provider=SQLOLEDB;Data Source=YOURSERVER;Initial Catalog=YOURDATABASE;User ID=USERNAME;Password=PASSWORD; Trusted_Connection=No"
    cnn.Open cnnstr
    ' cnn.Execute vbSql 'use this if just executing statement
    ' rs.Open vbSql, cnn 'use this if needing recordset
    ' if needing a recordset you'lll have to do something with said recordset:
    ' ThisWorkbook.Sheets("Sheet1").Range("A1").CopyFromRecordset rs
    
    cnn.Close
    Set cnn = Nothing
    
End Sub