INSERT INTO where data is on an Oracle database

[gmore24]

Hello,

To ease the process of importing data into a table, I'd like to know
if it's possible to use INSERT INTO with data from an Oracle database
or anything else of this kind.

I already have a query that does the job by looping thru a recordset
like this :

********************************************************
Dim cmdo As ADODB.Command
Dim rst As ADODB.Recordset
Dim db As DAO.Database
Dim cnn As ADODB.CONNECTION
Dim sSQL As String
Dim sqlstr As String
Dim ConnStr As String

Set db = CurrentDb()

Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
Set cmdo = CreateObject("ADODB.Command")

ConnStr = "PROVIDER=OraOLEDB.Oracle;DATA SOURCE=X;USER
ID=X;PASSWORD=X;"
cnn.Open ConnStr

sqlstr = "SELECT ITEM_ID FROM ITEMS"

cmdo.CommandText = sqlstr
cmdo.ActiveConnection = cnn

Set rst = cmdo.Execute

Do While Not rst.EOF
sSQL = "INSERT INTO Table1" & _
" ([ITEM_ID]) VALUES('" & rst![INVENTORY_ITEM_ID] &
"')"
db.Execute sSQL
rst.MoveNext
Loop

****************************************************
However, what I'd like to know is if it's possible to use INSERT INTO
with the connection already established with the Oracle database?
Like : INSERT INTO Table1 + ("the query that will enables to retrieve
data").... this without creating a recordset... ? Does that make
sense?

Thanks for your help,
Gmore

 

[Clifford Bass]

Hi,

Just create a link to the Oracle table through ODBC. Then you can
create an append query in the Query Designer that uses the linked table as
the source. It works with any database brand that provides ODBC drivers.

Clifford Bass

 

[gmore24]

Thanks for your reply.

Do you know where I can find an example? I tried but with no
success...

- Show quoted text -

 

[Clifford Bass]

Hi,

Access's online help has some information. For linking: Access 2003 or
earlier - search for "import or link data", including the quotes. Access
2007 - go to help, choose "External Data", then "Import" and then "Import or
link to SQL Server data". Go to the section on linking. While it tells
about SQL Server, linking to an Oracle table through ODBC is the same.

For information on append queries search for "append query", with the
quotes.

Hope that helps,

Clifford Bass