Pass-through treats NUMBER like TEXT 1

[iamareplicant]

Experts,

Here is a good one.

I have an A2K app with linked Oracle tables. I use pass-through queries to return data from the back end.

When I run some (not all) of the pass-through queries, Access renders Oracle field-type NUMBER data as TEXT.

That is, first, the alignment in the (what should be numeric) data cell of the returned query's grid is left-aligned (text) and all of my SQL VBA code that references that query's querydef sql (i.e., "WHERE ID = " & intID ) fails until I change the VBA to (i.e.) "WHERE ID = '" & intID & "'". Neato, huh? Nothing like having to treat a number like text to get an SQL statement to work,

So, I changed all of the code to treat what SHOULD be numeric data to TEXT, and the app works as it should.

Now the weird part (as if the above aint weird enough, right?). If I copy the file to another machine, THAT file (using an identical burn of Access 2K) blows up because IT is treating the numeric data as it should, and therefore all of my code that creates the workaround fails.

What is the deal? Has anyone experienced Oracle pass-through queries within A2K whose result set's NUMERIC data is rendered as TEXT by Access? If so, what was the culprit? Is is so easy I shall be kicking myself (BTW, I was going to force NUMERIC vals by using TO_NUMBER() in all of the pass-throughs, but found that that did not always work - that is, Access is blind to even a forced NUMERIC val).

I realized that i should expect some goofiness in an Access app that links to and passes-through to Oracle, but this one is a pretty important issue that I need to address...

Madams and sirs...help!

TIA,

JBG

 

[dhookom]

It may be an issue where your Oracle fields are defined with a larger size than Access can handle in numeric fields. This article might help

http://support.microsoft.com/default.aspx/kb/178043.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[OhioSteve]

I use ODBC connections to facilitate communication between Access and Oracle. When I create a connection, I must choose a driver. These connections are specific to just one PC. If you are using the same technique, then communication between Access and Oracle could vary a bit depending upon the driver used in the connection.

 

[iamareplicant]

Excellent. Thank you very much.