Solved

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

Posted on 2011-09-05
13
519 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
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.

863 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

16 Experts available now in Live!

Get 1:1 Help Now