Adding Data to Access Table from Excel 2013 with ADODB (Problem 2)

[CarlRostron]

I have submitted problem 1 and this appears to be a similar issue (maybe the same I don't know) but I get a different error message from a different part of my code.

Again I am trying to add some data from Excel to Access but this time I receive an error message:

"Operation must use an updateable query" but I have tried the same text string in Access as a Query using the Query Builder and it has no issues at all.

This is the text string:
UPDATE tblSymbol SET tblSymbol.Beta = 1.6047 WHERE (((tblSymbol.SymbolCode)="LYB"));

Here is the code that runs this:

Sub AddBetaToDB()
On Error GoTo Err:
  Dim cn As ADODB.connection
  Dim Answer As String
  Dim sql
  Dim Exists As Integer
  Dim counter As Integer


  Set cn = New ADODB.connection


  cn.connectionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " & DBPath & ";Persist Security Info=False;"


  cn.Open


  sql = "UPDATE tblSymbol SET tblSymbol.Beta = " & Round(Range("BetaValue").Value, 4) & " " & _
        "WHERE (((tblSymbol.SymbolCode)='" & symbol & "'));"
  'MsgBox sql
  cn.Execute sql ('FAILS AT THIS LINE)


  cn.Close
  Set cn = Nothing


Exit Sub


Err:
  MsgBox Err.Description
  Exit Sub
End Sub

 

[james_lankford]

even though these solutions apply to ASP some of them also apply to desktop applications
https://support.microsoft.com/en-us/kb/175168

 

[CarlRostron]

Thanks for your response.

I have since looked at this again now and it appears that an existing Connection in the form of a Table generated from the Import Data from Access wizard was Exclusively locking the database. It was this reason that the update could not happen.

Having since removed the table and rerun the same code published here, it worked without a problem.

I hope this post may help other users.