SQL UPDATE Statement returns error

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
207
Office Version
  1. 365
Platform
  1. Windows
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:
Update Failed 1.jpg


Here is what the UPDATE statement contains, and the error dialog (Part 5 and 6 have the same error):
Update Failed 3.jpg


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):
Update Failed 5.jpg


These are the WHERE parameters, which shows that the record exists:
Update Failed 4.jpg


Can anyone figure out what is the error?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I suspect the issue is that you're restricted to 255 columns, and you're trying to update columns 271-276.
 
Upvote 0
There is this restriction in Excel?
I thought that applies to Access.
 
Upvote 0
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?

VBA Code:
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
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top