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:
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.
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
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.