Save way to change the data source (Access database)

[JP-Ronse]

Hi All,

This afternoon I created a report using PQ & PP (Excel 2013), everything was working fine but then I had to change the source file name as I was testing on an off-line database and not the production db.

The queried tables are in both databases the same. (I am the only developer of the databases and hence sure that the same tables with the same structure exist in both.)

So I thought, piece of cake, change the name in the advanced editor.

From:

let
    Source = Access.Database(File.Contents("C:\Users\id980231\Documents\Zone_1\ROC KPI v2.6.accdb")),

To:

let
    Source = Access.Database(File.Contents("C:\Users\id980231\Documents\Zone_1\ROC KPI v2.7.accdb")),

And it failed to load, I didn't note down the full error but it said to disable and re-enable the connection or something like that. I tried that but lost my all my pivot tables.

This is not dramatic as it were only 2 pivot tables but would like to know if it can be avoided.

The department is ISO 27K certified and versioning is an important matter in this, so each time I make a modification to the database I've to change the version number.

Kind regards,

JP Ronse

P.S. Trying to solve real questions is the best school.

[Greg_Deckler]

You might try creating a query parameter and substiting that parameter in place of your file path. 

Would love to have seen the error.

[JP-Ronse]

Hi,

This is what I get (sending a frown):

Feedback Type:
Frown (Error)

Error Message:
We couldn't refresh the table 'tbl_Clarify_All' from connection 'Query - tbl_Clarify_All'. Here's the error message we got:

Out of line object 'DataSource', referring to ID(s) 'f7bb74f0-9474-4744-a352-646ad0fb8a93', has been specified but has not been used.

Formulas:


section Section1;

shared tbl_Clarify_All = let
Source = Access.Database(File.Contents("C:\Users\id980231\Documents\Zone_1\ROC KPI v2.7.accdb")),
_tbl_Clarify_All = Source{[Schema="",Item="tbl_Clarify_All"]}[Data]
in
_tbl_Clarify_All;

shared tbl_ROC = let
Source = Access.Database(File.Contents("C:\Users\id980231\Documents\Zone_1\ROC KPI v2.6.accdb")),
_tbl_ROC = Source{[Schema="",Item="tbl_ROC"]}[Data]
in
_tbl_ROC;

Stack Trace:
Microsoft.Mashup.Client.Excel.Com.ComWrapperException: We couldn't refresh the table 'tbl_Clarify_All' from connection 'Query - tbl_Clarify_All'. Here's the error message we got:

Out of line object 'DataSource', referring to ID(s) 'f7bb74f0-9474-4744-a352-646ad0fb8a93', has been specified but has not been used.
OLE DB or ODBC error: The query 'tbl_Clarify_All' or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query..
---> System.Runtime.InteropServices.COMException: We couldn't refresh the table 'tbl_Clarify_All' from connection 'Query - tbl_Clarify_All'. Here's the error message we got:

Out of line object 'DataSource', referring to ID(s) 'f7bb74f0-9474-4744-a352-646ad0fb8a93', has been specified but has not been used.
OLE DB or ODBC error: The query 'tbl_Clarify_All' or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query..

--- End of inner exception stack trace ---
at Microsoft.Mashup.Client.Excel.Com.ComWrapper.InvokeMember(String memberName, BindingFlags bindingFlags, Object[] args)
at Microsoft.Mashup.Client.Excel.Com.ModelTable.Refresh()
at Microsoft.Mashup.Client.Excel.Com.SafeCom.TryRefresh(Action action)
at Microsoft.Mashup.Client.Excel.Shim.AddInModelTable.TryRefresh()
at Microsoft.Mashup.Client.Excel.ConnectionManager.<>c__DisplayClass16.b__15()
at Microsoft.Mashup.Client.Excel.Com.SafeCom.Invoke[T](Func`1 func)

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Host.Document.ExceptionExtensions.InitializeInvocationStackTrace(Exception e)
at Microsoft.Mashup.Client.Excel.Com.SafeCom.Invoke[T](Func`1 func)
at Microsoft.Mashup.Client.Excel.Shim.AddInSafeInvoker.Invoke[T](Func`1 func)
at Microsoft.Mashup.Client.Excel.ConnectionManager.RefreshConnection(IWorkbook workbook, String queryName)
at Microsoft.Mashup.Client.Excel.FillManager.StartRefreshFill(IWin32Window ownerWindow, IWorkbook workbook, Query query)
at Microsoft.Mashup.Client.Excel.ExcelQueryServices.<>c__DisplayClassc.<>c__DisplayClasse.b__b()
at Microsoft.Mashup.Client.Excel.Shim.IUndoServicesExtensions.<>c__DisplayClass1.b__0(IUndoScope undoScope)
at Microsoft.Mashup.Client.Excel.Shim.AddInUndoServices.InvokeUndoableAction(IWorkbook workbook, UndoableActionType actionType, Action`1 action)
at Microsoft.Mashup.Client.Excel.Shim.IUndoServicesExtensions.InvokeUndoableAction(IUndoServices undoServices, IWorkbook workbook, UndoableActionType actionType, Action action)
at Microsoft.Mashup.Client.Excel.ExcelQueryServices.<>c__DisplayClassc.b__a(IWorkbook workbook)
at Microsoft.Mashup.Client.Excel.ExcelQueryServices.TryInvokeOnWorkbookEnsureConnections(Action`1 action)
at Microsoft.Mashup.Client.Excel.ExcelQueryServices.StartRefreshFill(Query query)
at Microsoft.Mashup.Client.Excel.QueriesTaskPaneControl.RefreshQueries(Query[] selectedQueries)
at Microsoft.Mashup.Client.Excel.QueriesTaskPaneControl.OnRefreshQueryContextMenuButtonClick(Query[] selectedQueries)
at Microsoft.Mashup.Client.ClientShared.Model.BaseQueryContextMenuProvider.<>c__DisplayClass6.b__3()
at Microsoft.Mashup.Client.ClientShared.ContextMenuRenderer.<>c__DisplayClass7.b__3()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.Mashup.Client.ClientShared.ContextMenuRenderer.<>c__DisplayClass7.b__2(Object sender, EventArgs eventArgs)
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I don't know how to create a query parameter and I am going in retirement next year. My colleagues never heard about PQ, PP, VBA hence I wan to make their life as easy as possible after my retirement.

Kind regards,

JP

OLE DB or ODBC error: The query 'tbl_Clarify_All' or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query..


Timestamp:
2018-01-15T18:18:59.0242229Z

Product Version:
2.26.4128.242 (Release-V2-Public) (x86)

Excel Version:
15.0.4981.1000

Excel Install Location:
C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE

Supports Premium Content:
True

IE Version:
11.0.9600.18860

OS Version:
Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 en-US)

CLR Version:
4.0.30319.42000

User ID:
38d15631-a938-4e1d-8183-ebbae2691fb6

Workbook Package Info:
1* - en-GB, fastCombine: Disabled.

Working set:
470 MB

Peak Virtual Memory:
1.51 GB