Setting a connection to an access database in VBA crashes excel

Question

This is the code I use to open a connection to an access database from excel. It used to work for more than a year.

Set dbname = New ADODB.Connection
theconnection = "//xxx.sharepoint.com/sites" & Application.PathSeparator & TARGET_DB
With dbname
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open theconnection
End With

By trial an error I've come to the conclusion that this line is causing the problem.

Set dbname= New ADODB.Connection

The problem began after an automatic update of my PC My Excel version 2016 MSO (16.0.7726.1036) 32-bit

Please let me know if you have run also into this problem, and if you know any fix or workaround.

ISTech · Accepted Answer · 2017-06-04 11:50:55Z

1

try to uncheck your 'ActiveX Data Objects' references and add them back:

Tools - References

or

use object to define a database:

 Dim dbname As Object
 Set dbname = CreateObject("ADODB.Connection")

or

if you create connection variable like this:

Dim con as New ADODB.Connection

change it to:

Dim con as ADODB.Connection
Set con = New ADODB.Connection

edited Jun 4, 2017 at 11:50

answered Jun 4, 2017 at 11:45

ISTech

1833 silver badges13 bronze badges

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

1 Comment

Marcin Dramiński Over a year ago

Thanks for the hints. The problem is solved. When I checked the References, I had Microsoft ActiveX Data Objects 6.0 Library checked. When I unchecked it, it disappeared. Looks like during some of the office 365 upgrades, it was changed for version 6.1. When I checked this version it began to work again. Thanks for your help again.

Dy.Lee · Accepted Answer · 2017-06-12 12:20:42Z

0

Maybe

Dim dbname As Object
Set dbname = CreateObject("ADODB.Connection")

theconnection = "//xxx.sharepoint.com/sites" & Application.PathSeparator & TARGET_DB
With dbname
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open theconnection
End With

I used like this , all code

Dim Rs As Object
Dim strConn As String
Dim i As Integer
Dim strSQL As String

strSQL = "select * from [table] "
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 12.0;"


Set Rs = CreateObject("ADODB.Recordset")
Rs.Open strSQL, strConn

If Not Rs.EOF Then
     With Ws
        .Range("a1").CurrentRegion.ClearContents
        For i = 0 To Rs.Fields.Count - 1
           .Cells(1, i + 1).Value = Rs.Fields(i).Name
        Next
        .Range("a" & 2).CopyFromRecordset Rs
    End With
End If
Rs.Close
Set Rs = Nothing

edited Jun 12, 2017 at 12:20

answered Jun 4, 2017 at 10:48

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

2 Comments

Marcin Dramiński Over a year ago

I tried this, but it didn't solve the problem. It was the Microsoft ActiveX library upgrade that created this problem. Thanks for helping.

Dy.Lee Over a year ago

@MarcinDramiński: I modified my answer, full code.

Collectives™ on Stack Overflow

Setting a connection to an access database in VBA crashes excel

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related