execute make-table query

[teel73]

Is there a way I can execute a make table query from oracle data? Basically what I wanna do is select data from oracle and create a table from that recordset simply by executing a make table query. I was able to make the connection and create the recordset. But I don't know the syntax to SELECT data from the recordset into a new table. Here's what I have:

Sub importData()
Dim rstImportTemp As adodb.Recordset
        Dim rstTemp As adodb.Recordset
        Dim fld As adodb.Field
        Dim sqlstmst$
        Dim last_event_id&
        Dim has_new_rec As Boolean
        Dim rec_count&
   
        'here is where I make my connection to Oracle
        If chkImpt Then Exit Sub
        
        Debug.Print sqlstmst
        
        Set rstImportTemp = New adodb.Recordset
        Set rstTemp = New adodb.Recordset
        
        '----oracle
        sqlstmst = "select c.CASE_ID, c.case_number " & _
        "from oflc.case_data c " & _
        "where c.application_id = 3 " & _
        "and c.CASE_NUMBER like '%H%'"
        rstImportTemp.Open sqlstmst, dvcon, adOpenForwardOnly, adLockReadOnly
 
DoCmd.RunSQL "SELECT rstImportTemp.FORM_ETA750_ID, rstImportTemp.CASE_NO INTO testTable FROM rstImportTemp;"

That returns an error that it can find the table or query.

Any help would be appreciated.

 

[DavidAtWork]

Can we see what your function chkImpt is doing, I would expect to see that chkImpt is either linking or importing the necessary Oracle tables
Also I'm not familiar with your method of opening the recordset
"rstImportTemp.Open sqlstmst..."
I have always used the
Dim db as Database
Set db = CurrentDb()
Set rstImportTemp = db.OpenRecordset(sqlstmst)
method

David

 

[gemma-the-husky]

of course

i would just execute a stored maketable query

docmd.openquery "makemytable"

alternatively, create a blank table with the structure you want, and append the records. this way is probably better, as it means you can add indexes to your temp table.