Jon,
First of all, your English is fine. 8)
Secondly, I use Oracle connections a lot in A97 using DAO methods and not
ADO, so perhaps some of what I may talk about here may not apply...
But generally, I have found viewing an Oracle numeric field ("Column" in
Oracle terms) via ODBC will result in either a text field or a numeric
field in Access (Jet) linked Oracle tables. What determines this is the
"precision" (I think that's the term - I'm away from my Oracle reference
material at the moment) specified for an Oracle numeric field/column.
Data types for numbers in Oracle include:
Numeric
Numeric(precision)
Do you have access to Sql Plus? If you're going to be doing any Oracle
stuff with Access, I'd recommend you have this installed along with Net
Client, SQL Easy Config and Oracle Enterprise Manager to be able to look
at what's happening with your Oracle tables.
Anyway, in SQL Plus, sign into your Oracle database and user name and run
a desc command for the table in question. For Example:
desc MY_TABLE
You'll get a listing of the Oracle columsn and datatypes.
I've found the following translate to Jet linked fields the following way:
Numeric - Jet reads this as a numeric field
Numeric(16) - Jet reads this as text.
I'm not sure about other values for NUmeric(x) as the above two are the
only two I've been using (Numeric are the data types I assign to Oracle
tables I construct, Numeric(16) are what the commercial Oracle application
I report against has for all its number data types).
Dealing with a text translation of numeric fields can be a pain, but
generally, I've found using the Val() function in my queries helps a lot,
especially in reports when I am trying to do sums of the particular field
in question.
If you have any experience in writing Oracle SQL, I'd recommend the use of
pass through queries. That is the term in A97, anyway, in which you set
up a query, indicate a specific DSN, and then write Oracle SQL, not jet
SQL. The advantage of this in Access/Oracle applications is that using
Oracle statements in pass through queries passes a lot of the processing
to the server, rather than the client machine. Using linked tables is
fine, but weird things start happening sometimes when you are using a lot
of linked tables in a query.
I too used to do a lot of apps with Access/Jet and Oracle tables, but for
the past year or so have been doing everything on Oracle. The one thing I
like a lot about Oracle SQL is its use of theta joins instead of the ansi
joins used by Jet SQL (Inner join, left, join stuff). If you code SQL
strings depending on selections a user makes on your forms, for example, I
find it easier to create the Oracle joins (which are indicated in the
where clause as simply such and such a PK = AnotherFK, with a (+) if you
want the equivalent of a Jet left/right join).
Hope this helps, but in reality, I'm sure I've probably confused you.
Sorry...
On Sun, 7 Sep 2003, Jon Ole Hedne wrote:
My Access 2002-application need to work with tables from both Oracle and
Access. To solve this, I want to run some querys on three views in Oracle
and import the results into temporary Access-tables.
I have tried this:
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString = "data source=" & datafil & ";Jet OLEDB:Database
Password="
conn.Open datafil
SQL = "SELECT FieldID, UserID, etternavn & ', ' & fornavn & ' f. ' &
foedselsdato as Navn INTO " & _
"" & Temp_Tabell & " " & _
"FROM " & _
"[ODBC;DSN=KSV;UID=SYSTEM;PWD=pwd;].[" & Ora_User & ".MY_VIEW]"
conn.Execute SQL
Set conn = Nothing
and it works, but Number-fields converts to text-fields and I would rather
use OleDB than ODBC. If I import the data using the import-wizzard from the
database-window, the number-fields imports to number-fields in Access.. I
can't figure it out! Of course I can run a new SQL string converting the
fields in Access from text to integer, but this is a bad solution..
And another problem with the above solution is that the query is running at
the access-side, not the Oracle-server where it belongs. Can anyone help me?
Jon Ole Hedne
Norway
(sorry about my english...)
--
Tim -
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
