Import table from Oracle to Access

[Jon Ole Hedne]

A couple of days ago I posted this message to comp.databases.ms-access, but
I didn't get my problem solved:

My Access 2002-application needs to work with tables from both Oracle and
Access. (It contains a tree-view control, and I need to use the same
connection in the control source). 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 OLEDBatabase
Password="
conn.Open datafil
SQL = "SELECT FieldID, UserID INTO " & _
"" & Temp_Tabell & " " & _
"FROM " & _
"[ODBC;DSN=KSV;UID=" & Ora_User & ";PWD=" & pwd & ";].[" & Ora_User
& ".MY_VIEW]"
conn.Execute SQL
Set conn = Nothing

and it works, but:

1) Number-fields converts to text-fields (but I could maybe use the
Val()-function ) and
2) I would rather use OleDB than ODBC.

Another problem with the above solution is that the query is running at the
Access-side, not the Oracle-server where it belongs. Is it possible to run a
pass through query to the Oracle side that creates a temporary table on the
Access-side? Can anyone help me?

Jon Ole Hedne
Norway

 

[Anne Nolan]

<snip>

Another problem with the above solution is that the query is running at the

Access-side, not the Oracle-server where it belongs. Is it possible to run a
pass through query to the Oracle side that creates a temporary table on the
Access-side? Can anyone help me?

Jon Ole Hedne
Norway

Jon,

Make the pass-through query so that it returns the data you want, then create an
Access Make-Table query based on the pass-through query.

Anne

 

[Jon Ole Hedne]

Maybe a foolish question, but can this be done with SQL in VBA-code? I have
to do so, because the servername, userid and password to the Oracle-database
must come from an ini-file. So: How do I use one recordset (the pass-through
query) as recordsource of the second one (the make-table-query)?

 

[Jon Ole Hedne]

It looks like I have solved the problem. Now I use an ADOX-command to create
a pass-through query in the database and then a simple Docmd.Runsql "Select
* into " & temp_table & " from " & Pass_through_query & " "

Unfortunately I haven't found a solution to avoid using ODBC as provider
instead of Jet...

Jon Ole