Go Premium for a chance to win a PS4. Enter to Win

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,630 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
  • 6
  • 4
  • 4
14 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

772 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