0

Getting error as Run-time error '-2147467259(800004005)': Automation error Unspecified error in the rs.update statment.

New to Excel Macro and doing it for the first time. Please help

Below is the code used

Sub dbconnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

   Dim rs As New ADODB.Recordset
   Dim sqlstr As String
   Dim strconn As String

   strconn = "Driver={SQL Server};Server=testing;Database=testdb;UID=sa;PWD=s123*"
   cn.Open strconn

   sqlstr = "select * from testing124(nolock)"
   rs.Open sqlstr, strconn, adOpenDynamic, adLockOptimistic
   rs.AddNew
   rs!ID = Cells(2, "a").Value
   rs!remarks = Cells(3, "a").Value
   rs.Update
   rs.Close
   cn.Close
   Set cn = Nothing
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
SNK
  • 33
  • 5
  • 1
    `rs!ID` and `rs!remarks` is no valid syntax. Please check and make sure you show **exactly** the code you are using in Excel. Don't re-type it always copy/paste. – Pᴇʜ Sep 09 '19 at 08:24
  • Using an empty connection string ( strconn = "" ) I get the same error! Is the driver installed? Are the parameters correct? (see [https://www.connectionstrings.com/](https://www.connectionstrings.com/)) Debug your code by steping into it! Create a code sample were you establish the connection and only read values from the database! – simple-solution Sep 09 '19 at 09:02
  • @Peh, [actually they are valid](https://stackoverflow.com/a/15958981/445425) – chris neilsen Sep 09 '19 at 09:44
  • Connection String is valid and am able to read values from the database.What else needs to be checked? Please help. – SNK Sep 09 '19 at 10:11
  • @chrisneilsen huh, thanks for the link. Never came across that before :o – Pᴇʜ Sep 09 '19 at 10:19

1 Answers1

0

I did create an empty MS Access database at C:\temp\test.accdb and a table "testtable" with some sample entries. The below code did read this sample datasets into the excel worksheet after referencing "Microsoft ActiveX Data Objects 2.8 Library" (Tools -> References):

Option Explicit

Sub dbconnection()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

    Dim rs As New ADODB.Recordset
    Dim sqlstr As String
    Dim strconn As String

    strconn = "DSN=MS Access Database;DBQ=C:\temp\test.accdb;"
    cn.Open strconn

    sqlstr = "select * from testtable"
    rs.Open sqlstr, cn

    ActiveSheet.Range("A1").CopyFromRecordset rs

    rs.Close
    cn.Close
    Set cn = Nothing

End Sub
simple-solution
  • 1,109
  • 1
  • 6
  • 13
  • Am able to read the values from database but insertion of new row is throwing that error. Please help. – SNK Sep 09 '19 at 10:37
  • The statment `Cells(2, "a").Value` makes no sense! `Cells(1,1).value` is the same as `Range("A1").value` – simple-solution Sep 09 '19 at 11:50
  • How is the field "ID" defined in your database? Does this field allow a value? – simple-solution Sep 09 '19 at 11:51
  • On which line do you get the error? rs.open? rs.AddNew? rs.Update? – simple-solution Sep 09 '19 at 11:52
  • Error is thrown in rs.Update line – SNK Sep 09 '19 at 12:19
  • Try to skip the "rs!ID" statement! Does it work without? – simple-solution Sep 09 '19 at 12:21
  • This is the table script – SNK Sep 09 '19 at 12:22
  • Even after skipping Try to skip the "rs!ID" statement! Does it work without? – simple-solution 1 min ago it is throwing same error on same line. – SNK Sep 09 '19 at 12:23
  • Try to create an acceptable/accepted dataset directly in vba: `rs!ID = 1000 : rs!remarks = "remark"`. Or review / watch the content of the recordset variable rs directly prior to the update command! vba might allow a data value which is refused by your database. – simple-solution Sep 09 '19 at 12:32
  • Tried hardcoding the values as well but same error is being thrown. Also in the immediate window using Debug.Print rs(0) & Debug.Print rs(1) the value is getting printed correctly. Please help. – SNK Sep 09 '19 at 12:36
  • You might not be able to distinguish a string !! value of ID from a number value! The database will expect a number for rs!ID (and not a string!) rs!ID = "1000" may fail! rs!ID = 1000 may work! – simple-solution Sep 09 '19 at 15:15
  • What's the definition of your table testing124? SELECT * FROM `INFORMATION_SCHEMA`.`TABLES` WHERE TABLE_NAME LIKE 'testing124' acc. to [https://stackoverflow.com/questions/898688/how-to-get-database-structure-in-mysql-via-query] – simple-solution Sep 09 '19 at 15:17
  • Tried by hardcoding the value as rs!iid = 1000 but still getting same error. – SNK Sep 10 '19 at 03:12
  • Definition of the table as per Information_schema.tables is as follows : Table Catalog is showing the database name which is used in the connection string. Table Schema is shown as dbo, table name is shown as testing124 and table type is shown as base table. – SNK Sep 10 '19 at 03:13
  • Is there any references which needs to be added for this. Currently I have added the following references. Microsoft Activex Data Objects Recordset 6.0 library and Microsoft ActivexData Object 2.8 Library. Please help. – SNK Sep 11 '19 at 03:10
  • Can you test a different database? Can you write to this database? – simple-solution Sep 17 '19 at 16:40
  • Yes the database has write permission and I m using sa login only – SNK Sep 19 '19 at 02:43
  • Can you "UPDATE" OR "INSERT INTO" the database with a direct SQL command (SQL string command)? – simple-solution Sep 21 '19 at 09:25
  • Yes it is accessible and allowing to insert and update through sql commands using sql server management studio. – SNK Sep 22 '19 at 16:04
  • Can you "UPDATE" OR "INSERT INTO" the database with a direct SQL command (SQL string command) from vba? If yes change (rs.addnew ... rs.update) to the appropriate SQL command! – simple-solution Sep 23 '19 at 17:40
  • I am getting this same error. I am selecting * from [View]. My view went from 135 columns to 155 columns and that's when this problem occured. – Dasal Kalubowila Oct 02 '20 at 11:16