Problem with append query - memo field

Hi
I have a series of queries, I have a number of temp tables which I use
as templates for my data.
I start with
1. Delete the data in a temp table
2. Use append query to fill the temp table with data

Now, in the temp table, I have a memo field. I append information from
a text column (Oracle DB) into the memofield in the temp table. For
some reason, only the first 255 characters copied across as if it
takes that column in the temp table as a text field.

Why is this and how to work around it?

Many thanks
Padthai

Re: Problem with append query - memo field

I will guess you are connected to the Oracle DB using ODBC. This is the
bottlenect. With ODBC you wont get more than 255 chars. To get around
this limitation you need to connect to the Oracle DB using ADO.

Here is a sample connection string you could try:

Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider=OraOLEDB.Oracle;Data
Source=MyOracleDB;User Id=myUsername;Password=myPassword;"

Set RS = cmd.Execute("Select * From tblx")

Note that you will have to use PL Sql (Oracle sql) for the ADO command
text. The nice thing about ADO is that it can translate PL Sql,
Transact Sql (ms Sql Server) and Jet Sql.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Problem with append query - memo field

On 29 Nov, 18:33, Rich P wrote:
> I will guess you are connected to the Oracle DB using ODBC. This is the
> bottlenect. With ODBC you wont get more than 255 chars. To get around
> this limitation you need to connect to the Oracle DB using ADO.
>
> Here is a sample connection string you could try:
>
> Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
> cmd.ActiveConnection = "Provider=OraOLEDB.Oracle;Data
> Source=MyOracleDB;User Id=myUsername;Password=myPassword;"
>
> Set RS = cmd.Execute("Select * From tblx")
>
> Note that you will have to use PL Sql (Oracle sql) for the ADO command
> text. The nice thing about ADO is that it can translate PL Sql,
> Transact Sql (ms Sql Server) and Jet Sql.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

I am not familiar of ADO, only refer to ADO in asp file when I try to
connect to DB. How to do this in MS Access?
Thanks

Re: Problem with append query - memo field

On Nov 29, 11:33 am, nujcha...@googlemail.com wrote:
> Hi
> I have a series of queries, I have a number of temp tables which I use
> as templates for my data.
> I start with
> 1. Delete the data in a temp table
> 2. Use append query to fill the temp table with data
>
> Now, in the temp table, I have a memo field. I append information from
> a text column (Oracle DB) into the memofield in the temp table. For
> some reason, only the first 255 characters copied across as if it
> takes that column in the temp table as a text field.
>
> Why is this and how to work around it?
>
> Many thanks
> Padthai

We might be able to tell you why, and what to do about it if you post
the SQL you use to append. There are several special situations that
result in this truncating behaviour but which one is causing your
difficulty? We are likely to have a much better idea if you show us
the SQL.