SQL UPDATE Statement returns error

[Larry Haydn]

Workbook A is an Excel VBA application.
Workbook B is another workbook that serves as the database.

Each record comprises of 276 fields, so I had to break the record into:
1. Saving a composite key (3 fields)
2. Make 6 UPDATE queries to save different parts of the record
3. Parts 1, 2, 3, 4 updates successfully (each has many blank fields)
4. Part 5 and 6 have the same error (see below)
Below is the query for part 6:

The following SQL statement raises an error:

Here is what the UPDATE statement contains, and the error dialog (Part 5 and 6 have the same error):

I have checked that:
* the record exists (based on the composite key)
* the field names are correct (otherwise it would have raised another error)
* the database columns are formatted as Text
* the variables are declared as String
* there is data in the query (see above)
* the field names and variables do not clash
* there are 4 other successful updates to this record, some with variables that are blank

This is the heading in Workbook B (the database):

These are the WHERE parameters, which shows that the record exists:

Can anyone figure out what is the error?

 

[RoryA]

I suspect the issue is that you're restricted to 255 columns, and you're trying to update columns 271-276.

 

[Larry Haydn]

There is this restriction in Excel?
I thought that applies to Access.

 

[Larry Haydn]

Wow! You are absolutely correct!
I wrote the following code to test -
* Update to column 255 - pass
* Update to column 256 - fail with the said error
Thank you very much!

Is there a way to work around this?

Sub TextInsertBeyondColumn255()

    OpenConn
    
        '==========================
        '   Column 255 - Success
        '==========================
        strSQL = "UPDATE [FSDB$] SET " & _
                        "[Line8DimE173]='*' " & _
                        "WHERE [LotNo]='0806BD28' AND [MoldNo]='RV-6-039T-1' AND [CustCode]='V2';"
        cn.Execute strSQL
    
        '=======================
        '   Column 256 - Fail
        '=======================
        strSQL = "UPDATE [FSDB$] SET " & _
                        "[Line8DimE174]='*' " & _
                        "WHERE [LotNo]='0806BD28' AND [MoldNo]='RV-6-039T-1' AND [CustCode]='V2';"
        cn.Execute strSQL
    
    CloseConn

End Sub

 

[RoryA]

Doesn't look like it from your layout as you need to filter using columns at the start of the sheet, while writing to columns at the far right. You'd have to open the workbook or redesign it (that is an awful lot of fields).

 

[Larry Haydn]

Thanks for your help.

The application has been using the traditional way of mapping variables to columns.
So I just use the old way to save the columns beyond 255.

I am just preparing to move the database to SQL Server, which should happen in the next 3 months.