Manipulating Text Strings

[daileyps]

I'm reading in a record from an ODBC connection with an SQL select and having a problem with a field that is defined on the database as a 2048 text string. The SQL select does not fail, but when I try to access the field, I get 3146 not found error. I'm thinking because it is greater than 255? Anyone have any suggestions how to read such a field in? Thank you in advance, I'll be sure to rate any helpful responses.

[Judgey]

I have had the same problem saving to a database with SQL, It just wouldn't let be use the INSERT INTO statement when I had a string more than 255, or included carriage returns ( I don't know why ).

The way I got around it though was to use the INSERT INTO statement to create the record and populate all the other fields, I then used the ;

DBrec.Open "Table",Dbase, adOpenStatic, adLockBatchOptimistic,adCmdTable
DBrec.Find "ID = " & IDstring
DBrec.Fields("Text") = StringText
DBrec.UpdateBatch

I don't know whether this will help, but could you not use the same principal to open the connection in the same way I open DBrec ?

[daileyps]

How did you read the data back? I just want to query the field, but can't read in the value.

[Judgey]

I never had any problems reading the the field, just used the normal code.

Dim DBRec as New Recordset

DBRec.open "Select TestTable.* From TestTable Where ((([TestTable].ID)= " & IdNo & " ));",Dbase,adOpenStatic

Text1.Text = DBRec.Fields("FieldName")

DBRec.Close

[daileyps]

I am unable to read in a string greater than 255...in this case, the field is 2048 in length:

Dim myConnect As DAO.Connection
Dim myWorkspace As DAO.Workspace
Dim mySQL As String
Dim myRecordset As DAO.Recordset
Set myWorkspace = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
Set myConnect = myWorkspace.OpenConnection("Header", dbDriverNoPrompt + dbRunAsync, , "ODBC;DSN=myDB;uid=;pwd=;")
mySQL = "SELECT myBigField" & _
"FROM myTable " & _
"WHERE myID = curID"

Set myRecordset = myConnect.OpenRecordset(mySQL) <---this is successful

myValue = myRecordset!myBigField <---this fails