Save way to change the data source (Access database)

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