Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JP-Ronse
Helper II
Helper II

Save way to change the data source (Access database)

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.

 

 

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

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

 

Would love to have seen the error.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.