pass-through queries

[mjlong999]

Hello all. I would like to insert data from an Access 97 table/query into an existing Oracle 8 table. I realize that a link table query should work, and I have proven that it does in my environment. However, because I am linking to Siebel tables, some of the links fail, as the Siebel tables have more than 255 columns, and Access poops out.

My preferred solution is to create a pass-through query to insert records into Oracle from Access, which I have also got to work. However, I could only do this with hard coded values in my insert statement. I would like to insert into the oracle table and use the selected fields from an Access table/query. However, I can't get it to work. This is what I've been trying.....

insert into OracleODBC.Ownername.Tablename
(
oracle_field
)
select access_field
from Ownername.AccessTablename

Is this possible?

If not, is it possible to create a csv or tab delimeted output file from Access? The export function I tested from within Access does not produce a clean delimeted file, which could be used by an Oracle import utility. By this, I mean it exports to a txt file, but it puts in extra characters to outline the values(to give it the look of a table).


Any help would be greatly appreciated....

Mike

 

[tomschafer]

Unfortuately Pass-Through Queries are run on the server side and can not reference Access Objects. But you can write the SQL for the Pass-Through dynamically with VBA. Try modifying this code to use the object name in your db. Note - I don't know the syntax for the Insert command in Oracle so I used SQL Server syntax but, you should be able to tweak to meet your needs.

Sub ExportToOracle()

Dim rs As Recordset
Dim strSQL As String

Set rs = CurrentDb.OpenRecordset("select access_field from Ownername.AccessTablename&quot

strSQL = "insert into OracleODBC.Ownername.Tablename (oracle_field)"

CurrentDb.QueryDefs("PassThroughQueryName&quot.SQL = strSQL

While rs.EOF = False
strSQL = "values ('" & rs!access_field & "')"
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

End Sub

Let me know if you have any questions.

 

[mjlong999]

That worked! Thank you for your quick response.

Thank you, Mike