0

I've been getting the following error when trying to write data from my HMI to a MSSQL db using ADODB.recordset with the AddNew/Update Methods. I'm using SQL Server Native Client 11.0 for the connection and Microsoft ActiveX Data Objects 6.0 Library.

Generic VBA Error -2147467259[Microsoft][SQL Server Native Client 11.0][SQL Server]FNGCO,ABCDEFGHI,1000003,2017-04-14,17:00:36:187,FOAML1,A1,,1

If it works this code should write each value to each column in the MSSQL database. I've got very similar code working in another part of the application, but I can't seem to get this working.

What I Have Tried So Far:

  • Checked against code running in another HMI, everything between the two appears to be identical.
  • Tried the same connection string with another ADODB.recordset and added data to the db with AddNew/Update.
  • Checked the database table to make sure all of the data I'm trying to enter fits the column (its not null, max characters not exceeded, etc...)

Here is my code: On debug, the code halts at sqlrs1.Update.

Option Explicit
Const strSQLNCLI11_1 = "Driver={SQL Server Native Client 11.0};Server=TESTDEMO\SQLEXPRESS;Database=RABPI;Uid=sa;Pwd=testdemo;QueryTimeout=0"'

Private Sub Button1_Released()
On Error GoTo ErrHandler

    Dim sqlcn2 As ADODB.Connection
    Dim sqlrs1 As ADODB.Recordset
    Set sqlcn2 = New ADODB.Connection
    Set sqlrs1 = New ADODB.Recordset

    sqlcn2.Open strSQLNCLI11_1

    sqlrs1.Open "Select * from AT_BW_PRDN ;", _
        sqlcn2, adOpenDynamic, adLockPessimistic

     sqlrs1.AddNew
     sqlrs1("AT_BW_BUS_UNIT") = "FNGCO"
     sqlrs1("AT_BW_PID") = "ABCDEFGHI"
     sqlrs1("AT_BW_PRDN_AREA") = "FOAML1"
     sqlrs1("AT_BW_SHIFT") = "A1"
     sqlrs1("AT_BW_ITEM_ID") = "1000007"
     sqlrs1("AT_BW_REL") = 0
     sqlrs1.Update
Exit Sub

ErrHandler:
LogDiagnosticsMessage "Generic VBA Error" & Err.Number & " " & Err.Description, ftDiagSeverityError, ftDiagAudienceOperator
    Set sqlcn2 = Nothing
    Set sqlrs1 = Nothing
Exit Sub

End Sub

UPDATE: I made a test database and re-created the tables I'm trying to write to without any constraints or triggers and the code functioned properly.

ObsoleteATM
  • 1
  • 3
  • Is this the complete error? 80004005 is generally DB login error. If this is a new SQL setup, by default it is running on WINDOWS ONLY. You will need to change it to MIXED MODE security. – athar13 Apr 15 '17 at 01:50
  • Yep, this is the whole error. This is a db restore from a live database. I set up mixed mode security for the instance and created the SA account with those credentials. That same connection string can successfully write to the database in other portions of my code. Thanks for the info on the db login error, I will check into that! – ObsoleteATM Apr 16 '17 at 21:46

0 Answers0