ADO Error when trying to update SQL Server db from VBA

[nyconfidential]

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!

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

 

[Rijnsent]

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:

https://support.microsoft.com/en-us/help/167957/info-extracting-error-information-from-ado-in-vb

Hope that helps,
Koen

 

[nyconfidential]

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:

https://support.microsoft.com/en-us/help/167957/info-extracting-error-information-from-ado-in-vb

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!