Can't connect to SQL server

Question

This one is a head scratcher. I am using VBA to try and connect to SQL server. Here is the code:

Sub GetDataFromADO()
'Declare variables'
Set mConn = New ADODB.Connection
Set mRS = New ADODB.Recordset
Dim strSQL As String

'Open Connection'
    mConn.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=PC-RYZEN;Data Source=TestDatabase;Integrated Security=SSPI;User ID=sa;Password=sa1234"
    mConn.Open

'Set and Excecute SQL Command'
    strSQL = "select * from TestData"

'Open Recordset'
    Set mRS.ActiveConnection = mConn
    mRS.Open strSQL

'Copy Data to Excel'
    ActiveSheet.Range("A1").CopyFromRecordset (mRS)

End Sub

I get the following error: Run-time error -214767259 [DBNETLIB][ConnectionOpen().]SQL Server does not exist or access denied

The name of the server from the above code is correct. The database and userID and password are correct.

I went into the SQL configuration manager and made sure that TCP/IP was enabled. I also enabled NamedPipes.

I went into Windows Firewall and advanced security to make sure PORT 1433 was open and all options checked.
I stopped and restarted the SQL service.

In the VBA project references, I am using Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library.

I still can't connect. Not sure what else to try. SQL server is version 15.0.2080.9

Answer 1

Maybe you should exchange the values of "Initial Catalog" and "Data Source".