Can't connect to SQL server

Gary Agoura 201 Reputation points
2022-01-20T16:19:29.267+00:00

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

{count} votes

Accepted answer
  1. Viorel 113.8K Reputation points
    2022-01-21T14:52:56.133+00:00

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

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful