Search code examples
sqlexcelvbasharepoint

Update Excel internal tables using SQL

I administer a company system which I coded from scratch in Excel VBA. I use an Excel front-end, with a back-end database in .mdb format.
Note that although this is an Access-compatible database, my users do not use the MS Access application. The whole thing is currently hosted on our shared drive.

Our shared drive is being decommissioned, and we are moving to SharePoint. My first thought was to move the back-end database files onto SharePoint.

It seems impossible to establish a connection to a live database on a SharePoint location. I tried, and asked a question. The answer was that it was not possible, and I was advised to investigate SharePoint Lists.

I had limited success SharePoint Lists. I got stuck with trying to update individual records. My aim is to preserve as much of my existing code as possible. I use VBA to write SQL, usually in the form

cnn.execute
  • where cnn is an ADO connection

I discovered there was a way of using Excel's internal tables as if they were databases. How to run a SQL Query with VBA on Excel Spreadsheets Data

I can just about cope with this. I can use a simple SELECT query in the form

 sql = "SELECT * FROM [Sheet1$A1:E6]"

However now I need to know whether there is any way to UPDATE the existing data, using SQL commands. I searched online, but all the results relate to Update queries on SQL SERVER. I am not using SQL Server: I need to do this entirely with Excel, because of SharePoint limitations.

Solution

  • You will need the tables to have headers and some sort of unique identifier on each row, but instead of the SQL you have for the select just use an Update command. The thing to remember is that the "table" is the sheet name and the "columns" are the header titles e.g.

    sql = "Update [Sheet1$] SET COLB = 'X' WHERE UID = 2"
connection.Execute(sql)

    Incidentally - these are not "internal tables" of Excel that are being updated - just Worksheets