DonLeverton
Programmer
thread702-1726000
I am using Access 2016 and DAO, so the function(s) from this reference won't work.
I'm thinking that it might work if I add a reference to ADO, but I'd rather not ... and just stick with DAO if possible.
================== Further Explanation ========================
In a nutshell:
I have an Access Web App (ACCDW) which was new format staring with Office365 (2013)
It is stored on my Sharepoint 2013 Team Site which uses Azure SQL as a data warehouse.
I have also designed a "hybrid" Access Web App that has a read/write "reporting database" which is an Access 2016 Desktop ACCDB
Here's my issue:
The data from our ERP (accounting) software exports data in Excel format, and of course some of it changes (is appended) daily.
Specifically, Inventory Transactions ... but there are more eg Purchase Orders)
This Excel data can be exported from the ERP software in one of 2 ways.
1.) Using an IBM program which is pretty old, and creates .XLS files. (Yuck)
2.) I recently discovered that I can use PowerQuery which has a number of advantages ... definitely becoming the preferred method.
I know what you might be thinking. "Eliminate the Excel export and link the ERP data directly to Access via ODBC", right?
Not an option here, as my IT guy won't allow me to have MS Access installed on my work PC.
So Excel it is then.
I have the Excel tables linked to my desktop app, (linked Excel data is not supported in the Web App)
I can write append queries that will update the linked tables, and carry it forward to the web app, but it's god-awful slow.
So what I'm thinking is that I could append a LOCAL table (MUCH faster) and then replace the AWA linked table with this new local table.
The code from the quoted link above seems to do just that, except for the ADO/DAO issue.
TIA for reading this, and any ideas are welcome!
Thanks,
Don
I am using Access 2016 and DAO, so the function(s) from this reference won't work.
I'm thinking that it might work if I add a reference to ADO, but I'd rather not ... and just stick with DAO if possible.
================== Further Explanation ========================
In a nutshell:
I have an Access Web App (ACCDW) which was new format staring with Office365 (2013)
It is stored on my Sharepoint 2013 Team Site which uses Azure SQL as a data warehouse.
I have also designed a "hybrid" Access Web App that has a read/write "reporting database" which is an Access 2016 Desktop ACCDB
Here's my issue:
The data from our ERP (accounting) software exports data in Excel format, and of course some of it changes (is appended) daily.
Specifically, Inventory Transactions ... but there are more eg Purchase Orders)
This Excel data can be exported from the ERP software in one of 2 ways.
1.) Using an IBM program which is pretty old, and creates .XLS files. (Yuck)
2.) I recently discovered that I can use PowerQuery which has a number of advantages ... definitely becoming the preferred method.
I know what you might be thinking. "Eliminate the Excel export and link the ERP data directly to Access via ODBC", right?
Not an option here, as my IT guy won't allow me to have MS Access installed on my work PC.
So Excel it is then.
I have the Excel tables linked to my desktop app, (linked Excel data is not supported in the Web App)
I can write append queries that will update the linked tables, and carry it forward to the web app, but it's god-awful slow.
So what I'm thinking is that I could append a LOCAL table (MUCH faster) and then replace the AWA linked table with this new local table.
The code from the quoted link above seems to do just that, except for the ADO/DAO issue.
TIA for reading this, and any ideas are welcome!
Thanks,
Don