By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,731 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Import table from Oracle to Access

P: n/a
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...)
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
Thanks a lot and no - you're not confusing me :-)

At home I have set up an Oracle-database for testing only, with precision
10. I don't know how the precision is set up at the production-database I'm
going to use later. It's probably a good idea to use the Val() function to
be sure I get Numeric fields in the output.

I have used pass through queries before, but know I need to work on
access-tables, which in this case means temporary tables with data from
Oracle. The reason for this is that I have a form with a treeview-control,
where I use the shape-command. When building the tree, I cannot use more
than one connection-source.

Of course linked tables could be an alternative, but to avoid all the
network-traffic I'll get with frequently running queries against linked
tables, I think the best solution is to create some sort of pass through
queries that generate temporary tables in Access.

"Tim Marshall" <tm******@Gunner.Sabot.Spam.On.Loaded.FIRE> wrote in message
news:Pi*******************************@plato.ucs.m un.ca...
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

Nov 12 '05 #3

P: n/a
Jon Ole Hedne wrote:

Of course linked tables could be an alternative, but to avoid all the
network-traffic I'll get with frequently running queries against linked
tables, I think the best solution is to create some sort of pass through
queries that generate temporary tables in Access.


I'm actually not familiar with the treeview control - is this an A2K or
A2K2 feature?

You might also try just having the pass through queries in your mdb and
link these to the Jet tables. But, again, if you are using Jet tables
linked from another mdb, that would involve two connections.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.