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

x
?
Solved

MS Access ODBC into Oracle gives error when encountering a null value in Oracle.

Posted on 2006-07-14
16
Medium Priority
?
1,609 Views
Last Modified: 2011-10-03
I am querying an Oracle (8 or 9) Database from MS 2003 front end. I get an error message [Oracle][ODBC] Invalid date/time format. (#0)
I think this is because there are null fields in the Oracle date field. I have tried using a function to test for nulls and assign an arbitrary date (the "null" date fields in Oracle are from the initial data load and I don't need results from that time frame). Nothing seems to work. Please help. I mark this "urgent" because I am under a crushing time deadline to fix this.
0
Comment
Question by:joelmcm
[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
  • 6
  • 4
  • 4
16 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17111521
Is this a passthru query (i.e. run on oracle) or an access query (i.e. run on locally linked tables)

this matters as syntax is different


WIth oracle, u can use NVL  basically substitute a null value with some default value

can u post your sql?

0
 

Author Comment

by:joelmcm
ID: 17111587
It is run locally on a table linked to an Oracle db.

SELECT [1ML_RPTOBS_CMPPG].PID, [1ML_RPTOBS_CMPPG].HDID, [1ML_RPTOBS_CMPPG].OBSDATE
FROM 1ML_RPTOBS_CMPPG
WHERE ((([1ML_RPTOBS_CMPPG].HDID)=28));

where 1ML_RPTOBS_CMPPG is the linked table.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17111644
This is a straight forward select, I wonder how u manage to get errors like that

I guess u could try checking for nulls like this


SELECT PID, HDID, IIF(ISNULL(OBSDATE)=True,'No Date',OBSDATE)
FROM 1ML_RPTOBS_CMPPG
WHERE HDID=28


0
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!

 
LVL 5

Expert Comment

by:BPeb
ID: 17111721
My guess is that since your using this as a local table then Access is pulling all of the data inculding the null dates you don't care about.  You could create an Oracle view, or Oracle Query that restricts these records and then link to that instead so Access doesn't see them.  If you think might need that data some day then you could use an NVL to force a bogus value like 1/1/1111 into the view or query.  Otherwise Access is going to have to evaluate all rows everytime and blow up on the bad data.

0
 

Author Comment

by:joelmcm
ID: 17112983
When I try the fix:
SELECT PID, HDID, IIF(ISNULL(OBSDATE)=True,'No Date',OBSDATE)
FROM 1ML_RPTOBS_CMPPG
WHERE HDID=28
The results are very curious. First I see my select table with values for all three (PID,HID, and OBSDATE). But when I try to see ALL the records by scrolling down, I get the same error message and all three columns turn into "name#".
0
 

Author Comment

by:joelmcm
ID: 17112986
How can I create and Oracle view from Access? These downloads are done every night in the wee hours. I hae access ODBCing into Oracle, but no access to do anything with the Oracle table in Oracle.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 17113704
What BPeb is saying is you create a view in Oracle. then you link that in. You need to ensure the right privileges are set so users have access to that view, probably a public synonym

Is HDID always holding a integer value?

U could try this

SELECT PID, CINT(HDID), IIF(ISNULL(OBSDATE)=True,'No Date',IIF(ISDATE(OBSDATE)=False,'Bad Date',OBSDATE))
FROM 1ML_RPTOBS_CMPPG
WHERE CINT(HDID)=28



0
 
LVL 5

Assisted Solution

by:BPeb
BPeb earned 1000 total points
ID: 17114227
Weird, I entered a response similar to what follows last night around nine, but it's not here now.

You have to go to Oracle to and create the view there.  Using a tool like Toad, Navigator, or PL/SQL Developer you can do it pretty easily.  The idea here is to have Oracle prevent Access from seeing any invalid data, so use the NVL command (like NZ in VBA) to fix any fields you're worried about.  

Second thought is to have the Oracle data swept and cleaned up or at least put a bogus date in the null date fields in case you ever need to query the whole table.

Finally, I've used Access to Oracle ODBC a lot over the past four years and it's a flaky connection.  The simplest change to a query can cause a crash, or slow it to a crawl.  The more you can have Oracle do in advance the better off you are.  It's more stable and faster that way too.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17114838
BPeb, what ODBC driver did u use? The Microsoft one or the Oracle one? I am forced to use the Microsoft one because of the users using different versions of Oracle, but I prefer the Oracle one. Its handy cos it gives u more meaningful error messages when password expires.
0
 
LVL 5

Expert Comment

by:BPeb
ID: 17114973
I tried both and felt the MS one better (more stable) at translating the ODBC calls.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17114983
BPeb, Thanks!
0
 
LVL 5

Expert Comment

by:BPeb
ID: 17115057
You're welcome.  Lets hope joelmcm  can get access to the Oracle side and try it, but you know how DBA's are!  :)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17115097
Yes, at my place its all paperwork. Even to get one synonym created, I have to raise a change request then get it authorised by a high level manager. No wonder company loses money!
0
 

Author Comment

by:joelmcm
ID: 17569591
Thank you all for your assistances. I did have a "view" created, but it was constructed as some kind of static file, and does not change over time.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

715 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