I have a simple, single user database, I am working with inside of the Microsoft 365 Access app. I am trying to use queries to pull a subset of data from one table (which was an imported .csv, but that isn't relevant to the issue) into another. I want to use the Field Size property on the Short Text fields to passively make sure junk doesn't end up in my database, since my source data is hundreds of thousands of records and not guaranteed to be perfectly clean.
I had assumed that if the source data contained more characters in a field than were allowed in the target field, it would trigger the "Microsoft Access can't append all the records in the append query." message. I could then abort the query and go track down the problem data if such a thing ever happened. I thought it wise to double check and, to my horror, no such message appeared. When I looked at the resulting data, the fields had been truncated at their respective character limits.
The following queries replicate the issue, but it happens whether I use the SQL view or the Design view, and whether just one record is inserted like this or a bunch of records are selected from another table.
CREATE TABLE myFirstTable
(
id INT NOT NULL,
tenCharacters VARCHAR(10),
maxCharacters VARCHAR,
CONSTRAINT pk PRIMARY KEY (id)
)
INSERT INTO myFirstTable (id, tenCharacters, maxCharacters)
SELECT 1, "going to be cut short", "this can be as long as I want, up to 255 characters"
Is there any way to get Access to stop silently truncating my Short Text fields? Any kind of error message or chance to abort would be good enough to let me know something went wrong and I should inspect the selection getting appended more carefully.
I've checked all the Access SQL documentation I can find, and cannot find any mention of this scenario. All my googling has failed me because if anyone else had this problem with Short Text they have been drowned out by the many, many instances of people asking about Access or SQL Server truncating Long Text/Memo fields at 255 characters in a whole slew of different circumstances.
This question implies that SQL Server sometimes throws an error in this scenario, so it feels like it is a reasonable thing to want. This answer to a different question seems to suggest ANSI_WARNINGS are related, but I am not familiar with Ruby or SQL Server so I don't fully understand the answer, and couldn't find a similarly named setting in Access.
I have been writing my queries in the SQL View of the query builder, since some of the other things I've needed to do were a bit beyond what the Design View can handle. I haven't needed to use VBA for anything, and the smaller scope of this project means the time investment / learning curve is not really worth it, so I'd like to continue avoiding it if there's another way to deal with this.
