Not sure if I understand fully, but if the name of the external
connection/table does not change, can you simply run a make-table query
against that connection one time to get the temporary table formatted
correctly? I often do this, and then set up an Append query to populate the
table when needed, preceded and followed by a Delete query to flush the
contents before and after use, while leaving the table structure intact for
the next Append/Delete cycle. I'm usually using ODBC links, however, instead
of ADO, so I am not sure if my comments are germane.
"Dave" wrote:
> Hi,
>
> I am trying to insert records into an access table
> returned from an ADO connection to an external oracle
> database. I am able to succeed this by inserting records
> into the access db while reading the records from oracle.
> This works fine, but I don't think it is the best way of
> doing it. I also have to structure the tables in access
> and change external queries accordingly. I ultimately want
> to run a query on the fly that would create a table in
> access with the results retured. Currently, I use the
> below, any help would be appreciated.
> ********************************************************
>
> Private Sub cmd_connect_Click()
>
> '**********************************************************
> *********************
> 'This Module will create 2 ADO Connections, one will
> connect to the Oracle Instance,
> 'the other will reference a connection to the current
> database.
> 'While oracle ado con loops through the records returned
> from the query, the current
> 'ADO con will write the data to a table in the current
> Access Database
> '**********************************************************
> *********************
> Call clear_form
>
> If Me.cmb_queries <> "" Then
>
> 'Declare Variables
> Dim oracle_db As New ADODB.Connection
> Dim oracle_rs As New ADODB.Recordset
> Dim current_db As New ADODB.Connection
> Dim current_rs As New ADODB.Recordset
> Dim y As Integer, n As Integer, z As Integer 'Counter
> Variables
> Dim str_access_table As String
> Dim strsql As String
>
> Set current_db = CurrentProject.Connection
>
> 'Get Required Parameters for selected query
> current_rs.Open "Select query_code, access_ref_table from
> sql_scripts where query_name = '" & Me.cmb_queries & "'",
> current_db
> strsql = current_rs("query_code")
> str_access_table = current_rs("access_ref_table")
> current_rs.Close
>
> DoCmd.SetWarnings False
> Me.lbl1.ForeColor = 0
> DoCmd.RepaintObject
> DoCmd.RunSQL "Delete * from " & str_access_table & ";"
> DoCmd.SetWarnings True
> Me.opt1.Value = 1
> DoCmd.RepaintObject
>
> 'Oracle Connection Parameters
> Me.lbl2.ForeColor = 0
> DoCmd.RepaintObject
> oracle_db.Provider = "MSDAORA.1"
> oracle_db.Open "oar_db", "userid", "psw"
> Me.opt2.Value = 1
> DoCmd.RepaintObject
>
> Me.lbl3.ForeColor = 0
> DoCmd.RepaintObject
> oracle_rs.Open (strsql), oracle_db, adOpenStatic
> Me.opt3.Value = 1
> DoCmd.RepaintObject
>
> 'Current Access DB Connection Parameters
> current_rs.Open (str_access_table), current_db,
> adOpenDynamic, adLockOptimistic
>
> 'n = 0
>
> oracle_rs.MoveFirst
>
> Me.lbl4.ForeColor = 0
> DoCmd.RepaintObject
>
> Do Until oracle_rs.EOF
> 'y = n + 1
>
> current_rs.AddNew
>
> For Each x In oracle_rs.Fields
> current_rs(x.Name) = x.Value
> Next
>
> 'Me.txt_counter = y
> 'DoCmd.RepaintObject
> 'n = y
>
> oracle_rs.MoveNext
>
> Loop
>
> current_rs.Update
>
> Me.opt4.Value = 1
> DoCmd.RepaintObject
>
> 'Close ado connections and kill references
> Me.lbl5.ForeColor = 0
> DoCmd.RepaintObject
>
> oracle_rs.Close
> oracle_db.Close
> Set oracle_rs = Nothing
> Set oracle_db = Nothing
>
> current_rs.Close
>
> 'Display record count
> strsql = "Select count(1) as record_count from " &
> str_access_table & ";"
> current_rs.Open (strsql), current_db
>
> z = current_rs("record_count")
> current_rs.Close
>
> 'Me.lbloutput.Visible = 1
> 'Me.lbl_destination = str_access_table
> 'Me.lbl_destination.Visible = 1
> 'DoCmd.RepaintObject
>
> Set current_rs = Nothing
> Set current_db = Nothing
>
> Me.opt5.Value = 1
> DoCmd.RepaintObject
>
> MsgBox z & " records have been copied to table " &
> str_access_table, vbInformation
>
> cmb_queries = ""
> Call clear_form
>
> Else
> MsgBox "Please Select a Query from the Pull Down List",
> vbInformation
> End If
>
> End Sub
>
|
Similar Threads
