Make Table from external DB Connection

[Dave]

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

 

[Brian]

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.