title: VBA Excel SQL Query to update a field with more than 255 characters
body: <p>looking for a bit of help/advice please.</p>
  
  <p><strong>The Problem:</strong> I am trying to run an SQL query in VBA Excel to update an Excel Spreadsheet (.xlsx) column/field with a value that is greater than 255 characters but I am receiving the following error:</p>
  
  <pre><code>The field is too small to accept the amount of data you attempted to add.
  Try inserting or pasting less data.
  </code></pre>
  
  <p><strong>The Code:</strong> My connection string is as follows:</p>
  
  <pre><code>    Sub Open_Conn(strDb As String)
  On Error GoTo conError
      Debug.Print (Time() &amp; " - Connecting to: " &amp; strDb)
      Set CON = New ADODB.Connection
      CON.Provider = "Microsoft.ACE.OLEDB.12.0"
      CON.Open "Data Source=" &amp; strDb &amp; ";" &amp; _
      "Extended Properties=""Excel 12.0 XML;HDR=Yes"""
      Debug.Print (Time() &amp; " - Connected")
      Exit Sub
  conError:
      MsgBox ("There was an error processing the connection: " &amp; strSQL &amp;
      vbNewLine &amp; Error(Err))
      Resume Next
  End Sub
  </code></pre>
  
  <p>and my sql query code is:</p>
  
  <pre><code>Public Sub AddChangeRecord(ByVal strFieldName As String, ByVal strFieldValueNew As String, ByVal strFieldValueOld As String)
  
  Dim strSQL As String
  Dim strProgramNo, strUserID, strUpdateDate As String
  
  strProgramNo = frmMain.tbProgramNumberPD.Value
  strUserID = Environ("Username")
  strUpdateDate = Format(Date, "dd mmm yy")
  
  strSQL = "Insert Into [tblAuditLog$] (Program_No, UserID, UpdatedField, UpdateDate, OldValue, NewValue) " _
  &amp; "Values ('" &amp; strProgramNo &amp; "', '" &amp; strUserID &amp; "', '" &amp; strFieldName &amp; "', '" &amp; strUpdateDate &amp; "', '" &amp; strFieldValueOld &amp; "', '" &amp; strFieldValueNew &amp; "')"
  
  Call Open_Conn(strDb)
  Call Open_RS(strSQL)
  
  Call Close_Conn
  
  End Sub
  </code></pre>
  
  <p><strong>The Background:</strong> I have tried some suggestions that I have found online already including setting the maxscanrow variable in the connection string and making sure my first row contains default values of the specific length (so the columns first value contains a random string that is longer than 255 characters). I have also read that it may be possible to change registry keys but I am looking to distribute this to other machines and changing the reg keys of each machine is not possible unfortunately. Does anyone have any more possible suggestions for me to try?</p>
  
  <p>As always thank you in advance for any help/advice.</p>
  
  <p><strong>Edit:</strong> Forgot to mention that there are already existing around 16 records in the table with the column in question having different value lengths.</p>