Saving a LONG datatype to Oracle/SQL server

[rod]

hi! i have a front-end database (MS ACCESS 97) with a Memo Field and i'm having a problem
transferring the records to an Oracle (8.1.6) database using VB-ADO. The error that i'm
getting is "ORA-01704:String Literal Too Long". This might be because of the Memo field which
i'm trying to upload to my server which has a LONG datatype.

    Dim DTSws As Workspace
    Dim DTSdb As Database
    Dim DTStbDocumentList As Recordset
    Dim rstDocumentList As New ADODB.Recordset
    Dim cConnStr As String
    Dim cSQL as string
    Dim slRecordsAffected As String

On Error GoTo ErrorHandler

    Set DTSws = DBEngine.Workspaces(0)
    Set DTSdb = DTSws.OpenDatabase(cDefaultDirectory + cDefaultFilename, False, False, cDatabaseConnect)
    Set DTStbDocumentList = DTSdb.OpenRecordset("DocumentList", dbOpenTable)

    cConnStr = "Provider=MSDAORA;Data Source=dostdb;User ID=DEV_DTS;Password=developer01"
    Set cnDatabase = New ADODB.Connection
    cnDatabase.Open cConnStr
    cnDatabase.Errors.Clear
    cnDatabase.BeginTrans
    
    DTSws.BeginTrans
    DTStbDocumentList.MoveFirst
        
    Do While Not DTStbDocumentList.EOF
                    
        cSQL = "Insert Into DocumentList" + _
                      "(DocumentCode,Subject,Remarks) " + _
               "Values ('" + _
               DTStbDocumentList("DocumentCode")) + "'," + _
               DTStbDocumentList("Subject")) + "'," + _
               DTStbDocumentList("Remarks")) + "')"


        cnDatabase.Execute cSQL, slRecordsAffected
                                
    .
    .
    .

any ideas?

thanks

[Samuel]

Hi,
if i remember, the datatype in Oracle8 that corrispond with memo of access must be CLOB or BLOB; in SQL is TEXT and in Oracle7 is LONG (but you can't decalre more one column as LONG!!)
try these data types

Bye

[rod]

LOB (large objects) is an additional data type for Oracle.

CLOB and LONG are both use for large fixed-width character data. The difference is that the maximum size for LONG is
2 gigabytes and CLOB is 4 gigabytes. But basically both of them can hold the content of an Access MEMO field.

I think the problem here is how the data is being transmitted from my workstation to the server. I have successfully
transferred about a thousand records... but i'm having a problem to some records where the content of the REMARKS
field is large.

[rod]

ok... any suggestions, work arounds, ideas on how to do this differently but
achieving the same result... please

Look at the size of the field in Oracle normaly the maximum of a VarChar2 field is 2000 character i don't think that you have so much caracter in the access Database