Adding Data to Access Table from Excel 2013 with ADODB

[CarlRostron]

I recently upgraded to office 2013 and my code has since stopped working so wondered if anyone could help me please?

I am simply trying to add data to an Access table using the rs.AddNew method.

Below is some of the code.

Sub AddSymbol(someSymbol As String)
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim rs2 As ADODB.Recordset
  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 = " & DB_LOCATION


  cn.Open


  Set rs = New ADODB.Recordset
  rs.Open "tblSymbol", cn, adOpenDynamic, adLockOptimistic, adCmdTable

  rs.AddNew (--CODE FAILS AT THIS LINE--)
  rs.Fields("SymbolName") = ActiveCell.Value
  rs.Fields("SymbolCode") = ActiveCell.Offset(0, 1).Value
  rs.Fields("AssetClassID") = 1
  rs.Fields("CurrencyID") = 64
  rs.Fields("ExchangeID") = 129
  rs.Fields("MonthID") = 11
  rs.Fields("DateAdded") = Date
  rs.Fields("IndustryID") = 7444
  
  rs.Update

 

[CarlRostron]

I should have added that the error message I recive is Runtime error 3251 Current record set does not support updating.

 

[Micron]

Well, no takers yet? I'm no expert on connections, but I'd try putting adLockOptimistic last. Probably not the anwer, but it seems to be more popular. Might also msgbox rs.recordcount just to be sure there are records to update. In fact, I'd put that qualifier in just in case the recordset is empty, unless that's normal anyway. The only other thing is maybe there is an issue with Trusted Locations after the Office update? Can you try this on other pc's or older Office version to see if it still works?

P.S. Exists is a reserved word.

 

[Kfrazie1]

Do you have the access file on a share drive? I had this issue when permissions were not set correctly on the shared folder.