INSERT INTO where data is on an Oracle database


G

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
 
Ad

Advertisements

C

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
 
G

gmore24

Thanks for your reply.

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

- Show quoted text -
 
Ad

Advertisements

C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top