1

I administer a company system which I coded myself 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, and it works as well as it can.

Unfortunately, our shared drive is being decommissioned, and we are moving to SharePoint. I am not a SharePoint expert. My first thought was to simply move the back-end database files onto SharePoint too - but it seems that it's impossible to establish a connection to a live database on a SharePoint location. I have tried, and even asked a question here about it. The answer was that it was not possible, and I was advised to investigate SharePoint Lists. I duly investigated SharePoint Lists, and had a limited amount of success with them. However I got stuck with trying to update individual records. You see, 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 then discovered - to my delight - that there was actually a way of using Excel's internal tables as if they were databases. I found that here: 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 have 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.

Grateful for any advice. Thank you.

Chris Melville
  • 1,476
  • 1
  • 14
  • 30

1 Answers1

1

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

CHill60
  • 1,180
  • 8
  • 14
  • Perfect - it worked! Thank you very much. I note that it's important to include the $ at the end of the sheet name. This solves a lot of things for me. – Chris Melville Apr 28 '23 at 20:53