ODBC Excel file access on Windows

I have connected successfully to a Excel file using the ODBC driver that came with Xojo.
I am successfully populating a listbox with the specific information I need from the Excel file.
When I try to update a cell I get this database error:
“The RecordSet cannot be edited because the uniqueness of its rows cannot be verified.”

Var sql As String = "SELECT `Tracking Information`,`POTran No` FROM [VOOR$] WHERE `POTran No` = '0000953636'"
Var rs As RowSet = db.SelectSQL(sql)

If rs <> Nil Then  
  Try
    rs.EditRow
    rs.Column("Tracking Information").StringValue = "TEST UPDATE"
    rs.SaveRow
  Catch error As DatabaseException
    MsgBox("DB Error: " + error.Message)
  End Try
  
End If

My question is how do I acquire the Primary Key of the row in the Excel file I am trying to update? The ‘POTran No’ column IS unique - as in there is a unique number in it in each row. How do I assign it as the primary key in this case?

Thank you for any ideas or assistance,
Michael

Hi Michael,
You should have a look at the office automation plugin that also comes with Xojo. You’ll need to copy it from the extra’s folder to the plugins folder & restart Xojo to use it. This will give you cell level access to the spreadsheet.

HTH
Wayne

Does it work if you take out the space in the column name, or replace it with an underscore for the primary column? Certainly MSACCESS can be finicky with spaces in names, excel maybe despite the quotes being used. Also ensure data sourced in ODBC manager is not set to Read Only.

I wrote an article for XDev back in issue 16.5 about using Excel as a database (albeit used mainly for searching/testing not updating). The best way to go however if on Windows is as suggested by Wayne and use the built in plugin. Also, those quotes (maybe be a translation/cut paste thing) don’t look correct. You can download the project and have a play (API 1).

http://www.xdevmag.com/browse/16.5/16503/

I also revisited my project and tried an update, but instead of using XOJO’s edit record command I used SQL and the update worked fine, something like this;

UPDATE [VOOR$] SET `Tracking Information` = 'TEST UPDATE' WHERE 'POTran No` = '0000953636'

Var sql As String
sql = "UPDATE [VOOR$] SET `Tracking Information` = 'TEST UPDATE' WHERE 'POTran No` = '0000953636'

Try
  db.ExecuteSQL(sql)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

Thank you, I got this working yesterday with this code (similar to your example)

Var sql As String = "UPDATE [VOOR$] SET [Tracking Information] = 'TEST UPDATE' WHERE [POTran No] = '0000953636'"

Try
  db.ExecuteSQL(sql)
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
End Try

1 Like