Problems with SQL Server & Access

When I try to Insert a record from a central db (SQL Server, ver 6.5) into a local db (Access95) I get 'Error 3075 Syntax error in string in query expression...'. What causes this problem is one field in the record. This field is defined as Text in SQL-server and as PM in Access. Inserting the data from Access to SQL-server causes no problems. There are no problems to retrieve the record from SQL-server. The problem arise when I try to Insert the retrieved record into Access.
I use VB4 and ADO. I store the SQL-command in a string (sSQL) and then use:
M_dbDatabase.Execute(sSQL, dbFailOnError + dbSQLPassThrough)
as the "trigger" for the command. In the VB code the Text-type/PM-type field is defined as a string.
I really hope that someone understood my question and that someone has an answer to this problem.
Please make my month! :-)
Thanks in advance from a Frustrated Swede!!

[Sanjeeev]

Please tell me first What is PM data type in Access, and What ver ADO you are using

PM(Memo) data type is (according to the help): "Longer text or combination of text and numbers. Maximum of 65.535 characters.(If the field is changed with DAO so text and numbers [not binary data] are saved in it the field size will be limited by the size of the database.)"
I have translated the text from Swedish, so I hope I got it right. The text that the problem has arised for is less than 65.000 characters long.

When it comes to the ADO I had misunderstood the information when I took over the responsibility of this system. What is used is DAO 3.0 and RDO 1.0.

I hope that this gives you some light to my problem and you know an answer (even though I gave some misleading information from the start).

A thousand thanks for your response!!!

[Sanjeeev]

Hi,

Here What I have tried as an example with VB6.0. and it worked for me.
It didn't produced any error for me


def in SQLServer database table.

ClassCodeInfo ------> VarChar(50)

Structur in Access database table

Table Name : texttab1
Def : ID-----> Autonumber
name -------> memo.


Note : If you have translated from swedish please check the content of the test. It should not contain "'" Char
in it.




'---------------------------------------------------------------------------------------------------------------
private Sub Command2_Click()

Dim ConSQlSer as ADODB.Connection
Dim SQLrecordset as ADODB.Recordset

Dim ConnString as string
Dim SQLSelect as string




Dim ConAccSer as ADODB.Connection


ConnString = "Provider=SQLOLEDB.1;Server=Sil_dev_server;Database=isovehicle_test"

set ConSQlSer = new ADODB.Connection

ConSQlSer.Open ConnString, "s2user", "test"

If Not ConSQlSer is nothing then

set ConAccSer = new ADODB.Connection
ConAccSer.Open "Provider=MSDASQL.1;DSN=QuadChecking", "", ""

If Not ConAccSer is nothing then

SQLSelect = "Select distinct ClassCodeInfo from isovehicle"
set SQLrecordset = new ADODB.Recordset

SQLrecordset.Open SQLSelect, ConSQlSer

Do While Not SQLrecordset.EOF
ConAccSer.Execute "Insert into texttab1( name ) values ( '" & SQLrecordset("ClassCodeInfo") & "')"
SQLrecordset.MoveNext
Loop

End If


End If

'---------------------------------------------------------------------------------------------------------------