ADODB Named Pipes not connecting (1 Viewer)

tabitha

Registered User.
Local time
Today, 10:28
Joined
Apr 24, 2015
Messages
62
Hi, I'm trying to create a connection to my SQL back end from my Access front end using ADODB, and I've been unsuccessful so far.

I'm running SQL Express 2014 on Windows Server 2012, to Access 2013 on Windows 7,
Here is my code:

Code:
Private Function TestADODB()
 
Dim rs As ADODB.Recordset
Dim strSQL As String
 
strSQL = "Select ID from dbo_Insured Where FirstName = 'Tabitha'"
    If con.State = adStateClosed Then
        con.ConnectionString = "Provider=SQLNCLI11;" _
            & "SERVER=MyServer/SQLEXPRESS;" _
            & "Database=Life;" _
            & "UID=****;" _
            & "Pwd=****"

        con.Open            ''''''''This is where the error pops up
    End If
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = con
        If bolClientSide Then
            .CursorLocation = adUseClient
        Else
            .CursorLocation = adUseServer
        End If
        .CursorType = IIf((rrCursor = 0), adOpenStatic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockReadOnly, rrLock)
        .Open strSQL
        If .EOF And .BOF Then
            MsgBox "No records returned"
            'NoRecords = True
            Exit Function
        Else
            MsgBox .RecordCount
        End If
    End With
 
Set rs = Nothing
 
End Function
No matter what I do, I can't figure out what the problem is. The error I'm currently getting is "Run-time error -2147467259 Named Pipes Provider: Could not open a connection to SQL Server[67]"

Things I verified:
>Server & Browser services are both running
>All TCP/IP connects are enabled in SQL Server Configuration Manager and allowing port 1433
>Named Pipes is enabled
>Firewall exceptions have been created for both in-coming and out-going port 1433 on both server and client
>Remote connections are permitted on database
>Can ping server
>Have Microsoft ADO Ext 6.0 for DDL & Security enabled

I have this same db connected to Access currently and have been using it for weeks without an issue. I don't see how/why I'm unable to create this connection when I have an active connection otherwise.

What am I missing??

I got most of the code for this from stackoverflow.com/questions/11714285/how-to-show-recordset-data-in-ms-access-subform because I've never done a ADODB connection before, and I read that is the only way to create a subform that requery based on actions from the main form. If there's an easier way, I'm open to suggestions, but I should know why this isn't working anyway.
 
  • Like
Reactions: Rx_

SQL_Hell

SQL Server DBA
Local time
Today, 18:28
Joined
Dec 4, 2003
Messages
1,347
Hello,


I think you have the name of your server wrong:

It should be: MyServer\SQLEXPRESS

You have: MyServer/SQLEXPRESS
 

Users who are viewing this thread

Top Bottom