ADO Error when trying to update SQL Server db from VBA

nyconfidential

New Member
Joined
Jul 22, 2015
Messages
46
Office Version
  1. 365
  2. 2016
Hi all - I'm accessing a SQL Server database via an ADO connection in VBA. I can retrieve records, import them into the Excel spreadsheet, the connection seems to be fine. However, when I try to update the records in the SQL database from VBA via ADO connection, I receive the following error message: "Run Time error: '-2147217872 (80040e2f) Automation error"

The error occurs on the "rs.update" line below - anyone know what I might be doing wrong? Note - I've updating all fields in the table, received the same error, just updating one field now for demonstrative purposes. Appreciate the help, thanks!

VBA Code:
Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Driver={ODBC Driver 17 for SQL Server};Server=OurServer;Database=OurDB;UID=OurUID;PWD=OurPW;"
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
  
    ' Open the connection and execute.
    conn.Open sConnString
    rs.Open "SELECT * FROM tm_endts;", conn, adOpenKeyset, adLockOptimistic
    'Set rs = conn.Execute("SELECT * FROM tm_endts;")
  
    rs.AddNew
    rs.Fields(0) = "Test"

    rs.Update 'ERROR OCCURS HERE
    rs.Close
  
End Sub
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows

nyconfidential

New Member
Joined
Jul 22, 2015
Messages
46
Office Version
  1. 365
  2. 2016
Hi myconfidential,
at first glance your code looks okay. The suspected culprits for me are: does the user have write rights on the database? And: is that driver compatible with your version of Windows/Office (32 vs 64 bit). To dive in a bit further, try this error catching method, it will probably give you a more meaningful error message:
Hope that helps,
Koen

Thanks Rijnsent! I don't THINK it's permissions issue, as the table I'm trying to write to can be edited in SQL Server and Access as a linked table. I've actually determined it would probably wiser to do this within Access anyway, will be able to create a more user friendly form. Thanks again for responding!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,775
Messages
5,574,159
Members
412,574
Latest member
shadowfighter666
Top