Method open of object _connection failed

Question

I am trying to connect to an SQL ce database within Excel. It keeps giving me the:

Method Open of object _connection failed

Error message whenever it gets to Conn.Open.

I've never used ADO before so I'm not sure what's going wrong.

Below is my connection script.

Sub SQLCeConnect()
Dim Conn As New ADODB.Connection
Dim Query As New ADODB.Command
Dim ConnStr As String
Dim RecordSet As New ADODB.RecordSet
Dim i As Integer

'the connection string
ConnStr = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=X:\ADOTEST\MYDB.sdf;"

'Open ADO Connection
Conn.ConnectionString = ConnStr
Conn.Open
Query.ActiveConnection = Conn
Query.CommandText = "SELECT * From DoorLayers"

Set RecordSet = Query.Execute
RecordSet.Close
Conn.Close
Conn.ConnectionString = ""

Do While Not RecordSet.EOF
    For i = 0 To RecordSet.Fields.Count - 1
        Debug.Print RecordSet.Fields(i).Name, RecordSet.Fields(i).Value
    Next
    RecordSet.MoveNext
Loop
RecordSet.Close

End Sub

connectionstrings.com/sql-server-ce

gembird
– gembird

2013-03-18 08:41:06 +00:00
Commented Mar 18, 2013 at 8:41 — gembird
– gembird, Commented Mar 18, 2013 at 8:41

Dick Kusleika · Accepted Answer · 2013-03-16 16:20:42Z

1

The is usually a bad connection string, but yours looks fine. The next most common cause is that you don't have the OLEDB provider drivers on your desktop. You can download it here

http://www.microsoft.com/en-us/download/details.aspx?id=5821

answered Mar 16, 2013 at 16:20

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joeb Over a year ago

Couldn't figure this out. I have the oledb provider drivers installed. I decided to abandon the vba approach. I am going to simply write vb.net dll and reference it in excel. Should make things much easier. Thanks for the help.

Randerson Maurilio · Accepted Answer · 2016-04-15 13:27:11Z

0

Are you closing the connection before use it on WHILE. You must close it after use the while instruction.

answered Apr 15, 2016 at 13:27

Randerson Maurilio

1

Collectives™ on Stack Overflow

Method open of object _connection failed

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related