473,585 Members | 2,717 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Import table from Oracle to Access

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.Connection String = "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;UI D=SYSTEM;PWD=pw d;].[" & 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
3 23444
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(precisi on)

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.Connection String = "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;UI D=SYSTEM;PWD=pw d;].[" & 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
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******@Gunne r.Sabot.Spam.On .Loaded.FIRE> wrote in message
news:Pi******** *************** ********@plato. ucs.mun.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(precisi on)

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.Connection String = "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;UI D=SYSTEM;PWD=pw d;].[" & 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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
8971
by: GrayGeek | last post by:
After cx_Oracle and the related Oracle tools for Python 2.2.3 + Boa-constructor on Win2000, I added "import cx_Oracle" to the top of a test script. It gives me an error about being unable to find the OCISetDefault entry point in 'oci.dll'. Google says nothing about this error, and neither does the cx_Oracle author. Anyone have a solution...
1
6183
by: X | last post by:
Howdy: To follow up on a problem with MS Access (2000) and Oracle 9i (9.2.0.x) - History: I am trying to import tables from my Oracle 9i database on RedHat Linux 7.2 to MS Access (2000) on NT. I managed to turn on logging / debugging for ODBC connections and I got a few messages that I am unfamiliar with.
1
19889
by: M Bouloussa | last post by:
Hello, I have a little problem with an oracle import. My problem is : 1) I have a dump (file.dmp) contains a table T1 (just for this example) 2) I have an oracle schema (user=U1, pass=pass1) contains a table T2 with the same structure as T1 but having different tablespace (data,index) and with different
5
4335
by: premmehrotra | last post by:
I am using Microsoft Access 2000 and Oracle 9.2.0.5 on Windows 2000. When I export a table from Access to Oracle using ODBC I get error: ORA 972 identifier too long I think the error is because one or more columns in Access table are longer than 30 characters. My question is how one can overcome this problem. I also see several column...
3
4061
by: premmehrotra | last post by:
I am using Access 2000 and Oracle 9.2.0.x on a Windows 2000. I have setup Oracle 9.2 ODBC Driver (I have not yet figured how to set Microsoft's Oracle ODBC driver). I am exporting a table from Access to Oracle and I get following error: ODBC Call Failed: ORA-12571: TNS Packet Failure Error I see table and its indexes created in Oracle....
1
2963
by: Arti Potnis | last post by:
Hi, I want to import some tables from Oracle 9i to MS Access 2000. I'm able to import the table structures and data using File->Get External Data->Import . (I don't want to import links) However, this does not allow me to import constraints and relationships between tables. Is there some way to do so. If not through a menu or utility, is...
7
4180
by: Randy | last post by:
Folks: We have a web-based app that's _really_ slowing down because multiple clients are writing their own private data into a single, central database. I guess the previous programmer did things this way because it made things easy. Well, I'm the person that has to put up with the long-term headache. Anywho, someone at work wants things...
0
3719
by: Julie Warden | last post by:
Group, I'm running Oracle 8.05 with Solaris 6. I have a database with several schemas, and I want to create a test schema from one of the other ones. I'm having a problem with my export/import procedures. I created the schema: grant connect,resource to tst identified by tst;
0
1450
by: tacofinger | last post by:
Hi, How to import MS Query result to Access Table? I have one MS query result from oracle ODBC and total record is 100ku records. I have tried to use link table from Access -import function but it takes a long time to retrieve or refresh data. (because the database is in US and i am working in Asia. Any suggestion would be highly...
0
7900
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7832
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8192
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8332
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8204
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6592
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
2338
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1442
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1167
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.