cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JP-Ronse Regular Visitor
Regular Visitor

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
Super User
Super User

Re: Save way to change the data source (Access database)

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

 

Would love to have seen the error.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
JP-Ronse Regular Visitor
Regular Visitor

Re: 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

 

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)