[X]My Assistant

[pduffer]

Whenever I link to an Excel spreadsheet in Access it is always read only. Is there a way to append/delete/edit Excel spreadsheets directly from Access?

We are transforming the bulk of our reporting from Microsoft Access to Power BI. Our data is in an Oracle database. Sometimes the needed data transformations for reporting are too complex to be performed by Power BI. Another issue is that we want to populate data tables with a year's worth of data at the end of the year so it can be used for reporting and doesn't change. We link to these already prepared tables instead of having to repeat the data transformations so it makes everything run faster. Currently entire copies of our Oracle database are saved 3 times a year for reporting purposes. However, everything is moving to the cloud by Spring 2020 and the IT people tell us these "frozen" copies will no longer be available. So it is imperative we be able to prepare unchanging tables for reporting two or three times a year that we can store on our server. I might also mention that our IT people are completely overwhelmed by the whole moving to the cloud process and seem to be paranoid when it comes to security to the extent that some highly recommended SQL server type applications will not even be considered. They also don't allow direct communication between our department and the consultants being used, and don't personally understand the issues we have enough to tell the consultants what is needed and what problems are being encountered. Sounds like fun, right? But they are in control!

We have learned that reports linked to an Excel worksheet run much faster than when linked to Access data tables containing the same records - as in 100+ times faster or more. Therefore, we would like to skip populating the Access tables and directly populate Excel worksheets instead if that is possible. If not, I guess we could export the tables from Access to Excel after they are populated, but that seems like extra work.

Any help or advice is greatly appreciated.

[theDBguy]

Hi. You can manipulate Excel sheets through automation. Have you considered doing that? Just curious...

[pduffer]

I'm not sure I follow exactly what you mean. I have written processes before which created fairly extensive spreadsheets using Access, but they were all built from scratch and took quite a bit of trial and error to get worked out initially. That's why I was thinking if I could simply link to spreadsheet(s) instead of tables and populate them once the transformations are completed it would be less time consuming.

[GroverParkGeorge]

It would be helpful if you COULD directly manipulate (add, update, delete) data in linked Excel files, but that's not possible. There was a time when it was different, but Microsoft lost a lawsuit many years ago and had to disable that capability. To my knowledge, it's never been restored. Automation is your most effective avenue, albeit less handy.

[pduffer]

Thanks. That's what I thought I would learn but was holding out hope!

[projecttoday]

We have learned that reports linked to an Excel worksheet run much faster than when linked to Access data tables containing the same records - as in 100+ times faster or more.

Where did you learn that?

Are you saying that your idea was to enter data into Excel manually via Access and report on that, in effect using Excel as your back-end?

[pduffer]

We learned it by trial and effect. Had a couple of reports that linked to small data tables in an Access database (in addition to Oracle). Got time out errors frequently when refreshing. Linked them to a spreadsheet with the same data in worksheets and it just zipped right along.

Yes, we would in effect use Excel as the back end instead of Access for these particular reports. (The bulk of our reports link directly to Oracle.) The Access database and Excel spreadsheet were stored in the same folder.
This post has been edited by pduffer: Nov 6 2019, 03:58 PM

[GroverParkGeorge]

small data tables in an Access database (in addition to Oracle).

You were not trying to use queries that joined local Access tables to the remote Oracle tables, by any chance, were you?

[pduffer]

Yes, you can do that in Power BI. But works better (faster in this case anyway) joining Excel with Oracle.

[projecttoday]

There must have been something wrong with the Access-backend version.

The bulk of your reports link directly to Oracle? From what?

[pduffer]

Link to Oracle data using Microsoft Power BI.

[theDBguy]

Hi. Glad to see others have jumped in while I was away to explain what I meant by "automation." Good luck with your project.

[projecttoday]

So you basically have an Oracle database which you report on with Power BI. But Power BI is inadequate for some reports. So you've been using Access linked to an Excel backend containing report-prepped data. You say the Excel backend is much faster than an Access backend. What you don't like about this is that you can't update the Excel tables. (Why do you need to update these tables? How did they get created in the first place?) And it all goes to the cloud next year.

I have done some fairly complex reports using Access as a frontend with pass-through queries to a DB2 database (similar to Oracle). This was achieved with temporary tables. There is no need for manual modification of the reporting data. On one long-term report, I keep past transaction data in the Access frontend so as not to have to read it again. New data are appended daily. But the database has the ability to re-read all the data if necessary so there's no permanent store of "calculated" reporting data or un-normalized data anywhere.

Excel is used for output, along with Access forms and reports.

[pduffer]

No, we haven't been using Excel for the reports but want to try using it to store the data files prepared by Access to see if it works smoother based on experiences with a few reports. Not sure why the ones we've tried thus far read Excel data easier as everything is structured the same, just in worksheets rather than Access tables. These are very small tables of data, chiefly used for some reporting structure. Since other projects use the same tables we don't just use internal tables as they may change.

[projecttoday]

There must be something wrong. Excel is not a more-robust database than Access.

Is Power BI capable of producing the reports you want and you just don't like the wait or is it incapable? (I'm not familiar with Power BI.)

[dmhzx]

I've worked in places where they have IT departments like that. Not only do they not understand the business, they don't understand IT very much.

They tend to follow the latest trends regardless of what the business might want.

There is no such thing a 'cloud' storage. All data is stored on physical media. The difference is that you have no idea where your data actually is, or who can access it if it's in 'the cloud', and if the internet goes down, work stops.

Anyway:

If the basic requirement is for a copy of the data to be frozen three times a year , why not make a copy of it in Access, where you can do whatever transforms you need with VBA and UDFs if necessary. If you want to export to Excel you can open Excel from within Access, and send over whatever you want.

CopyFromRecordset is pretty good most of the time. It does have a few really irritating quirks, and you may need to reformat whole columns afterwards, but you can do whatever you want to a spreadsheet from within Access.

Including adding or deleting or changing rows or cells.

Access can of course link directly to Oracle if you have an ODBC driver for it.

What I've done in the past if I want a specific data snapshot to be available is to copy the entire Access back end to a saved location as an "as at" version.

Not sure if that's any help.

[pduffer]

Yes, we are set up to pull the data from Oracle using an Access database and populate a back end database for each time that we want to capture frozen data. We've been doing that for a while and then using that back end for various reports. I just thought if Power BI would be able to read the back end data from Excel faster than from Access I could save it in Excel to function as the back end rather than the current Access method.

Of course, it's entirely possible that there's just a network setting that is causing the slowdown. My supervisor (our former IT head who actually HAS the ability to structure things right, just not the permissions anymore) discovered yesterday that the problem with some reports timing out was due to the fact IT had them going through the firewall which was dropping the connection from time to time. (Not sure why OUR process using data on OUR servers has to route through OUR firewall in order to refresh a report. But then again, that's just what we deal with now. Did I mention paranoia?)

[pduffer]

Robert - Power BI is a very capable and robust reporting tool. If you have ever worked with power query in Excel, you have worked with part of what is included in Power BI. It can use a myriad of data sources - Excel, Text/CSV, XML, JSON, PDF, SQL Server, Access, Oracle, IBM Db2, IB Informix, IBM Netezza, MySQL, PostgreSQL, Sybase, SAP Business, Amazon Redshift, Azure, and about 3 to 4 times more sources than I listed.

[projecttoday]

Keep looking. Are companies switching their backend data from Access to Excel for better performance? I don't think so.

[MadPiet]

<snip>
Therefore, we would like to skip populating the Access tables and directly populate Excel worksheets instead if that is possible.
</snip>

You can create connections in an Excel workbook, and then create queries that grab data from wherever you want. One thing you could do is call a stored procedure that's in your Oracle DB to return a dataset that you want to work with. And then if you need to transform it more before importing, you can transform your data using
I was a SQL Saturday a few weeks ago, and watched Reza Rad do some really amazing stuff with PowerQuery. He has an evolving book on PowerBI/PowerQuery that's well worth reading - his stuff is great. (www.radacad.com) It's advanced though.

Another book you may benefit from is Gil Raviv's book "Collect, Combine, and Transform Data Using Power Query in Excel and Power BI". (Or dig around on YouTube and see if Adam & Patrick cover what you're looking for … look for "GuyInACube" - they're both MSFT employees now. I've met them both and they REALLY know their stuff.)

The upside to skipping Access is that PowerBI and the PowerPivot stuff in Excel both use the same data engine, which is basically an in-memory Analysis Services Tabular instance. Since it uses Columnstore, it can compress large datasets (depending on the uniqueness of a column's values).

Sorry for the over the river and through the woods answer, but there are a bunch of resources out there, if you know where to look. And if you get stuck, Twitter is awesome.