Make table from Oracle sql

[teel73]

Hello,

I have connected to on oracle database thru ODBC connection via VBA. The connection works.

I have an oracle select script that I want to use in my vba.

I basically want to make an access table from the oracle sql. What would be the most efficient way to do this?

Can I use oracle sql in the SQL view of a query to run a make-table?

 

[mdlueck]

Do you desire to make corresponding tables in Access as a mirror image of the tables in the Oracle database?

If so, see...
"SELECT…INTO Statement (Microsoft Access SQL)"
http://msdn.microsoft.com/en-us/library/office/bb208934(v=office.12).aspx

 

[teel73]

thank you mdlueck

I still can't seem to get my code to work. here's what I have:

Dim server As String
Dim username As String
Dim password As String
Dim mcon As String
     
mcon = "Driver={Microsoft ODBC for Oracle}; Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prodora08.doleta.gov)(PORT=1921))(CONNECT_DATA=(SID=stbpdb)));User Id=RPT_GROUP;Password=RPT_GROUP#1;"
        
Set con = New ADODB.Connection
con.Open mcon
    
 'Get Oracle Instance
        strSQL = "select CASE_DATA.CASE_ID, CASE_DATA.CASE_NUMBER " _
        & "INTO myTestTable from OFLC.CASE_DATA left join OFLC.CASE_TEMP_INFO on case_data.case_id = CASE_TEMP_INFO.CASE_ID " _
        & "where CASE_DATA.application_id = 9 and CASE_DATA.CASE_NUMBER like '%H%'"
        
        
        DoCmd.RunSQL strSQL

That codes produces an error that it can't find the the file. What am I missing? and what file?

The connection works because I can create a recordset from a simple select statement and debug.print the data.

 

[mdlueck]

You have a very fine Pass-Through query configured. Unfortunately that is not going to work. You need to run in split mode... downloading the table from one database (Oracle) and populating a table in the Access DB.

If you need to run the query as Pass-Through then you must use a nested DAO.QueryDef configuration. I posted that solution here:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

Otherwise just use a single DAO.QueryDef object NOT configured for Pass-Through and use that to simple create the table.

Not the prettiest execution wise, this latter method, but for just getting the table created it will do.

Oh, and for this latter method you will need a Linked Table object in the Access database so that Access the DAO.QueryDef can deal with the Oracle table.

Oh oh... I see you have mixed your DB objects. You are creating an ADO connection, but then having DoCmd execute your query. That is two different ways of connecting to Oracle.

 

[teel73]

Yes. there is a reason to my madness .. I have 40 different tables that I need to pull data from so I'd rather just create subsets from those tables. I didn't wanna have to physically link 40 tables to my database.