Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Records _ Populate Excel Records Using Access

Posted by: pduffer Nov 6 2019, 01:57 PM

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.

Posted by: theDBguy Nov 6 2019, 02:14 PM

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

Posted by: pduffer Nov 6 2019, 02:19 PM

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.

Posted by: GroverParkGeorge Nov 6 2019, 02:28 PM

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.

Posted by: pduffer Nov 6 2019, 02:30 PM

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

Posted by: projecttoday Nov 6 2019, 03:50 PM

QUOTE
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?

Posted by: pduffer Nov 6 2019, 03:57 PM

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.

Posted by: GroverParkGeorge Nov 6 2019, 04:00 PM

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?

Posted by: pduffer Nov 6 2019, 04:03 PM

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

Posted by: projecttoday Nov 6 2019, 04:11 PM

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

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

Posted by: pduffer Nov 6 2019, 04:15 PM

Link to Oracle data using Microsoft Power BI.

Posted by: theDBguy Nov 6 2019, 04:29 PM

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

Posted by: projecttoday Nov 6 2019, 04:44 PM

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.

Posted by: pduffer Nov 6 2019, 05:29 PM

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.

Posted by: projecttoday Nov 6 2019, 05:35 PM

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.)

Posted by: dmhzx Nov 6 2019, 06:38 PM

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.

Posted by: pduffer Nov 7 2019, 08:32 AM

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?)

Posted by: pduffer Nov 7 2019, 08:48 AM

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.

Posted by: projecttoday Nov 7 2019, 12:40 PM

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

Posted by: MadPiet Nov 7 2019, 12:41 PM

<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.


Posted by: pduffer Nov 7 2019, 12:55 PM

Yes, I'm familiar with both www.radacad.com and Guy in a Cube. Have used their helps often. I agree they are top notch!

I'm guessing it's the fact that Power BI and PowerPivot in Excel both use the same data engine that is causing the performance improvement I experienced via the tables in Excel instead of Access. Again, these are very small (less than 1,700 records) tables I have used thus far in these particular projects. But, it could also be how our internal network is structured. frown.gif

As I have time I'm going to build a report that will use test data tables. I will have the larger tables in both Access and Excel so I can link the same report to either one easily. Then I can see if the performance difference holds true with larger amounts of data.

Posted by: pduffer Nov 7 2019, 01:05 PM

QUOTE
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


I can't do the data manipulation required in Excel using an ODBC connection. At least I haven't done enough direct ODBC data retrieval in Excel to know how to handle that yet. My Access procedure uses multiple layers of queries with VBA processes included. As far as a stored procedure goes that gets back to the IT department knowledge and willingness, which are both in short supply right now.

Posted by: MadPiet Nov 7 2019, 01:12 PM

You can bypass Access entirely if you want. You would just create a connection to your data source(s), like your Oracle DB, and then you could call a stored procedure from it to return records. There are tons of options in PowerQuery. The upside is that since Excel will be using essentially an in-memory instance of Tabular, it will compress your data.

Posted by: pduffer Nov 7 2019, 01:26 PM

By a stored procedure in Oracle would that be like a view rather than a table? A coworker has built several Power BI reports that pull from a view, but those generally take the longest to refresh. I have always opted to pull directly from tables rather than views as I can get the data I need faster that way.

I've also experimented with using the SQL script from passthrough queries written in Access to pull in data. Maybe I just have more to learn, but my experience has been that I don't have an option to rename the source. For example, our ODBC connection is named "prod" and I can pull three or more different sets of data via a SQL script using the "prod" connection - and everyone will show "prod" as the name of the data source. So you wind up with numerous data source listings that are named the same, but only one will actually allow you to select the table(s) in "prod" you desire while the others will strictly bring in a defined set of records. That can cause a lot of confusion!

Posted by: MadPiet Nov 7 2019, 02:40 PM

I'm not totally sure about the permissions part (because I have SQL Server installed on my local machine), but I can write a query in the window if I want, and pull in just the data that will be returned by the query...

If you go to the Modeling tab, then Get Data, Database, Oracle...
Once you select the server, if you click the Advanced Options triangle, you can basically paste/type a query in there. If it works like the connection to SQL Server does, PowerBI passes the query back to the database to be executed, so you could paste something like

EXEC <schema>.<stored procedure name> param1, param2....

and it would execute that stored procedure/query and return the results to PowerBI.

Posted by: pduffer Nov 7 2019, 03:01 PM

I'm sure SQL Server would help but our IT dept said "no way" when we approached them about it. I think their mindset is that if we can produce reports using our current connections - and we can - then there's no need for them to deal with something else even if it makes our job and overall performance simpler/faster.

Posted by: MadPiet Nov 7 2019, 03:04 PM

That's too bad. They could do something super simple like create a schema, add a bunch of objects to it (views, stored procedures, etc), and then grant you access to that. Basically, only let you see and use objects that don't do any data modification. =(

You might have to make a case with them about how much time it would save you...

Posted by: pduffer Nov 7 2019, 03:13 PM

Thanks for the info and advice. I think that's going to have to be a battle for later. I know they are already overwhelmed with something that has to be fully implemented by March, so this will probably be pulled back into the forefront after that - if we haven't already come up with something else.