Re: Accessing oracle database from Visual Basic

Zachary Taylor wrote:
>
> Any help would be appreciated, as I am new to Oracle.
>
> I am trying to insert into an Oracle database from Microsoft Access 97. I
> am using an ODBC connection to an Oracle 7.3 database. When ever I try to
> do multiple Execute sql statement it hangs. If I comment out either of the
> two Execute sql commands it runs fine. I will eventually be attempting to
> do basically a thousand of so inserts. Is there a better way to do this?
>
> My code follows:
>
> Sub Import_dbf()
> Dim wrkODBC As Workspace
> Dim myDB As Database
> Dim conPubs As Connection
> Dim rs_per_oracle As Recordset
> Dim rs_per_dbf As Recordset
> Dim sql As String
>
> Set wrkODBC = CreateWorkspace("", "dauuser", "dauuser", dbUseODBC)
> Set conPubs = wrkODBC.OpenConnection("Dau", False, False, "odbc;")
> Set myDB = DBEngine.Workspaces(0).Databases(0)
> Set rs_per_dbf = myDB.OpenRecordset("Margpers", DB_OPEN_TABLE)
> rs_per_dbf.MoveFirst
> conPubs.Execute "delete from imppersonnel;"
>
> sql = "insert into imppersonnel (SSN, COMPONENT, COOL, INTERN,
> COOPED, TUITION, STULOAN, DTPRESPOS, ACQEX, CAREERLEVEL) "
> sql = sql + "values ('" + rs_per_dbf("SSN") +
> "','a',1,'a','a','a','a','a',1,1);"
> 'MsgBox sql
> conPubs.Execute sql
>
> MsgBox "hello"
> End Sub
>
> Thanks
> Zachary Taylor
> taylorzac_at_bah.com

Hi,

attach your oracle-table into your access-db. Then handle it like you would do with any other access-table. If you'll intend to insert lot's of records ensure that your rollback_segments are well-sized.

-- 
Regards

M.Gresz    :-)