Why does linked oracle view get data type PM in MS Access 2007 for one value

marcgu
marcgu used Ask the Experts™
on
Hi!

Short version: I have a view in Oracle. I have created a link in MS Access 2007 to this view. It works fine except for the fact that one value from the view get data type PM instead of TEXT. This leads to Error 3118 in MS Access when I try to link to tabels into one question using this value.

In the view the field that gets data type PM in MS Access has data type Varchar2(4000)

In the attached document there is a detailed description.

/Marcus  Background.pdf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
x77
Commented:
Text type on Ms Access has a limit of 255 chrs.
Varchar2 on Oracle has limit of 4096 chrs.

When I Export data from a DataTable to MsAccess:

        Select Case Tc
            Case TypeCode.String
                FLD = TD.CreateField (ColumnName, If(MaxLength > 255 OrElse MaxLength < 0, _
                                                     DataTypeEnum.dbMemo, DataTypeEnum.dbText), _
                                      If(MaxLength > 0, CObj (MaxLength), Missing.Value))

I Create a Memo Field for char length > 255.
On my Access 2007, I do´nt know "PM" type, I think it is your language name for DbMemo.
x77

Commented:
About the error.

Oracle allows Varchar2 until 4096 char, but there are some problems on binding vars when using unicode.
I think it is a bug, but it works fine until 2048 chars and fail to get data when exceds this size.

I find some triks to solve this problem in my querys (decompose field on two strings with length < 2048, using clob parameters, ...)
x77

Commented:
this a sample where I use Lob type to update a String until 4000 chrs on Oracle with unicode using ODP - Oracle.Data.Access.

Dim Cmd As New OracleCommand("Update Proyectos Set Estado=:Estado,Descr=dbms_lob.substr(:Descr,4000,1),Fecha=:Fecha,Datos=:Datos " & _
                         "Where Id=:Id and Rv=:Rv", Db)
    AddParams(Cmd, prj.Estado, prj.Descr, FileDateTime(prj.Fname), prj.ToBlob, prj.Id, prj.Rv) 'DateTime.Now
    Cmd.Parameters(1).OracleDbType = OracleDbType.Clob
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Just curious.

What does "PM" stand for...

Author

Commented:
Hi! It´s as x77 pointed out the swedish translation for the data type: Memo in MS Access.

Author

Commented:
x77. Thanks a lot. I will try it out.  Just to clarify. Should this code be in my oracle view and where I only replace ColumnName with my real columname?

Select Case Tc
            Case TypeCode.String
                FLD = TD.CreateField (ColumnName, If(MaxLength > 255 OrElse MaxLength < 0, _
                                                     DataTypeEnum.dbMemo, DataTypeEnum.dbText), _
                                      If(MaxLength > 0, CObj (MaxLength), Missing.Value))

Open in new window



You write that you use it when you´re export data.
x77
Commented:
I try today a Link from MsAccess to Oracle View with Varchar2(4000) field.
It Works ok, also on Update.

What Driver are you using ?
Note that you can use Microsoft Odbc for Oracle and also the Oracle Driver for Odbc.

Author

Commented:
Hi! Thanks for your advice.
I tried with these two but with the same result. In the attached picture, you see the only placed I have changed so far.


driver.jpg

Author

Commented:
Hello! Please, Should this code be a part of my oracle view? I only know basic sql and MS Access and do not understan how to integrate this code which x77 suggested into my oracle view which can be read in my pdf  attached to my question.

       
Select Case Tc
            Case TypeCode.String
                FLD = TD.CreateField (ColumnName, If(MaxLength > 255 OrElse MaxLength < 0, _
                                                     DataTypeEnum.dbMemo, DataTypeEnum.dbText), _
                                      If(MaxLength > 0, CObj (MaxLength), Missing.Value))

Open in new window



Neither do I understand how to integrate this code into my view:
Dim Cmd As New OracleCommand("Update Proyectos Set Estado=:Estado,Descr=dbms_lob.substr(:Descr,4000,1),Fecha=:Fecha,Datos=:Datos " & _
                         "Where Id=:Id and Rv=:Rv", Db)
    AddParams(Cmd, prj.Estado, prj.Descr, FileDateTime(prj.Fname), prj.ToBlob, prj.Id, prj.Rv) 'DateTime.Now
    Cmd.Parameters(1).OracleDbType = OracleDbType.Clob

Open in new window

x77

Commented:
Not, this code is part of my method for export a DataTable to a Ms. Access database.
I put it only to show how a String Field become Text or Memo on Acces depending on maximum size.
Automatic linked View do same adaptation.

Your code is correct, I do´nt understand why if fails.
I try it with oracle 9 and Microsoft ODBC drivers and it works fine.

I know about some bugs with size of Unicode Strings with Oracle Parameters, but do´nt apply tho this question.

What character set uses your Oracle Server ?

Author

Commented:
Hi!

I ran this query: SELECT * FROM NLS_DATABASE_PARAMETERS and got this result:
NLS_CHARACTERSET      WE8MSWIN1252

Could this explain the result?

Is there no way to "force" the size in my view to be maximum 2000? Instead of my 4000?
Commented:
Your Nls_CHARACTERSET  indicates 1 bytes per  char. No Problem.

You can limit your column to 2000 char using Substr:

create view v1 as select id,rv,estado,activo,fecha,descr from proyectos;
create view v2 as select id,rv,estado,activo,fecha,substr(descr,1,2000) Descr from proyectos;

x77

Commented:
About Error 3118

You cannot join Memo or OLE Object fields.
The field you use to logically tie two or more tables together must be some other data type, such as text or integer.

This error is from Ms Access, your View works ok but you can not operate with Memo Fields.
Note that Text Fields on MsAccess are limited to 255 chars.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial