Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-05
13
Medium Priority
?
533 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 15

Assisted Solution

by:x77
x77 earned 2000 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 2000 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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