Problem with append query

Problem with append query - memo field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nujcharee@googlemail.com - 29 Nov 2007 16:33 GMT

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

Reply to this Message

Rich P - 29 Nov 2007 18:33 GMT

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

Reply to this Message

nujcharee@googlemail.com - 30 Nov 2007 08:23 GMT

> 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
[quoted text clipped - 15 lines]
>
> *** 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

Reply to this Message

lyle - 30 Nov 2007 08:49 GMT

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
[quoted text clipped - 12 lines]
> 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.

Reply to this Message