Best way to append to Oracle

[whistler]

Please can somebody enlighten me how to append records from within MS Access 2003 in an efficient way.

I have some VBA code where I set the parameters of a parameter query in MSACCESS and then execute this query.

something like

qd as querydef
set qd.sql= INSERT INTO TblA ( A, B, C, D ) SELECT StrA, StrB, strC, strD
qd.execute

where A,B,C,D are target fields, StrA, StrB, StrC, StrD are parameters and TblA is the Access name of a linked Oracle table.

This works, but very slow...

I suspect a passthrough query would be in order, but am unsure how to go about.

Any hints ?

Thanks, Jos




--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[John Spencer]

If fields A,B,C, and D are all text fields and the four strings all have
values you might try

Dim Db as DAO.Database
Set db = CurrentDB()

strSQL = "INSERT INTO TblA ( A, B, C, D ) " & _
" VALUES( """ & StrA & """, """ & _
StrB & """, """ & _
strC & """, """ & _
strD & """)"
db.Execute strSQL, dbfailonerror

If the strings could be zero-length or the fields are other than text fields
than the SQL string will need to be modified to have the proper delimiters,
etc.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

qd as querydef
set qd.sql= INSERT INTO TblA ( A, B, C, D ) SELECT StrA, StrB, strC, strD
qd.execute

where A,B,C,D are target fields, StrA, StrB, StrC, StrD are parameters and TblA is the Access name of a linked Oracle table.

This works, but very slow...

I suspect a passthrough query would be in order, but am unsure how to go about.

Have you tried a bound form, rather than entering data into an unbound form
and then opening a recordset and running a query for each record?

Your query as written doesn't have a FROM clause - it may be using the (large,
I presume) linked Oracle table as its source. Also as written it can't
possibly work since you're not using a string constant.

Try

Dim strSQL As String
Dim db As DAO.Database
strSQL = "INSERT INTO tblA(A,B,C,D) VALUES('" & strA & "', '" & strB _
& "', '" & strC & "', '" & strD & "');"
db.Execute strSQL, dbFailOnError

Or, use the CreateQuerydef method to create a passthrough query - see the
online help for Querydef.