0

I have done some investigation on this issue, but nothing works out. I am trying to change my PowerPivot connection. Im able to change Access source file, but on refresh it gives me an error, because the workbook(highlighted) connection string remains unchanged. See images.

PowerPivot wizard

Workbook connection string

I found this VBA code to change it manually, but it gives me 1004 error.

Sub NewConnection()

  ActiveWorkbook.Connections("Existing Connection name").OLEDBConnection.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\user\Desktop\Category Reports\Access DB\Access.accdb;Persist Security Info=false"

End Sub

I have tried many codes and none of them works. Please help!

grechill
  • 1
  • 1
  • Have you tried wrapping the source string in single quotes? I think the spaces would throw an error – AranDG Apr 15 '16 at 10:59
  • Single quotes? How? Give an example please – grechill Apr 15 '16 at 11:24
  • `ActiveWorkbook.Connections("Existing Connection name").OLEDBConnection.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\user\Desktop\Category Reports\Access DB\Access.accdb';Persist Security Info=false"` – AranDG Apr 15 '16 at 11:52
  • I don't see any difference between my code and yours. Anyway, it doesn't work =( – grechill Apr 15 '16 at 12:38

1 Answers1

0

Apparently Access data base was corrupt, but it was not easy to identify. I have managed to create new data base and re-link it to my excel worksheet via PowerPivot.

Works great!

grechill
  • 1
  • 1