Append to SQL Server

[Sash]

I have a SQL Server linked table in Access and I'm running an append query to
populate this table. It works fine except if the field has 10 characters,
245 blank spaces are put at the end in SQL Server. The columns are set-up as
VarChar in SQL Server and are a memo field in Access. The reason they are a
memo field is that this field could contain 9 characters or 255 characters.
However, I do not want SQL Server to consistently be 255 characters with
blank spaces at the end.

The Access data truly does not have spaces at the end.

 

[John Spencer MVP]

I have not seen that behavior before. Are you sure that the SQL field is
defined as VarChar and not Char?

IF it is varChar then perhaps you can use the Trim or RTrim function on the
memo field in the query. It is possible that the Access memo field does
contain trailing spaces depending on how the data got into the memo field.
Typing in the data will automatically remove any trailing spaces, but
importing from another source that does have the spaces at the end or using
code or an insert or update query will keep trailing spaces.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Michel Walsh]

If you really have varchar(255) and not simply char(255), then maybe you
could run RTRIM on the value you append, to remove trailing blanks at the
end of the string of 255 characters.

If you use char(255), in MS SQL Server, the field is always filled up with
blanks to make exactly 255 characters. AND you also have to supply those
blanks, when you compare that field to a constant string, since, evidently,
"a" is not equal to "a ".




Vanderghast, Access MVP

 

[Sash]

It's definately VarChar. I've checked it and checked it, because it's
driving me crazy that it isn't working as I would think. John makes a good
point about the data. I am importing it from another source. I'll try to
run an RTRIM after appending.