ACCESS: How To: Create Table from Pass-Through Query [RESOLVED]

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
MS Access, remote Oracle Database NEWBIE ALERT!

I am trying to create a table based on a one-time Pass-Through query snapshot of an Oracle database. I have made a lot of progress ... I can build static pass-through queries and I actually get real data back from my database! I have even had some luck and success with fetching date limited data using the Oracle SQL "TO_DATE" function.

I have to generate my SQL statement dynamically ... the date range always changes ... so I have to load a table through code. Here is what I have so far:

Option Compare Database
Option Explicit
'=================================================================
' TEST ACCESS TO Oracle DER DATABASE
'=================================================================
Function Fetch_DER()
    Dim myDER As Database
    Dim myQuery As QueryDef
    Dim myTable As TableDef
    Dim strSQL1 As String
    
    ' Return reference to current database
    Set myDER = CurrentDb
    ' Connect to the Remote Oracle Data Base
    myDER.Connect = _
        "ODBC;DATABASE=DER_ACCESS;UID=myUID;PWD=password;DSN=DER_database_access"
    '===============================================================
    
    ' Delete the old table "TEST", if it exists
    If myDER.TableDefs.Count > 0 Then
        For Each myTable In myDER.TableDefs
            If myTable.Name = "TEST" Then
                myDER.TableDefs.Delete myTable.Name
            End If
        Next
    End If
    
    ' Create a table from a dynamic pass-through query
    Set myTable = myDER.CreateTableDef("TEST")
    
    ' Define a simple test Pass-Through SQL statement
    strSQL1 = "SELECT * FROM [der.form_lim_view] WHERE prfl_id = 3940;"
    
    '  >>>> HERE IS WHERE I AM STUCK ...
    '                  I need to populate "TEST" from my pass-through query "strSQL1"

    MsgBox ("Look at the new 'TEST' Table!")
    
End Function

Do I need to create a 'temporary' QueryDef, or is there some easier way I can specify the SQL statement to pass to the "myDER" connection for loading the new table "TEST"? If I have to create a QueryDef, then I have to check for the old version, delete it if it exists, and then create a fresh version (yuk).

My sincere appreciation for all comments, suggestions and assistance.

[dee-u]

Couldn't you do something like this?

This example selects all records in the Employees table and copies them into a new table named Emp Backup.

Sub SelectIntoX()
 
    Dim dbs As Database
    Dim qdf As QueryDef
 
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
 
    ' Select all records in the Employees table 
    ' and copy them into a new table, Emp Backup.
    dbs.Execute "SELECT Employees.* INTO " _
        & "[Emp Backup] FROM Employees;"
        
    ' Delete the table because this is a demonstration.
    dbs.Execute "DROP TABLE [Emp Backup];"
 
dbs.Close
 
End Sub

[Webtest]

dee-u:
=====

Thank you for your gracious comments. The remote database is on an Oracle server machine, and the only access I have is to read a snapshot of a table. I tried the INTO SQL function, but it didn't work in my pass-through query ... I always get an error message. Doesn't that create the new table on the source database machine? It is the Oracle server that is processing the SQL command. I have absolutely NO write access to the Oracle server.

I just need to know how to get my snapshot into a table on my client destop PC. It surely must be possible, but I just can't seem to figure out how to get from a completed connection to the remote database (achieved!) to an SQL PASS-THROUGH query into the TableDef I have created.

Thank you again for your comments. We will get through this somehow!

[Webtest]

Thanks to an old post by DaveBo (Ref in Code section) I got my basic application working. I can now retrieve a Snapshot Query from an Oracle table into an Access Table! Here is the code:

'################################################################################
' Create a Table named "TEST_TABLE" from a Recordset from table der.form_lim_view
' Modified From Post by DaveBo: 
'     "Table from Recordset" http://www.vbforums.com/showthread.php?t=263113
'################################################################################
Sub LIMITED_Test_MakeDataTableFromADO_Recordset()
    Const myTableName = "DER_DATA_TABLE"
    Dim myADOConnect As New ADODB.Connection
    Dim myRecSet As New ADODB.Recordset
    Dim myTable As TableDef
    Dim myDER As Database
    Dim tstr As String      'temp string
    
    Dim fld As DAO.Field
    Dim FldNo As Integer
    Dim fName As String
    Dim fSize As Long
    Dim fType As Integer
    Dim strQuery As String
    Dim x As Long

    Set myDER = CurrentDb()    ' This is just to get at the TableDefs

    ' Declare and Define the ODBC Connection String and the Oracle Pass-Through Query for the data
    Dim strODBC As String
    strODBC = "ODBC;DATABASE=DER_ACCESS;UID=myUID;PWD=password;DSN=DER_database_access"
    Dim strsql As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSQL3 As String
    Dim strSQL4 As String
    Dim strSQL5 As String
    Dim strSQL6 As String
    Dim strSQL7 As String
    Dim strSQL8 As String
    strSQL1 = "SELECT * "
    strSQL2 = "FROM der.data_extract_lim_view "
    strSQL3 = "WHERE ("
    strSQL8 = "(PRFL_ID = 3940)"   'G-Gro-DERdata-Weekly
    strSQL4 = "((RCVD_DATE)>TO_DATE('7/4/2005 8:59:59', 'MM/DD/YYYY HH24:MI:SS'))"
    strSQL5 = " AND "
    strSQL6 = "((RCVD_DATE)<TO_DATE('7/11/2005 9:00:01', 'MM/DD/YYYY HH24:MI:SS'))"
    strSQL7 = ");"
    
    strsql = strSQL1 & strSQL2 & strSQL3 & strSQL8 & strSQL5 & strSQL4 & strSQL5 & strSQL6 & strSQL7
    
    ' Open the ADO ODBC Connection into the Oracle Database
    myADOConnect.Open strODBC
    
    ' Define, configure, and open the Recordset for the snapshot of the data in the Oracle table
    Set myRecSet = New ADODB.Recordset
    myRecSet.CursorType = adOpenForwardOnly
    myRecSet.LockType = adLockReadOnly
    myRecSet.Open strsql, myADOConnect
    
    MsgBox ("Number of Fields in RecordSet: " & myRecSet.Fields.Count)
    
    ' Create the new table structure & define all fields from the recordset properties
    If myDER.TableDefs.Count > 0 Then
        For Each myTable In myDER.TableDefs
            'MsgBox (myTable.myTableName)
            If myTable.Name = myTableName Then
                myDER.TableDefs.Delete myTable.Name
            End If
        Next
    End If
    
    Set myTable = myDER.CreateTableDef(myTableName)  ' Create new TableDef object.

    Debug.Print
    For FldNo = 0 To myRecSet.Fields.Count - 1
        fName = myRecSet.Fields(FldNo).Name
        fSize = myRecSet.Fields(FldNo).DefinedSize
        fType = myRecSet.Fields(FldNo).Type
        Debug.Print fName, fSize, fType   ' show the field info
    Next

    myTable.Fields.Append myTable.CreateField("Profile", dbLong)
    myTable.Fields.Append myTable.CreateField("Form ID", dbLong)
    myTable.Fields.Append myTable.CreateField("Field Name", dbText)
    myTable.Fields.Append myTable.CreateField("Field Val", dbText)
    myTable.Fields.Append myTable.CreateField("Date", dbDate)
    myTable.Fields.Append myTable.CreateField("Store #", dbLong)

    myDER.TableDefs.Append myTable  ' Add this new table structure into TableDefs
    
    ' Load the recordset's data into new table
    '
    '    INSERT INTO TEST_TABLE VALUES(NULL,'text string',123.45)
    '
    With myRecSet
    While Not .EOF
        
        If .Fields(3).ActualSize > 0 Then
          If .Fields(3).Value <> Chr(10) Then
            'NOTE:
            'Special punctuation characters will crash field 3 (Parameters)
            'Write AlphaNumeric only to the field
        
            ' loop on recordset getting each record and building up
            ' an INSERT statement for each. Seems like hard way.
            strQuery = "INSERT INTO " & myTableName & " VALUES("

            strQuery = strQuery & .Fields(0).Value & ","         'Profile
            strQuery = strQuery & .Fields(1).Value & ","         'Form Number
            strQuery = strQuery & "'" & .Fields(2).Value & "',"  'Field Name
        
            tstr = .Fields(3).Value
            tstr = Replace(tstr, "'", "")
            tstr = Replace(tstr, """", "")
        
            strQuery = strQuery & "'" & tstr & "',"  'Parameter
            strQuery = strQuery & "'" & .Fields(4).Value & "',"  'Date
            strQuery = strQuery & .Fields(5).Value & ")"         'Store Number
        
            Debug.Print strQuery
            'THE FOLLOWING GOES TO THE LOCAL ACCESS CLIENT DATABASE!!!
            myDER.Execute strQuery
          End If
        End If
        'myADOConnect.Execute strQuery        ' add the record
        .MoveNext
    Wend 'Exit on EOF
    End With
    
    Set fld = Nothing
    Set myTable = Nothing
    Set myDER = Nothing
    
    myRecSet.Close
    Set myRecSet = Nothing
    myADOConnect.Close
    Set myADOConnect = Nothing
End Sub ' LIMITED_Test_MakeDataTableFromADO_Recordset

Be aware that there are a lot of configuration issues that need to be resolved in getting the connection to the Oracle server in the first place!

Good Luck and Good Learning. Thanks again, DaveBo!!!