Solved

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

Posted on 2011-09-05
13
515 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now