Solved

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

Posted on 2011-09-05
13
525 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:marcgu
  • 7
  • 5
13 Comments
 
LVL 15

Assisted Solution

by:x77
x77 earned 500 total points
ID: 36486095
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.
0
 
LVL 15

Expert Comment

by:x77
ID: 36486121
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, ...)
0
 
LVL 15

Expert Comment

by:x77
ID: 36486142
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
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36488802
Just curious.

What does "PM" stand for...
0
 

Author Comment

by:marcgu
ID: 36488816
Hi! It´s as x77 pointed out the swedish translation for the data type: Memo in MS Access.
0
 

Author Comment

by:marcgu
ID: 36488877
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.
0
 
LVL 15

Assisted Solution

by:x77
x77 earned 500 total points
ID: 36488889
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.
0
 

Author Comment

by:marcgu
ID: 36489015
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
0
 

Author Comment

by:marcgu
ID: 36505636
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

0
 
LVL 15

Expert Comment

by:x77
ID: 36512544
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 ?
0
 

Author Comment

by:marcgu
ID: 36516142
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?
0
 
LVL 15

Accepted Solution

by:
x77 earned 500 total points
ID: 36516700
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;

0
 
LVL 15

Expert Comment

by:x77
ID: 36516734
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.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question