CODEWITHSUNDEEP
vbaexcelcrashadodb
Marcin Dramiński
Marcin Dramiński

Reputation: 13

Setting a connection to an access database in VBA crashes excel

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.

Upvotes: 1

Views: 1689

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 0

ISTech
ISTech

Reputation: 183

  • 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

Upvotes: 1

Related Questions