Importing Excel data into an Azure SQL server via Access
Hello All
I have a database which is split with the frontend (in Access 2010) and the backend on an SQL server in the cloud on Azure. Previous to this the database was completely on a server in house on Access 2010 only.
The old database ran a vba procedure which would take a range from an excel spreadsheet (the columns were always A-C but the rows could change) and import the data into a table.
I do not know how to import the excel info from the excel spreadsheet into a table on the Azure machine via a form on Access. Ideally I would like the user to see a form, press an import button, and the correct excel range would import into the table on the server. Then I could manipulate the data as I needed to.
In the old database I used DoCmd.TransferSpreadsheet acImport (Actually I had to run this chunk of code in a loop because the only way to tell I got to the bottom of the data was that the word TOTAL would appear in column A)
Any help is really appreciated.
Andy
I have a database which is split with the frontend (in Access 2010) and the backend on an SQL server in the cloud on Azure. Previous to this the database was completely on a server in house on Access 2010 only.
The old database ran a vba procedure which would take a range from an excel spreadsheet (the columns were always A-C but the rows could change) and import the data into a table.
I do not know how to import the excel info from the excel spreadsheet into a table on the Azure machine via a form on Access. Ideally I would like the user to see a form, press an import button, and the correct excel range would import into the table on the server. Then I could manipulate the data as I needed to.
In the old database I used DoCmd.TransferSpreadsheet acImport (Actually I had to run this chunk of code in a loop because the only way to tell I got to the bottom of the data was that the word TOTAL would appear in column A)
Any help is really appreciated.
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The beauty of ODBC is it shelters the Access app from whatever BE it is connected to so it doesn't much matter whether it is Azure, Oracle, Pervasive, or anything else. ODBC is not 100% since there are still quirks (mostly due to the way each driver is implemented) but in general, the BE doesn't matter.
ASKER
It looks like I was just overthinking it. Looks like it is working. If something goes wrong I will post again (Azure has tricked me in the past by looking like it was working :D)
ASKER
Thanks to all!
Andy