I need to odbc insert access form text into SQLServer as ole?

[Guest]

I need to use Access and ODBC to insert text into a linked SQLServer table
where the field is stored as an ole object. Currently I'm using an unbound
form control and an append query. When I do it now the data is stored and
retrieved as unicode text and the extra spaces is messing up the output side.

 

[Sylvain Lafontaine]

Extra spaces? Maybe because you have used a nchar() instead of an
nvarchar() as the type of the field on the SQL-Server table.

I'm not sure what you mean by "where the field is stored as an ole object".
SQL-Server doesn't have a direct support for OLE objects but the type Image
can be used as a replacement.

 

[Guest]

I say "stored as an ole object" because that is what shows when I look at the
linked SQLserver table db in design view in my odbc access db. My
understanding from MS Office help is that the data in Acess text and memo
fields is now stored in Unicode format which uses 2 bytes for the character
definition (the first is used for certain araibic characters and is defaulted
to 0 for latin character types). Apparently when the text I'm inputting is
coming back out it is being decoded as twice the number of characters with
each 0 being seen as a space. The SQLserver db records are being populated
through a third party softeware so I can't change the data type there.

 

[Sylvain Lafontaine]

You're right when you say that text and memo fields are now stored as
Unicode format in Access. (UTF16 by default; if you chose with compression
mode, UTF8 will be used).

SQL-Server is capable of using both types: Unicode and Ascii and when you
make an insert, it will convert the text to the proper type. Proper
conversion should also be done if you are updating the database through a
linked table.

However with types such as the "OLE Object", Image and Binary fields, there
will be no conversion of the data. The fields are usually used for storing
things like images or text fields in an unsupported mode for the SQL-Server,
such as UTF8.

I suppose that there is some mixe-up at this level.

It's also a fact that using Access to interpret the schema of the SQL-Server
database might lead to some misunderstanding about the real type of the
field as it is stored on the SQL-Server.

You will have to be more explicit about this third party software, what you
are doing exactly and what are the real type of the field. Consulting the
web site of this third party software and using a tool such a SQL-Server
Enterprise Manager (for SQL-Server 2000) or SSMS (for 2005) will tell you
exactly what's going on.