Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert this ADO to DAO? (Delete / Recreate ODBC tables in Office 365 Access Web App)

Status
Not open for further replies.

DonLeverton

Programmer
Aug 22, 2016
1
CA
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'd use ADO & reassign the Connection string.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top